sql - 如何优化这个查询？

Question

我在 SQL Server 2005 中有一个包含 ID,paymentType,price 列的表 sales。

create table sales(id int IDENTITY(1,1),
                   paymentType varchar(2), 
                   price decimal(10,2) default(0)
                  )

有没有一种方法可以在一个查询中执行此操作？

SELECT SUM(price) as TE 
FROM sales 
WHERE paymentType = 'E' 

SELECT SUM(price) as TC 
FROM sales 
WHERE paymentType = 'C'

Answer 1

您可以使用 GROUP BY 按 paymentType 对您的值进行分组，并获得“E”和“C”的两条记录及其总和

SELECT paymentType,SUM(price) 
FROM sales 
WHERE paymentType = 'E' or paymentType = 'C'
GROUP BY paymentType