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

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

+--------------------------------+-----------------+|领域 |类型 |+--------------------------------+-----------------+|玩具ID |int(10) 无符号 ||little_kid_id |int(10) 无符号 |+--------------------------------+-----------------++--------------------------------+-----------------+|领域 |类型 |+--------------------------------+-----------------+|游戏ID |int(10) 无符号 ||小孩子1 |int(10) 无符号 ||小孩子2 |int(10) 无符号 ||小孩子3 |int(10) 无符号 |+--------------------------------+-----------------+

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

我想要一个查询，它会给我一个 little_kid 参与的玩具和游戏的总数.

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

<上一页>从 little_kid_id = 900 的玩具中选择计数(*)；从游戏中选择 COUNT(*) little_kid1 = 900或 little_kid2 = 900或 little_kid3 = 900；

是否有可能在单个 SQL 查询中得到这个?显然，我可以通过编程方式对它们求和，但这不太理想.

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

解决方案

包装它们并使用子查询:

选择(从 little_kid_id = 900 的玩具中选择计数(*))+(SELECT COUNT(*) from Games WHERE little_kid1 = 900或 little_kid2 = 900或 little_kid3 = 900)AS SumCount

瞧！

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!