Sql 将两列相加为同一表中的行的查询

您可以使用UNION来执行此操作，从而有效地将两个查询连接到同一个结果集中:

select customer_id ,customer_fullname , amount_withdraw ,amount_deposit ,entry_date 
from Operations b join Customers a
on b.customer_id2 = a.customer_id
where customer_id = 19

UNION

SELECT customer_id ,customer_fullname , SUM(amount_withdraw) - SUM(amount_deposit) ,null, null 
FROM Operations b 
JOIN join Customers a on b.customer_id2 = a.customer_id
WHERE customer_id = 19
GROUP BY customer_id ,customer_fullname

UNION 

SELECT customer_id ,customer_fullname , SUM(amount_withdraw) ,SUM(amount_deposit) ,null 
FROM Operations b 
JOIN join Customers a on b.customer_id2 = a.customer_id
WHERE customer_id = 19
GROUP BY customer_id ,customer_fullname

这样做好吗...通常不会像这样的查询和聚合最好留给应用程序或报告层.上述查询的第一个问题是结果的顺序，第二个问题是您使用同一列返回不同的信息集.

我们可以根据Date列的可空性来确定顺序，但是语法看起来有点奇怪.我们不能对UNION个查询使用ORDER BY子句中的函数，因此要做到这一点，我们必须将汇总数据包装到子查询或CTE中

http://sqlfiddle.com/#!18/7022a/2

SELECT * FROM (
  select customer_id ,customer_fullname , amount_withdraw ,amount_deposit ,entry_date 
from Operations b 
JOIN Customers a on b.customer_id2 = a.customer_id
where customer_id = 19

UNION

SELECT customer_id ,customer_fullname , SUM(amount_withdraw) - SUM(amount_deposit) ,null, null 
FROM Operations b 
JOIN Customers a on b.customer_id2 = a.customer_id
WHERE customer_id = 19
GROUP BY customer_id ,customer_fullname

UNION 

SELECT customer_id ,customer_fullname , SUM(amount_withdraw) ,SUM(amount_deposit) ,null 
FROM Operations b 
JOIN Customers a on b.customer_id2 = a.customer_id
WHERE customer_id = 19
GROUP BY customer_id ,customer_fullname
) RollupData
ORDER BY ISNULL(entry_date,'2099-12-31'), ISNULL(amount_deposit, 999999999)

排序可以通过添加任意列而不是依赖于entry_date的空性以不同的方式修复，这使得它变得更加明显，如果您直接使用结果，我们可以从最终输出中删除该列，但我们经常会保留它，以便可以在报告层中重新确认排序.下面显示了如何删除它:

http://sqlfiddle.com/#!18/7022a/5个

SELECT customer_id ,customer_fullname , amount_withdraw ,amount_deposit ,entry_date 
FROM
(
    select 1 as [order], customer_id ,customer_fullname , amount_withdraw ,amount_deposit ,entry_date 
    from Operations b 
    JOIN Customers a on b.customer_id2 = a.customer_id
    where customer_id = 19

    UNION

    SELECT 2 as [order], customer_id ,customer_fullname , SUM(amount_withdraw) - SUM(amount_deposit) ,null, null 
    FROM Operations b 
    JOIN Customers a on b.customer_id2 = a.customer_id
    WHERE customer_id = 19
    GROUP BY customer_id ,customer_fullname

    UNION 

    SELECT 3 as [order], customer_id ,customer_fullname , SUM(amount_withdraw) ,SUM(amount_deposit) ,null 
    FROM Operations b 
    JOIN Customers a on b.customer_id2 = a.customer_id
    WHERE customer_id = 19
    GROUP BY customer_id ,customer_fullname
  
) RollupData
ORDER BY [order]

更标准的解决方案是将聚合移动到其他列，您也可以使用窗口查询，但这些查询将具有与您的请求不同的布局.