要从多表查询中获取特定公司生产的某些产品的最新检测报告,我们需要设计一个SQL查询,通常涉及公司表、产品表和检测报告表之间的关联。以下是几种实现方式:
SELECT
c.company_name,
p.product_name,
r.report_id,
r.test_date,
r.test_result
FROM
companies c
JOIN
products p ON c.company_id = p.company_id
JOIN
reports r ON p.product_id = r.product_id
JOIN (
SELECT
product_id,
MAX(test_date) AS latest_date
FROM
reports
GROUP BY
product_id
) latest ON r.product_id = latest.product_id AND r.test_date = latest.latest_date
WHERE
c.company_name = '目标公司名称'
AND p.product_name IN ('产品1', '产品2', '产品3');
WITH ranked_reports AS (
SELECT
c.company_name,
p.product_name,
r.report_id,
r.test_date,
r.test_result,
ROW_NUMBER() OVER (PARTITION BY p.product_id ORDER BY r.test_date DESC) AS rn
FROM
companies c
JOIN
products p ON c.company_id = p.company_id
JOIN
reports r ON p.product_id = r.product_id
WHERE
c.company_name = '目标公司名称'
AND p.product_name IN ('产品1', '产品2', '产品3')
)
SELECT
company_name,
product_name,
report_id,
test_date,
test_result
FROM
ranked_reports
WHERE
rn = 1;
SELECT
c.company_name,
p.product_name,
r.report_id,
r.test_date,
r.test_result
FROM
companies c
JOIN
products p ON c.company_id = p.company_id
JOIN
reports r ON p.product_id = r.product_id
LEFT JOIN
reports r_newer ON p.product_id = r_newer.product_id AND r.test_date < r_newer.test_date
WHERE
c.company_name = '目标公司名称'
AND p.product_name IN ('产品1', '产品2', '产品3')
AND r_newer.report_id IS NULL;
以上方案都可以根据您的具体数据库结构和需求进行调整。