MySQL 如何在两个不同的表上将两个 count(*) 结果相加？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/826365/
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
How do I add two count(*) results together on two different tables?
提问by Runcible
I have two tables: Toys and Games.
我有两张桌子：玩具和游戏。
+--------------------+------------------+
| Field | Type |
+--------------------+------------------+
| toy_id | int(10) unsigned |
| little_kid_id | int(10) unsigned |
+--------------------+------------------+
+--------------------+------------------+
| Field | Type |
+--------------------+------------------+
| game_id | int(10) unsigned |
| little_kid1 | int(10) unsigned |
| little_kid2 | int(10) unsigned |
| little_kid3 | int(10) unsigned |
+--------------------+------------------+
A little kid can have multiple toys. A little kid can be participating in multiple games at once.
一个小孩子可以有多个玩具。一个小孩可以同时参加多个游戏。
I want a query that will give me the total number of toys + games that a little_kid is involved with.
我想要一个查询，它会给我一个 little_kid 参与的玩具 + 游戏的总数。
Basically, I want the sum of these two queries:
基本上，我想要这两个查询的总和：
SELECT COUNT(*) FROM Toys WHERE little_kid_id = 900;
SELECT COUNT(*) from Games WHERE little_kid1 = 900
OR little_kid2 = 900
OR little_kid3 = 900;
Is it possible to get this in a single SQL query? Obviously, I can sum them programmatically, but that's less desirable.
是否可以在单个 SQL 查询中获得它？显然，我可以以编程方式对它们求和，但这不太理想。
(I realize that the contrived example makes the schema look ineffecient. Let's assume that we can't change the schema.)
（我意识到人为的示例使模式看起来效率低下。假设我们无法更改模式。）
回答by Eric
Wrap them up and use subqueries:
将它们包装起来并使用子查询：
SELECT
(SELECT COUNT(*) FROM Toys WHERE little_kid_id = 900)+
(SELECT COUNT(*) from Games WHERE little_kid1 = 900
OR little_kid2 = 900
OR little_kid3 = 900)
AS SumCount
Voila!
瞧！
回答by anish
SELECT
((SELECT COUNT(*) FROM Toys WHERE little_kid_id = 900)+
(SELECT COUNT(*) from Games WHERE little_kid1 = 900
OR little_kid2 = 900
OR little_kid3 = 900))
AS Sum FROM DUAL;
回答by Adam Robinson
SELECT COUNT(1) FROM
(
SELECT 1 FROM Toys WHERE little_kid_id = 900
UNION
SELECT 1 FROM Games WHERE little_kid1 = 900
OR little_kid2 = 900
OR little_kid3 = 900
)
回答by James C
Depending on how much this query is likely to be run and how often the data changes you could periodically put data into an aggregated table like this:
根据此查询可能运行的次数以及数据更改的频率，您可以定期将数据放入聚合表中，如下所示：
CREATE TABLE aggregated (
little_kid_id INT UNSIGNED,
games_count INT UNSIGNED,
toys_count INT UNSIGNED,
PRIMARY KEY (little_kid_id)
);
Performance wise that would be s**t hot fast and avoids any nasty sub-queries.
性能明智，这将是 s**t hot fast 并避免任何令人讨厌的子查询。
回答by Scott Ramey
Try this one...
试试这个...
db: mysql
SELECT SUM(dum.tab) AS total FROM (
SELECT COUNT(b.category_id) AS tab FROM tblcategory AS b WHERE b.category_id=1
UNION ALL
SELECT COUNT(a.category_id) AS tab FROM tblcategory AS a WHERE a.category_id=2
) AS dum
回答by Hitesh Makwana
SELECT M.*,M.TOYSCOUNT+M.GAMECOUNT
FROM (
(SELECT COUNT(*) FROM Toys WHERE little_kid_id) AS TOYSCOUNT,
(SELECT COUNT(*) from Games WHERE little_kid1 = 900 OR little_kid2 = 900 OR little_kid3 = 900) AS GAMECOUNT
) M
回答by Arul
select t1.tx,t2.px,t3.mx,t2.px + t3.mx
as total from(
SELECT COUNT (DISTINCT id) as tx
FROM Customer) as t1
cross join(
select COUNT (DISTINCT name) as px
FROM details
) as t2
cross join(
select count (distinct device_id) as mx
from detailconfig
) as t3
回答by PhantomReference
SELECT COUNT(1) FROM
(
(SELECT 1 FROM Toys WHERE little_kid_id = 900
UNION
SELECT 1 FROM Games WHERE little_kid1 = 900
OR little_kid2 = 900
OR little_kid3 = 900) as temptable
)
