gpt4 book ai didi

SQL Server按不同字段的顺序选择组

转载 作者:行者123 更新时间:2023-12-02 22:05:09 24 4
gpt4 key购买 nike

我有一张 table :

Id    Date        color
-------------------------
1 23/04/2013 red
2 23/04/2013 white
3 23/04/2013 yellow
4 23/04/2013 red
5 23/04/2013 orange
6 23/04/2013 blue
7 23/04/2013 yellow
8 23/04/2013 red

我按颜色分组并按总数和颜色排序:

Select top 5 color, count(color) as total from table where Date<=getdate() group by color order by total desc, color asc

所以直到这里,一切都正常。

现在,我想要相同的,但不是按颜色排序。我想按总数排序,然后按日期排序。但我不想按日期分组。

最佳答案

试试这个 -

DECLARE @temp TABLE
(
Id INT IDENTITY(1,1)
, [Date] DATETIME
, color NVARCHAR(50)
)

INSERT INTO @temp ([Date], color)
VALUES
('20130423', 'red'),
('20130422', 'white'),
('20130423', 'yellow'),
('20130423', 'red'),
('20130425', 'orange'),
('20130423', 'blue'),
('20130423', 'yellow'),
('20130423', 'red')

SELECT TOP 5 color, total = COUNT(color)
FROM @temp
WHERE [Date] <= GETDATE()
GROUP BY color
ORDER BY total DESC, MAX([Date])

关于SQL Server按不同字段的顺序选择组,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/16250908/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com