MySQL 是否可以在同一个查询中计算两列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5133388/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to count two columns in the same query
提问by santa
Let's say I have the following table structure:
假设我有以下表结构：
t1
-------------
id // row id
userID_follower // this user is a follows another member
userID_following // other member that this user
Is it possible to run a single query to combine both of the following:
是否可以运行单个查询来组合以下两项：
how many users this person is following
select COUNT(id) from t1 WHERE userID_follower = ".$myID." ."
how many users follow this person
select COUNT(id) from t1 WHERE userID_following = ".$myID."
此人关注了多少用户
选择 COUNT(id) from t1 WHERE userID_follower = ".$myID." .”
有多少用户关注此人
选择 COUNT(id) from t1 WHERE userID_following = ".$myID."
Thanks.
谢谢。
回答by The Scrum Meister
In MySql, You can use the SUM()function over a condition, since a false condition will equal to 0, and a true one will equal to 1:
在 MySql 中，您可以SUM()在条件上使用该函数，因为假条件等于0，真条件等于1：
SELECT SUM(userID_follower = $myID) AS followerCount,
SUM(userID_following = $myID) AS followingCount
FROM t1
WHERE userID_follower = $myID
OR userID_following = $myID
回答by Thomas
The more Hoyle(ISO) solution would be using a Case expression:
更多的 Hoyle(ISO) 解决方案将使用 Case 表达式：
Select Sum( Case When userID_follower = $myID Then 1 Else 0 End ) As followerCount
, Sum( Case When userID_following = $myID Then 1 Else 0 End ) As followingCount
From t1
Where userID_follower = $myID
Or userID_following = $myID
回答by Bill Karwin
I recommend returning two rows with one count on each row, instead of two columns:
我建议返回两行，每行一个计数，而不是两列：
SELECT 'follower', COUNT(*) AS count FROM t1 WHERE userID_follower = ?
UNION ALL
SELECT 'following', COUNT(*) FROM t1 WHERE userID_following = ?
This may seem like a degenerate solution, but the reason is that if userID_follower and userID_following are indexed, this can utilize the indexes. If you try to get the results in two columns as shown in the other answers, the can't use the indexes and has to do a table-scan.
这似乎是一个退化的解决方案，但原因是如果对 userID_follower 和 userID_following 进行索引，则可以利用索引。如果您尝试在其他答案中显示的两列中获取结果，则无法使用索引并且必须进行表扫描。
Other tips that are tangential to the question:
与问题无关的其他提示：
- There's no advantage to using COUNT(id) in this case.
- You should use SQL query parameters instead of interpolating $myID into your query.
- 在这种情况下使用 COUNT(id) 没有任何好处。
- 您应该使用 SQL 查询参数而不是将 $myID 插入到您的查询中。
回答by SiggyF
I think something like this should work:
我认为这样的事情应该有效：
select ownerID, count( distinct userID_follow), count(distinct userID_following) from t1 group by ownerID
