如何在两个不同的表上添加两个count（*）结果？ [英] How do I add two count(*) results together on two different tables?

问题描述

我有两张表：玩具和游戏。

  + ------------- ------- + ------------------ + 
 |字段|类型| 
 + -------------------- + ------------------ + 
 | toy_id | int（10）unsigned | 
 | little_kid_id | int（10）unsigned | 
 + -------------------- + ------------------ + 
 
 + -------------------- + ------------------ + 
 |字段|类型| 
 + -------------------- + ------------------ + 
 | game_id | int（10）unsigned | 
 | little_kid1 | int（10）unsigned | 
 | little_kid2 | int（10）unsigned | 
 | little_kid3 | int（10）unsigned | 
 + -------------------- + ------------------ + 
  

一个小孩可以有多个玩具。
一个小孩可以一次参加多个游戏。

我想要一个查询，给我一个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 
 OR little_kid2 = 900 
 OR little_kid3 = 900）
 AS SumCount 
  

p>

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!

这篇关于如何在两个不同的表上添加两个count（*）结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！