MS-SQL对输出进行降序排序 [英] MS-SQL sort output in descending order
本文介绍了MS-SQL对输出进行降序排序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这个MS-SQL查询,数据库中有成千上万的行记录:
SELECT DISTINCT TOP 7 DATENAME(MM,mydatetime)+''+ CAST(DAY(mydatetime)AS VARCHAR(2))作为date
,MONTH(mydatetime)
,DAY(mydatetime)
,COUNT(页)计为totalcount
,计数为(DISTINCT页)作为来访者
从someTable
所在页面,如'%AEC%'
GROUP BY DATENAME(MM,mydatetime)+''+ (DAY(mydatetime)AS VARCHAR(2))
,MONTH(mydatetime)
,DAY(mydatetime)
按MONTH(mydatetime)DESC
,DAY(mydatetime)DESC排序
它将输出以下内容:
日期|总数|访客
-----------------------------------
10月17日| 4 | 1
10月15日| 1 | 1
10月12日| 1 | 1
10月3日| 3 | 3
10月2日| 42 | 22
9月28日| 2 | 1
9月21日| 14 | 10
我的问题是我只是不能按降序输出它,所以它看起来像这样:
日期|总数|访客
-----------------------------------
9月21日| 14 | 10
9月28日| 2 | 1
10月2日| 42 | 22
10月3日| 3 | 3
10月12日| 1 | 1
10月15日| 1 | 1
10月17日| 4 | 1
因此,非常感谢您的帮助。
解决方案
对于您的实际需求,您可以将当前查询用作派生表并按所需方式对结果进行排序:
选择*
FROM(选择不同的前7个DATENAME(mm,mydatetime)+''
+ CAST(DAY(mydatetime)AS VARCHAR(2))作为日期,
MONTH(mydatetime)作为月,
DAY(mydatetime)作为theDay,
COUNT(page)作为totalcount,
COUNT(DISTINCT页)AS访问者
来自某个表
WHERE页,如'%AEC%'
GROUP BY DATENAME(mm,mydatetime)+''
+ CAST(DAY) (mydatetime)AS VARCHAR(2)),
MONTH(mydatetime),
DAY(mydatetime)
MON ORDER TH(mydatetime)DESC,
DAY(mydatetime)DESC)
订单,按当月,当天I have this MS-SQL query with thousands of row records in database:
SELECT DISTINCT TOP 7 DATENAME(MM, mydatetime) + ' ' + CAST(DAY(mydatetime) AS VARCHAR(2)) as thedate
, MONTH(mydatetime)
, DAY(mydatetime)
, COUNT(Page) as totalcount
, count(DISTINCT Page) as visitors
FROM someTable
WHERE Page LIKE '%AEC%'
GROUP BY DATENAME(MM, mydatetime) + ' ' + CAST(DAY(mydatetime) AS VARCHAR(2))
, MONTH(mydatetime)
, DAY(mydatetime)
ORDER BY MONTH(mydatetime) DESC
, DAY(mydatetime) DESC
It will output this:
thedate | totalcount | visitors
-----------------------------------
October 17 | 4 | 1
October 15 | 1 | 1
October 12 | 1 | 1
October 3 | 3 | 3
October 2 | 42 | 22
September 28| 2 | 1
September 21| 14 | 10
My problem is that I simply cant output this in descending order so it will look like this:
thedate | totalcount | visitors
-----------------------------------
September 21| 14 | 10
September 28| 2 | 1
October 2 | 42 | 22
October 3 | 3 | 3
October 12 | 1 | 1
October 15 | 1 | 1
October 17 | 4 | 1
So any help is greatly appreciated.
解决方案 For your actual requirement, you can use your current query as a derived table and order that result in the way you want:
SELECT *
FROM ( SELECT DISTINCT TOP 7 DATENAME(mm, mydatetime) + ' '
+ CAST(DAY(mydatetime) AS VARCHAR(2)) AS thedate,
MONTH(mydatetime) AS theMonth,
DAY(mydatetime) AS theDay,
COUNT(page) AS totalcount,
COUNT(DISTINCT page) AS visitors
FROM sometable
WHERE page LIKE '%AEC%'
GROUP BY DATENAME(mm, mydatetime) + ' '
+ CAST(DAY(mydatetime) AS VARCHAR(2)),
MONTH(mydatetime),
DAY(mydatetime)
ORDER BY MONTH(mydatetime) DESC,
DAY(mydatetime) DESC) A
ORDER BY theMonth, theDay
这篇关于MS-SQL对输出进行降序排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文