如何在一个查询中计算2个不同的数据 [英] How to count 2 different data in one query
问题描述
我需要在一个查询中计算两列中某些数据的出现总数。数据库位于SQL Server 2005中。
I need to calculate sum of occurences of some data in two columns in one query. DB is in SQL Server 2005.
例如,我有此表:
Person: Id, Name, Age
我需要在一个查询中获得以下结果:
1.姓约翰的人数
2.年龄超过30岁的约翰的人数。
And I need to get in one query those results:
1. Count of Persons that have name 'John'
2. Count of 'John' with age more than 30 y.
我可以通过这种方式(仅作为示例)对子查询进行操作:
I can do that with subqueries in this way (it is only example):
SELECT (SELECT COUNT(Id) FROM Persons WHERE Name = 'John'),
(SELECT COUNT (Id) FROM Persons WHERE Name = 'John' AND age > 30)
FROM Persons
但这很慢,我正在寻找更快的方法。
But this is very slow, and I'm searching for faster method.
我发现了这个解决方案(适用于MySQL)(它几乎解决了我的问题,但不适用于SQL Server)。
I found this solution for MySQL (it almost solve my problem, but it is not for SQL Server).
您知道吗
推荐答案
使用CASE语句可让您在单个查询中计算所需的任何内容查询:
Using a CASE statement lets you count whatever you want in a single query:
SELECT
SUM(CASE WHEN Persons.Name = 'John' THEN 1 ELSE 0 END) AS JohnCount,
SUM(CASE WHEN Persons.Name = 'John' AND Persons.Age > 30 THEN 1 ELSE 0 END) AS OldJohnsCount,
COUNT(*) AS AllPersonsCount
FROM Persons
这篇关于如何在一个查询中计算2个不同的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!