SQL 中 GROUP BY 的排序问题

Question

SELECT TOP 10 tb_show.showId, tb_Show.Title, tb_Show.PageName, Quantity
FROM         tb_OrderItem INNER JOIN
                        (SELECT * FROM tb_Show WHERE IsDeleted = 0 AND PrivateShow = 0 AND Archive = 0 ) tb_Show
                      ON tb_OrderItem.ShowId = tb_Show.showId             
GROUP BY tb_show.showId, tb_Show.Title, tb_Show.PageName, Quantity
ORDER BY Quantity DESC 

• 160 dsadsadsadsa dsadsadsadsa 3
• 160 dsadsadsadsa dsadsadsadsa 2
• 150 基于 css 的地图测试事件 css-based-map-test-event 1
• 159 交叉空心测试交叉-hollows-test 1

---

但我不想重复ID ie 160。所以我将其设置为子查询，并且仅获得带有 DISTINCT 关键字的三列。问题是，当我将其移动到带有 DISTINCT 关键字的子查询时，按数量排序不起作用，而没有排序是可以的，但是不同的关键字删除排序...

---

SELECT DISTINCT TOP 10 showId, Title, PageName  from 
( SELECT TOP 10 tb_show.showId, tb_Show.Title, tb_Show.PageName, Quantity
FROM         tb_OrderItem INNER JOIN
                        (SELECT * FROM tb_Show WHERE IsDeleted = 0 AND PrivateShow = 0 AND Archive = 0 ) tb_Show
                      ON tb_OrderItem.ShowId = tb_Show.showId             
GROUP BY tb_show.showId, tb_Show.Title, tb_Show.PageName, Quantity
ORDER BY Quantity DESC ) tb_show

• 150 基于 css 的地图测试事件 css-based-map-test-event
• 159 交叉空心测试 cross-hollows-test
• 160萨德萨德萨萨德萨德萨德萨

Answer 1

这将为您提供第二个示例中的输出。不同子查询对您不起作用的原因是因为您还按数量进行分组，这导致输出单独的不同行。

SELECT TOP 10 tb_show.showId, tb_Show.Title, tb_Show.PageName, MAX(quantity) as Quantity
FROM tb_OrderItem 
    INNER JOIN tb_Show 
        ON tb_OrderItem.ShowId = tb_Show.showId 
WHERE tb_show.IsDeleted = 0 AND tb_show.PrivateShow = 0 AND tb_show.Archive = 0 
GROUP BY tb_show.showId, tb_Show.Title, tb_Show.PageName
ORDER BY MAX(quantity)