MYSQL GROUP BY 具有特定列的多个不同值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14296545/
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
MYSQL GROUP BY multiple different values with specific column
提问by richard
MYSQL Structure:
MYSQL 结构：
ID | USERID | FRIENDID | Type
-------------------------------
1 | 10 | 20 | Gold
2 | 20 | 10 | Gold
3 | 30 | 40 | Silver
4 | 40 | 30 | Silver
5 | 50 | 60 | Gold
6 | 60 | 50 | Gold
7 | 70 | 80 | Bronze
8 | 80 | 70 | Bronze
9 | 90 | 100 | Bronze
10 | 100 | 90 | Bronze
What i want is GROUP (ID 1 & ID 2) and (ID 5 & ID 6) because they are "gold" type, NOT GROUP BY TYPE.
我想要的是 GROUP (ID 1 & ID 2) 和 (ID 5 & ID 6) 因为它们是“黄金”类型，而不是 GROUP BY TYPE。
Return Results:
返回结果：
1. 10 & 20, type:gold. (GROUP)
3. 30 & 40, type:silver.
4. 40 & 30, type:silver.
5. 50 & 60, type:gold. (GROUP)
7. 70 & 80, type:bronze.
8. 80 & 70, type:bronze.
9. 90 & 100, type:bronze.
10. 100 & 90, type:bronze.
How to do that with php query?
如何使用 php 查询做到这一点？
回答by Gordon Linoff
What you need to do is to add an additional grouping clause based on the type. When it is 'gold', you get a constant. Otherwise, you use the id:
您需要做的是根据类型添加额外的分组子句。当它是“黄金”时，你会得到一个常数。否则，您使用 id：
select least(userid, friendid), greatest(userid, friendid), type
from t
group by least(userid, friendid), greatest(userid, friendid),
(case when type = 'gold' then 0 else id end)
This does rearrange the order of the ids for non-gold types. If ordering is important, the SQL is a little more complicated:
这确实重新排列了非黄金类型的 id 的顺序。如果排序很重要，则 SQL 会稍微复杂一些：
select (case when type = 'gold' then least(userid, friendid) else userid end),
(case when type = 'gold' then greatest(userid, friendid) else friendid end),
type
from t
group by least(userid, friendid), greatest(userid, friendid),
(case when type = 'gold' then 0 else id end)
回答by Saharsh Shah
Try this:
尝试这个：
SELECT id, userid, friendid, type
FROM (SELECT id, userid, friendid, type,
IF(LOWER(type) = 'gold', IF(@lasttype=(@lasttype:=TYPE), @auto, @auto:=@auto+1), @auto:=@auto+1) indx
FROM tablename, (SELECT @auto:=1, @lasttype:=0) a
ORDER BY id) a
GROUP BY indx;
