我有一个名为category的表,该表具有3列：id,name,parent_id.
ID name group_id
== == ==
1 haim 1
2 gil 2
3 alon 1
4 idan 1
5 shai 3
6 adi 2
7 itay 3
8 aviram 1
9 lioz 3
10 amit 2
11 ben 2
12 eran 1
我需要在一个查询中选择每个group_id 2行,所以结果将是：
ID name group_id
== == ==
1 haim 1
3 alon 1
2 gil 2
6 adi 2
5 shai 3
7 itay 3
非常重要的一点是,它也将按group_id进行排序.
谢谢！
最佳答案
我考虑了每个group_id在表中找到的第一个和第二个ID.
原文链接：https://www.f2er.com/mysql/532175.htmlSELECT ID,NAME,GROUP_ID
FROM MYTABLE WHERE ID IN (
SELECT MIN(ID)
FROM MYTABLE
GROUP BY GROUP_ID)
UNION ALL
SELECT ID,GROUP_ID
FROM MYTABLE WHERE ID IN (
SELECT MIN(ID)
FROM MYTABLE
WHERE ID NOT IN (
SELECT MIN(ID)
FROM MYTABLE
GROUP BY GROUP_ID
)
GROUP BY GROUP_ID)
ORDER BY GROUP_ID