我有一个内联表值函数
[dbo].[Sales] (@FromDate DATE, @ToDate DATE) T1
返回一个表:
+----------+----------+-----+
| Location | Category |Sales|
+----------+----------+-----+
| AMB | A | 10 |
| BWD | A | 20 |
| UNR | A | 30 |
| BWD | C | 40 |
| BWD | P | 50 |
| BWD | S | 60 |
| BWD | W | 70 |
+----------+----------+-----+
现在,我希望在此输出上应用某些过滤器并在一行中获取 6 个值。 SELECT 背后的逻辑是:
SELECT
(SELECT SUM(T1.Sales) WHERE T1.Category = 'P') AS 'Pipes',
(SELECT SUM(T1.Sales) WHERE T1.Category = 'C') AS 'Casing',
(SELECT SUM(T1.Sales) WHERE T1.Category = 'W') AS 'Wires',
(SELECT SUM(T1.Sales) WHERE T1.Location = 'BWD' AND T1.Category IN ('A','S')) AS 'Switches',
(SELECT SUM(T1.Sales) WHERE T1.Location IN ('AMB','UNR')) AS 'Accessories',
(SUM(T1.Sales)) AS 'Total'
FROM [dbo].[Sales] (@FromDate,@ToDate) T1
预期结果为:
+-------+--------+-------+----------+-------------+-------+
| Pipes | Casing | Wires | Switches | Accessories | Total |
+-------+--------+-------+----------+-------------+-------+
| 50 | 40 | 70 | 80 | 40 | 280 |
+-------+--------+-------+----------+-------------+-------+
我知道我写的 SELECT 查询是错误的。如何在不重复调用函数的情况下在单个查询中获得此所需输出?我希望这尽可能快。(欢迎任何替代方案!
下一步: 一旦我使用单个 SELECT 在一行中获取这 6 个值,我希望将这 6 个值作为另一个 TVF(外部 TVF(的一部分返回。
我是初学者,如果您能就此查询的速度/性能方面或更好的替代方案(如果有的话(提供诚实的建议/意见,我将不胜感激。
提前非常感谢你。
只使用条件聚合,而不是子查询:
SELECT SUM(CASE WHEN T1.Category = 'P' THEN T1.Sales END) as Pipes,
SUM(CASE WHEN T1.Category = 'C' THEN T1.Sales END) as Casing,
SUM(CASE WHEN T1.Category = 'W' THEN T1.Sales END) as Wires,
SUM(CASE WHEN T1.Location = 'BWD' AND T1.Category IN ('A','S') THEN T1.Sales END) as Switches,
SUM(CASE WHEN T1.Location IN ('AMB', 'UNR') THEN T1.Sales END) AS Accessories,
SUM(T1.Sales) AS Total
FROM [dbo].[Sales] (@FromDate, @ToDate) T1;
请注意,我还删除了列别名两边的单引号。 这是一个坏习惯。 仅对字符串和日期常量使用单引号。