如何在两个不同的表上将两个 count(*) 结果相加?

我有两张桌子:玩具和游戏.

+--------------------+-----------------+|领域 |类型 |+--------------------+--------------------+|toy_id |int(10) 无符号 ||little_kid_id |int(10) 无符号 |+--------------------+--------------------++--------------------+--------------------+|领域 |类型 |+--------------------+--------------------+|game_id |int(10) 无符号 ||little_kid1 |int(10) 无符号 ||little_kid2 |int(10) 无符号 ||little_kid3 |int(10) 无符号 |+--------------------+--------------------+

一个小孩可以有多个玩具.一个小孩可以同时参加多个游戏.

我想要一个查询，该查询将提供一个 little_kid 所涉及的玩具 + 游戏的总数.

基本上，我想要这两个查询的总和:

<前>SELECT COUNT(*) FROM Toys where little_kid_id = 900;SELECT COUNT(*) from Games WHERE little_kid1 = 900或 little_kid2 = 900或 little_kid3 = 900；

是否可以在单个 SQL 查询中获得此信息?显然，我可以以编程方式对它们求和，但这不太理想.

(我意识到人为的示例使架构看起来效率低下.假设我们无法更改架构.)

解决方案

把它们包起来并使用子查询:

SELECT(SELECT COUNT(*) FROM Toys WHERE little_kid_id = 900)+(SELECT COUNT(*) from Games WHERE little_kid1 = 900或 little_kid2 = 900或 little_kid3 = 900)AS 总和计数

瞧！

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.

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.

(I realize that the contrived example makes the schema look ineffecient. Let's assume that we can't change the schema.)

解决方案

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!