在查询中计数两次,一次使用限制

发布于 2024-09-02 02:43:20 字数 669 浏览 3 评论 0原文

给出下表:

Table1
[class]   [child]
 math      boy1
 math      boy2
 math      boy3
 art       boy1

Table2
[child]   [glasses]
 boy1       yes
 boy2       yes
 boy3       no

如果我想查询每个班级的儿童人数,我会这样做:

SELECT class, COUNT(child) FROM Table1 GROUP BY class

如果我想查询每个班级戴眼镜的儿童人数,我会这样做:

SELECT Table1.class, COUNT(table1.child) FROM Table1
LEFT JOIN Table2 ON Table1.child=Table2.child
WHERE Table2.glasses='yes' GROUP BY Table1.class

但我真正想要的是做的是:

SELECT class, COUNT(child), COUNT(child wearing glasses)

坦率地说,我不知道如何在一个查询中做到这一点。

帮助?

Given the following tables:

Table1
[class]   [child]
 math      boy1
 math      boy2
 math      boy3
 art       boy1

Table2
[child]   [glasses]
 boy1       yes
 boy2       yes
 boy3       no

If I want to query for number of children per class, I'd do this:

SELECT class, COUNT(child) FROM Table1 GROUP BY class

and if I wanted to query for number of children per class wearing glasses, I'd do this:

SELECT Table1.class, COUNT(table1.child) FROM Table1
LEFT JOIN Table2 ON Table1.child=Table2.child
WHERE Table2.glasses='yes' GROUP BY Table1.class

but what I really want to do is:

SELECT class, COUNT(child), COUNT(child wearing glasses)

and frankly I have no idea how to do that in only one query.

help?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

风月客 2024-09-09 02:43:20

你可以尝试这样的事情(不检查语法):

SELECT 
   class, 
   COUNT(distinct Table1.child), 
   SUM(IF(Table2.glasses='yes', 1, 0))
FROM Table1
LEFT JOIN Table2 ON Table1.child=Table2.child

这有点老套,你可能会发现在两个查询中完成它更容易,但这种技术在紧要关头对我有用

You can try something like this (not syntax checked):

SELECT 
   class, 
   COUNT(distinct Table1.child), 
   SUM(IF(Table2.glasses='yes', 1, 0))
FROM Table1
LEFT JOIN Table2 ON Table1.child=Table2.child

It's a bit hacky and you may find it's just easier to do it in two queries but this technique has worked for me in a pinch

甜尕妞 2024-09-09 02:43:20

在 MySQL 中,你可以这样做:

SELECT class, COUNT(child), SUM(IF(glasses = 'yes', 1, 0)

in MySQL you can do it something like this:

SELECT class, COUNT(child), SUM(IF(glasses = 'yes', 1, 0)

初与友歌 2024-09-09 02:43:20

它不是一个完美的解决方案,它只是一个替代解决方案,

SELECT class AS a, COUNT( child ) AS b
FROM Table1
GROUP BY class
UNION ALL SELECT COUNT( table1.child ) AS a, Table1.class AS b
FROM Table1
LEFT JOIN Table2 ON Table1.child = Table2.child
WHERE Table2.glasses = 'yes'
GROUP BY Table1.class

输出是:

a   b
art     1
math    3
1   art
2   math

在此,我们可能会得到一些开发更多东西的想法。

It is not a perfect solution, it is just a alternative solution,

SELECT class AS a, COUNT( child ) AS b
FROM Table1
GROUP BY class
UNION ALL SELECT COUNT( table1.child ) AS a, Table1.class AS b
FROM Table1
LEFT JOIN Table2 ON Table1.child = Table2.child
WHERE Table2.glasses = 'yes'
GROUP BY Table1.class

The output is :

a   b
art     1
math    3
1   art
2   math

In this, we may got some idea for develop more thing.

神经暖 2024-09-09 02:43:20

也许这个帖子已经有 5 年历史了,但我也有一个解决方案。

使用 1 和 0 等数字来表示是/否和真/假对您来说会更容易。

解决方案是(如果将是和否更改为 1 和 0):

SELECT class,     
COUNT(distinct Table1.child) AS NumbofChilds,     
SUM(Table2.glasses) AS ChildswithGlasses 

FROM Table1 INNER JOIN Table2 ON Table1.child=Table2.child

GROUP BY class;

必须检查语法,但我对表有类似的查询并且它有效。

Maybe the post is 5 years old but I ahve also a solution.

Working with digits like 1 and 0 for yes/no and true/false will be easier for you.

A solution would be (if you change the yes's and no's to 1 and 0):

SELECT class,     
COUNT(distinct Table1.child) AS NumbofChilds,     
SUM(Table2.glasses) AS ChildswithGlasses 

FROM Table1 INNER JOIN Table2 ON Table1.child=Table2.child

GROUP BY class;

Syntax have to be checked, but I have a similar query for my tables and it works.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文