TSQL在同一表上组合多个子查询

Question

我一直在尝试改进一个SQL查询，该查询在同一表上使用多个子查询，但具有不同的条件，并且仅从每个子查询检索第一个结果。

我将尝试简化用例:

我有一个表Products:

tbody> <<tr>2

Product_id	reference	field3	字段4
1	ref1	val1	val3
	ref2	val2	val4

可以将相关子查询(称为"子选择")转换为独立的子查询，然后将它们连接起来。这样，每个子查询只需要运行一次。我将向您展示如何为您的第一个子查询执行此操作。

这是一个取代第一个子查询的子查询。

SELECT reference, MAX(a_date) a_date
FROM history
WHERE  physicalcode = 'TST' 
AND issue = 0
GROUP BY reference

这给出了一个虚拟表，其中包含与问题中的标准匹配的历史表中的每个参考号的最新日期。history (physicalcode, issue, reference, a_date)上的多列索引使此速度更快。

然后你可以像这样将它连接到主表:

SELECT 
p.reference,
p.field3, p.field4,
a.a_date a_date_issue_0
FROM products p
LEFT JOIN ( /*the subquery */
SELECT reference, MAX(a_date) a_date
FROM history
WHERE  physicalcode = 'TST' 
AND issue = 0
GROUP BY reference
) a ON p.reference=a.reference

这些子查询也可以定义为视图或公共表表达式(cte)。如果你有很多，你可能会发现这样做更容易阅读和推理你的查询。

最后一个子查询用这种方式处理有点棘手。我建议你先回答这个问题，然后再问另一个问题。

感谢@O。我已经找到了一个改进这个查询的方法。

为了合并几个请求，我使用了如下的CTE:

从

SELECT
(SELECT TOP 1 a_date 
FROM history h 
WHERE h.reference = p.reference 
AND physicalcode = 'TST' 
AND issue = 0 
ORDER BY a_date DESC) AS latest_date_issue_0,
(SELECT TOP 1 a_date 
FROM history h 
WHERE h.reference = p.reference 
AND physicalcode = 'TST' 
AND issue = 1 
ORDER BY a_date DESC) AS latest_date_issue_1
(SELECT top 1 a_date 
FROM history h 
WHERE h.reference = p.reference 
AND h.physicalcode = 'TSTKO' 
ORDER BY h.d_systeme DESC ) AS d_tst_ko,
(SELECT top 1 a_date 
FROM history h 
WHERE h.reference = p.reference 
AND h.physicalcode = 'CALLERID' 
ORDER BY h.d_systeme DESC ) AS d_wrong_number
FROM products p

WITH physicalcode_cte (reference, physicalcode, issue, a_date)  as
(
SELECT reference, physicalcode, issue, max(a_date)
from historique
where codephysique in ('TST','TSTKO','CALLERID')
and a_date > dateadd(month, -4, getdate()) -- filter on date range to reduce number of rows
group by reference, physicalcode, issue
)
SELECT
date_issue_0.a_date,
date_issue_1.a_date,
tst_ko.a_date,
wrong_number.a_date
FROM products p
LEFT JOIN physicalcode_cte date_issue_0 on p.reference = date_issue_0.reference 
AND date_issue_0.codephysique = 'TST' 
AND date_issue_0.anomalie = 0
LEFT JOIN physicalcode_cte date_issue_1 on p.reference = date_issue_1.reference
AND date_issue_1.codephysique = 'TST' 
AND date_issue_1.anomalie = 1
LEFT JOIN physicalcode_cte tst_ko on p.reference = tst_ko.reference
AND tst_ko.codephysique = 'TST' 
LEFT JOIN physicalcode_cte wrong_number on p.reference = wrong_number.reference AND 
AND wrong_number.codephysique = 'TST' 

我将这个想法应用于不同的场景，并制作了2个CTE。我不能合并所有的东西，有时合并会增加成本。但经过几次测试，我已经能够将总成本从7100降低到2100。
这仍然是很多，但无论如何少了3倍。需要5秒而不是超时。
这是一个用于月度报告的查询，所以我不需要它非常快，我将保持这种方式。

谢谢你!