如何在SQL-2中显示和计数多列

Question

表的名称产品，其中列包括苹果(A)和葡萄(G)。

A G

1 1    
1 1
1 Null

我执行了查询..。

Select count(Apple) as 'Apple' count(Grape) as 'Grape' 
from products 
where Apple = 1 and Grape = 1

我从上面的查询得到的输出是

Apple=2
Grape=2

我应该去找Apple=3和Grape=2。请帮帮忙。

Answer 1

create table AppleGrape(A int, B int)

go
insert into AppleGrape values(1,1)
insert into AppleGrape values(1,1)
insert into AppleGrape values(1,NULL)

SELECT sum(A) as 'Apple',sum(isnull(B,0)) as 'Grape'
FROM AppleGrape

也要检查小提琴。http://sqlfiddle.com/#!3/126e05/5

Answer 2

如果你需要一个整体的话，这看起来可能更安全。它更安全地处理空值，并且不依赖于聚合默认处理空值的不同行为，这可能会导致一些查询中断。

SELECT SUM(ISNULL(Apple,0)) AS Apple, SUM(ISNULL(Grapes,0)) AS Grape FROM Products

Answer 3

您的where条件仅包括行where Apple = 1 and Grape = 1，因此忽略了包含grape NULL的完整行。