时间戳

首页 > 解决方案 > 多个 SELECTS 进入单个结果行

sql-server - 多个 SELECTS 进入单个结果行

问题描述

我正在尝试将 2 个选择语句组合成一个查询，如果可能的话，我希望得到 1 行包含 4 列（每个查询 2 列）。我确信我过去使用过类似下面的内容没有问题，但我收到错误“当 EXISTS 未引入子查询时，选择列表中只能指定一个表达式。”

谢谢

SELECT 
(SELECT
SUM(SO.new_profits_sales_totalactualprofit) as TAP_AFFINITY, 
SUM(SO.new_profits_sales_totaldealprofit) as TDP_AFFINITY
FROM new_dealsheet DS
LEFT JOIN salesorder SO ON DS.new_dsheetid = SO.salesorderid
LEFT JOIN New_salespeople SP ON DS.New_SalespersonId = SP.New_salespeopleId 
WHERE CAST(SO.New_purchaseordersenddate as date) = CAST(GETDATE() as date)  
AND SO.New_PassedToAdmin = 1
AND SP.New_SalesGroupIdName = 'AFFINITY') as t1,

(SELECT
SUM(SO.new_profits_sales_totalactualprofit) as TAP_GENUS, 
SUM(SO.new_profits_sales_totaldealprofit) as TDP_GENUS
FROM new_dealsheet DS
LEFT JOIN salesorder SO ON DS.new_dsheetid = SO.salesorderid
LEFT JOIN New_salespeople SP ON DS.New_SalespersonId = SP.New_salespeopleId 
WHERE CAST(SO.New_purchaseordersenddate as date) = CAST(GETDATE() as date)  
AND SO.New_PassedToAdmin = 1
AND SP.New_SalesGroupIdName = 'GENUS') as t2

标签： sql-server

解决方案

您可以交叉连接两个单行结果集以组合它们。

WITH t1 AS
(
SELECT
SUM(SO.new_profits_sales_totalactualprofit) as TAP_AFFINITY, 
SUM(SO.new_profits_sales_totaldealprofit) as TDP_AFFINITY
FROM new_dealsheet DS
LEFT JOIN salesorder SO ON DS.new_dsheetid = SO.salesorderid
LEFT JOIN New_salespeople SP ON DS.New_SalespersonId = SP.New_salespeopleId 
WHERE CAST(SO.New_purchaseordersenddate as date) = CAST(GETDATE() as date)  
AND SO.New_PassedToAdmin = 1
AND SP.New_SalesGroupIdName = 'AFFINITY'
), t2 AS
(
SELECT
SUM(SO.new_profits_sales_totalactualprofit) as TAP_GENUS, 
SUM(SO.new_profits_sales_totaldealprofit) as TDP_GENUS
FROM new_dealsheet DS
LEFT JOIN salesorder SO ON DS.new_dsheetid = SO.salesorderid
LEFT JOIN New_salespeople SP ON DS.New_SalespersonId = SP.New_salespeopleId 
WHERE CAST(SO.New_purchaseordersenddate as date) = CAST(GETDATE() as date)  
AND SO.New_PassedToAdmin = 1
AND SP.New_SalesGroupIdName = 'GENUS'
)
SELECT *
FROM t1 cross join T2

但更好的方法是在一个查询中完成所有操作

SELECT SUM(CASE WHEN SP.New_SalesGroupIdName = 'GENUS' THEN SO.new_profits_sales_totalactualprofit END) AS TAP_GENUS,
       SUM(CASE WHEN SP.New_SalesGroupIdName = 'GENUS' THEN SO.new_profits_sales_totaldealprofit END)   AS TDP_GENUS,
       SUM(CASE WHEN SP.New_SalesGroupIdName = 'AFFINITY' THEN SO.new_profits_sales_totalactualprofit END) AS TAP_AFFINITY,
       SUM(CASE WHEN SP.New_SalesGroupIdName = 'AFFINITY' THEN SO.new_profits_sales_totaldealprofit END)   AS TDP_AFFINITY
        FROM   new_dealsheet DS
               LEFT JOIN salesorder SO
                      ON DS.new_dsheetid = SO.salesorderid
               LEFT JOIN New_salespeople SP
                      ON DS.New_SalespersonId = SP.New_salespeopleId
        WHERE  CAST(SO.New_purchaseordersenddate AS DATE) = CAST(GETDATE() AS DATE)
               AND SO.New_PassedToAdmin = 1
               AND SP.New_SalesGroupIdName IN ( 'GENUS','AFFINITY')

推荐阅读