我正在尝试将 2 个 select 语句合并到一个查询中，如果可能的话，我希望以包含 4 列的 1 行（每个查询 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
您可以交叉连接两个单行结果集以组合它们。
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')
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句