MySQL:如何GROUP BY一个字段检索ORDER BY另一个字段的行? [英] MySQL: How to GROUP BY a field to retrieve the rows with ORDER BY another field?
问题描述
假设以下数据:
数据: $ b
id |日期|名称|等级
-------- + --------------- + ----------- + -------- -------
1 | 2010/12/03 |迈克| 12
2 | 2010/12/04 |珍妮| 12
3 | 2010/12/04 |罗纳德| 15
4 | 2010/12/03 | Yeni | 11
我想知道谁在每一天的成绩最好,如下所示:
预期结果:
id |日期|名称|等级
-------- + --------------- + ----------- + -------- -------
1 | 2010/12/03 |迈克| 12
3 | 2010/12/04 |罗纳德| 15
我认为查询应该是这样的:
SELECT name FROM mytable
GROUP BY日期
ORDER BY grade DESC
,但返回如下所示:
当前不需要的结果:
id |日期|名称|等级
-------- + --------------- + ----------- + -------- -------
1 | 2010/12/03 |迈克| 12
2 | 2010/12/04 |珍妮| 12
我搜索了,我发现原因:
GROUP BY在ORDER BY之前发生,所以它不会看到也不能应用ORDER。
所以我如何应用ORDER GROUP BY? 请注意:请注意,我需要最简单的查询,因为我的查询其实非常复杂,我知道我可以通过一些子查询或JOINing实现此结果,但是我想知道如何将ORDER应用于GROUP BY。谢谢
我在这个例子中使用了Oracle,但是SQL应该在mysql中工作(您可能需要调整to_date的内容使用mysql)。
CREATE TABLE mytable(ID NUMBER,dt DATE,NAME VARCHAR2( 25),等级NUMBER);
INSERT INTO mytable VALUES(1,to_date('2010-12-03','YYYY-MM-DD'),'Mike',12);
INSERT INTO mytable VALUES(1,to_date('2010-12-04','YYYY-MM-DD'),'Jenny',12);
INSERT INTO mytable VALUES(1,to_date('2010-12-04','YYYY-MM-DD'),'Ronald',15);
INSERT INTO mytable VALUES(1,to_date('2010-12-03','YYYY-MM-DD'),'Yeni',11);
SELECT id
,dt
,name
,grade
FROM mytable t1
WHERE grade =(SELECT max(grade)
FROM mytable t2
WHERE t1.dt = t2.dt)
ORDER BY dt
结果:
ID DT名称等级
1 12/3/2010 Mike 12
2 12/4/2010 Ronald 15
assume following data:
Data:
id | date | name | grade
--------+---------------+-----------+---------------
1 | 2010/12/03 | Mike | 12
2 | 2010/12/04 | Jenny | 12
3 | 2010/12/04 | Ronald | 15
4 | 2010/12/03 | Yeni | 11
i want to know who has the best grade in each day, something like this:
Desired Result:
id | date | name | grade
--------+---------------+-----------+---------------
1 | 2010/12/03 | Mike | 12
3 | 2010/12/04 | Ronald | 15
i thought query should look like this:
SELECT name FROM mytable
GROUP BY date
ORDER BY grade DESC
but it returns something like this:
Current Unwanted Result:
id | date | name | grade
--------+---------------+-----------+---------------
1 | 2010/12/03 | Mike | 12
2 | 2010/12/04 | Jenny | 12
i searched and i found the reason:
GROUP BY happens before ORDER BY so it does not see and can't apply ORDER.
so how can i apply ORDER on GROUP BY?
Note: please keep in mind that i need the most simple query, because my query is actually very complex, i know i can achieve this result by some subquery or JOINing, but i want to know how to apply ORDER to GROUP BY. thanks
I used Oracle for this example, but the SQL should work in mysql (you may need to tweak the to_date stuff to work with mysql). You really need a subquery here to do what you are asking.
CREATE TABLE mytable (ID NUMBER, dt DATE, NAME VARCHAR2(25), grade NUMBER);
INSERT INTO mytable VALUES(1,to_date('2010-12-03','YYYY-MM-DD'),'Mike',12);
INSERT INTO mytable VALUES(1,to_date('2010-12-04','YYYY-MM-DD'),'Jenny',12);
INSERT INTO mytable VALUES(1,to_date('2010-12-04','YYYY-MM-DD'),'Ronald',15);
INSERT INTO mytable VALUES(1,to_date('2010-12-03','YYYY-MM-DD'),'Yeni',11);
SELECT id
, dt
, name
, grade
FROM mytable t1
WHERE grade = (SELECT max(grade)
FROM mytable t2
WHERE t1.dt = t2.dt)
ORDER BY dt
Results:
ID DT NAME GRADE
1 12/3/2010 Mike 12
2 12/4/2010 Ronald 15
这篇关于MySQL:如何GROUP BY一个字段检索ORDER BY另一个字段的行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!