将 MySQL 中特定字段中具有相同值的行分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15203058/
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
Group the rows that are having the same value in specific field in MySQL
提问by user2131907
I am having the following data in MySQL table.
我在 MySQL 表中有以下数据。
+--------+----------+------+
| job_id | query_id | done |
+--------+----------+------+
| 15145 | a002 | 1 |
| 15146 | a002 | 1 |
| 15148 | a002 | 1 |
| 15150 | a002 | 1 |
| 15314 | a003 | 0 |
| 15315 | a003 | 1 |
| 15316 | a003 | 0 |
| 15317 | a003 | 0 |
| 15318 | a003 | 1 |
| 15319 | a003 | 0 |
+--------+----------+------+
I would like to know if it's possible to have a sql query, which can group by query_id IF ALL 'done' fields are marked as 1. The possible output I imagine would be:
我想知道是否有可能有一个 sql 查询，它可以按 query_id 分组，如果所有“完成”字段都标记为 1。我想象的可能输出是：
+----------+------+
| query_id | done |
+----------+------+
| a002 | 1 |
| a003 | 0 |
+----------+------+
I've tried the following SQL query:
我尝试了以下 SQL 查询：
select job_id, query_id, done from job_table group by done having done = 1 ;
But no luck. I would really appreciate for any help!
但没有运气。我真的很感激任何帮助！
采纳答案by Bogdan Gavril MSFT
I'm not particullarly proud of this solution because it is not very clear, but at least it's fast and simple. If all of the items have "done" = 1then the sum will be equal to the count SUM = COUNT
我对这个解决方案并不特别自豪，因为它不是很清楚，但至少它快速而简单。如果所有项目都“完成”= 1，那么总和将等于计数 SUM = COUNT
SELECT query_id, SUM(done) AS doneSum, COUNT(done) AS doneCnt
FROM tbl
GROUP BY query_id
And if you add a having clause you get the items that are "done".
如果你添加一个 having 子句，你会得到“完成”的项目。
HAVING doneSum = doneCnt
I'll let you format the solution properly, you can do a DIFERENCE to get the "not done" items or doneSum <> doneCnt.
我会让你正确格式化解决方案，你可以做一个不同的事情来获得“未完成”的项目或 doneSum <> doneCnt。
Btw, SQL fiddle here.
顺便说一句，SQL在这里摆弄。
回答by Denis
All "done" jobs :
所有“完成”的工作：
SELECT * FROM job_table GROUP BY done HAVING MIN(done) = 1;
All "undone" jobs :
所有“未完成”的工作：
SELECT * FROM job_table GROUP BY done HAVING MAX(done) = 0;
All jobs having the same value ("done" OR "undone") :
所有具有相同值的作业（“完成”或“撤销”）：
SELECT * FROM job_table GROUP BY done HAVING MAX(done) = 0 OR MIN(done) = 1;
回答by T I
try something like
尝试类似
SELECT qry_id, 1
FROM (
SELECT qry_id, done
FROM tbl
GROUP BY qry_id, done
) AS t
GROUP BY qry_id
HAVING COUNT(*)=1
UNION
SELECT qry_id, 0
FROM (
SELECT qry_id, done
FROM tbl
GROUP BY qry_id, done
) AS t
GROUP BY qry_id
HAVING COUNT(*)>1
回答by Maciej Cygan
I think you should be looking at this
我想你应该看看这个
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
