多表查询获取特定公司下所有产品的最新检测报告

问题分析

要获取特定公司下所有产品的最新检测报告，通常需要涉及以下几个表： 1. 公司表(companies) 2. 产品表(products) 3. 检测报告表(inspection_reports)

解决方案

方案一：使用子查询获取最新报告

SELECT 
    p.product_id,
    p.product_name,
    r.report_id,
    r.report_date,
    r.report_content
FROM 
    products p
JOIN 
    companies c ON p.company_id = c.company_id
JOIN 
    inspection_reports r ON p.product_id = r.product_id
WHERE 
    c.company_name = '目标公司名称'
    AND r.report_date = (
        SELECT MAX(report_date)
        FROM inspection_reports
        WHERE product_id = p.product_id
    );

方案二：使用窗口函数(更高效)

WITH LatestReports AS (
    SELECT 
        r.*,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY report_date DESC) as rn
    FROM 
        inspection_reports r
)
SELECT 
    p.product_id,
    p.product_name,
    lr.report_id,
    lr.report_date,
    lr.report_content
FROM 
    products p
JOIN 
    companies c ON p.company_id = c.company_id
JOIN 
    LatestReports lr ON p.product_id = lr.product_id AND lr.rn = 1
WHERE 
    c.company_name = '目标公司名称';

方案三：使用LEFT JOIN排除法

SELECT 
    p.product_id,
    p.product_name,
    r.report_id,
    r.report_date,
    r.report_content
FROM 
    products p
JOIN 
    companies c ON p.company_id = c.company_id
LEFT JOIN 
    inspection_reports r ON p.product_id = r.product_id
LEFT JOIN 
    inspection_reports r_newer ON p.product_id = r_newer.product_id 
    AND r_newer.report_date > r.report_date
WHERE 
    c.company_name = '目标公司名称'
    AND r_newer.report_id IS NULL
    AND r.report_id IS NOT NULL;

性能考虑

1. 确保相关表上有适当的索引：

   • 公司表的company_name字段
   • 产品表的company_id和product_id字段
   • 检测报告表的product_id和report_date字段

2. 对于大数据量，方案二(窗口函数)通常性能最好

3. 如果只需要部分字段，避免使用SELECT *，只查询必要的字段

扩展建议

1. 如果需要分页，可以添加LIMIT和OFFSET
2. 可以添加排序条件，如按产品名称或报告日期排序
3. 考虑添加报告状态过滤(如只查询通过的检测报告)

希望这些方案能帮助您解决问题。根据您的具体数据库结构和数据量，可以选择最适合的方案。