一个MySQL语句问题

Question

下面SQL查询出来的结果如下：

SELECT * FROM item_img_spec WHERE item_id = 2 ORDER BY sort ASC, item_img_spec_id DESC

怎么让他 spec_id 相同的只显示一条

变成这样

---

SELECT * FROM item_img_spec WHERE item_id = 2 GROUP BY spec_id ORDER BY sort ASC, item_img_spec_id DESC

谢谢大家，直接这样就可以啦

Answer 1

SELECT distinct spec_id FROM item_img_spec WHERE item_id = 2 ORDER BY sort ASC, item_img_spec_id DESC

Answer 2

SELECT distinct spec_id ,item_id,img_id,spec_id FROM item_img_spec WHERE item_id = 2 ORDER BY sort ASC, item_img_spec_id DESC

对spec_id去重试试

Answer 3

需要做多次的 Group By 才能得到:

SELECT item_img_spec_id, spec_id ,item_id,img_id,sort FROM item_img_spec A
WHERE A.item_id = 2
AND EXISTS(
        SELECT 1 FROM item_img_spec B Group BY spec_id
        HAVING A.sort = Min(B.sort)    
    )
AND EXISTS(
        SELECT 1 FROM item_img_spec C Group BY C.sort
        HAVING A.item_img_spec_id = MAX(C.item_img_spec_id)
    ) 
ORDER BY sort ASC;

Answer 4

如果item_img_spec_id是表的主键，可以这样写，在spec_id相同保留的情况下保留item_img_spec_id最大的记录

SELECT * FROM item_img_spec t1
    INNER JOIN (
        SELECT spec_id, max(item_img_spec_id)
        FROM item_img_spec 
        WHERE item_id = 2
        GROUP BY spec_id
    ) t2
    ON t1.item_img_spec_id = t2.item_img_spec_id
ORDER BY t1.sort ASC, t1.item_img_spec_id DESC