php - 在 MySql 中，SELECT all with DIFFERENT id?

Question

我有一张 table ，

(请注意，这个问题听起来可能重复，但我在发布之前检查了其他问题...如果您在其他帖子中找到答案，建议> 我而不是 downvoting。我保证删除它以防万一。thx)

|tid |name|age|sex|pack_id
 1   |Joe |57 |M  |77
 2   |Joe |57 |M  |77
 3   |Joe |57 |M  |89
 4   |Joe |57 |M  |89
 5   |Joe |57 |M  |94

 6   |Roy |98 |M  |7
 7   |Roy |98 |M  |8
 8   |Roy |98 |M  |9
 9   |Roy |98 |M  |7
 10  |Roy |98 |M  |7
 11  |Roy |98 |M  |7

 12  |Joe |46 |M  |96

 13  |Guh |97 |F  |1

 14  |Kin |68 |F  |33
 15  |Kin |68 |F  |37
 16  |Kin |68 |F  |37
 17  |Kin |68 |F  |37
 18  |Kin |68 |F  |35
 19  |Kin |68 |F  |37

 20  |Pit |43 |F  |77
 21  |Pit |43 |F  |1
 22  |Pit |43 |F  |2
 23  |Pit |43 |F  |1
 24  |Pit |43 |F  |77

 25  |Joe |71 |M  |75

 26  |Bob |82 |M  |77

 27  |Guh |97 |F  |20
 28  |Guh |97 |F  |28
 29  |Guh |97 |F  |28
 30  |Guh |97 |F  |23

我的目标是拥有一个不会产生重复 pack_id 的查询像这样的东西:

|tid |name|age|sex|pack_id
 1   |Joe |57 |M  |77    
 3   |Joe |57 |M  |89
 5   |Joe |57 |M  |94

 6   |Roy |98 |M  |7
 7   |Roy |98 |M  |8
 8   |Roy |98 |M  |9

 12  |Joe |46 |M  |96

 13  |Guh |97 |F  |1

 14  |Kin |68 |F  |33
 18  |Kin |68 |F  |35
 19  |Kin |68 |F  |37

 20  |Pit |43 |F  |77
 21  |Pit |43 |F  |1
 22  |Pit |43 |F  |2

 25  |Joe |71 |M  |75

 26  |Bob |82 |M  |77

 27  |Guh |97 |F  |20
 28  |Guh |97 |F  |28

 30  |Guh |97 |F  |23

我试过:

SELECT `tid` ,`name`,`age`,`sex`,`pack_id` FROM `myTable` group by `pack_id` //FAILED

也试过这个:

SELECT DISTINCT(`pack_id`) ,`name`,`age`,`sex`,`pack_id` FROM `myTable` group by `pack_id`// RETURNS ONE SINGLE 'name' MANY TIMES

尝试过

MySQL select distinct where in all

尝试过

How can i get all the unique id having more than one id with different address

尝试了更多...

还是做不到。

Answer 1

听起来您需要在两个级别上GROUP BY:name 和pack_id。例如:

SELECT tid,name,age,sex,pack_id FROM yourtable GROUP BY name, pack_id;

参见 reference manual更多详情