按多列排序 [英] order by multiple columns
问题描述
我需要按两列对数据进行排序，我该怎么做?
I need to order data by two columns, How do i do that?
这是我的桌子:
Name | ImpFile | ImpTime
Sam Imp01 2012-05-16 09:54:02.477
Ann Imp01 2012-05-16 09:54:02.478
Mark Imp01 2012-05-16 09:54:02.477
John Import12 2012-05-16 09:55:37.384
Bart Import12 2012-05-16 09:55:37.387
Sasha Import12 2012-05-16 09:55:37.385
我需要按 ImpTime 和 ImpName 对这个表进行排序，它应该是这样的:
I need to sort this table by ImpTime and ImpName and it should look like this:
Name | ImpFile | ImpTime
Import12 2012-05-16 09:55:37.387
Bart Import12 2012-05-16 09:55:37.387
John Import12 2012-05-16 09:55:37.384
Sasha Import12 2012-05-16 09:55:37.385
Imp01 2012-05-16 09:54:02.478
Ann Imp01 2012-05-16 09:54:02.478
Mark Imp01 2012-05-16 09:54:02.477
Sam Imp01 2012-05-16 09:54:02.477
我正在使用此查询，但它不按名称对表进行排序，当多行的时间值相同时，仅按名称排序.
I am using this query, but it does not order the table by Name, in only orders by name when the time is the same value for multiple rows.
select Name, ImpFile, ImpTime
from people
union
select distinct '', ImpFile, max(ImpTime)
from people
group by ImpFile
order by ImpTime desc, Name
这个查询给了我这样的表:
This query gives me table like this:
Name | ImpFile | ImpTime
Import12 2012-05-16 09:55:37.387
John Import12 2012-05-16 09:55:37.384
Bart Import12 2012-05-16 09:55:37.387
Sasha Import12 2012-05-16 09:55:37.385
Imp01 2012-05-16 09:54:02.478
Sam Imp01 2012-05-16 09:54:02.477
Ann Imp01 2012-05-16 09:54:02.478
Mark Imp01 2012-05-16 09:54:02.477
有没有办法同时按这两列排序?
Is there any way to order by those two column at the same time?
编辑当我使用 order by ImpFile DESC, ImpTime desc 时会发生什么?
它给了我一个这样的结果表:
EDIT
What happens when I use order by ImpFile DESC, ImpTime desc?
It gives me a result table like this:
Name | ImpFile | ImpTime
Import12 2012-05-16 09:55:37.387
Imp01 2012-05-16 09:54:02.478
Bart Import12 2012-05-16 09:55:37.387
John Import12 2012-05-16 09:55:37.384
Sasha Import12 2012-05-16 09:55:37.385
Ann Imp01 2012-05-16 09:54:02.478
Mark Imp01 2012-05-16 09:54:02.477
Sam Imp01 2012-05-16 09:54:02.477
推荐答案
获取各组组长并按时间降序排序:
Get the leader of each group and sort them by descending time:
with grp(Name,ImpFile,TimeGroup,ImpTime) as
(
select cast(null as varchar(5)), ImpFile, max(ImpTime) as TimeGroup,
max(ImpTime) as ImpTime
from people
group by ImpFile
)
select *
from grp
order by TimeGroup desc;
输出:
NAME IMPFILE TIMEGROUP IMPTIME
(null) Import12 2012-05-16 09:55:37.3870000 2012-05-16 09:55:37.3870000
(null) Imp01 2012-05-16 09:54:02.4780000 2012-05-16 09:54:02.4780000
然后将followers加入leader并获取leader的时间(TimeGroup):
Then join the followers to leader and get the leader's time(TimeGroup):
with grp(Name,ImpFile,TimeGroup,ImpTime) as
(
select cast(null as varchar(5)), ImpFile, max(ImpTime) as TimeGroup,
max(ImpTime) as ImpTime
from people
group by ImpFile
union all
select p.Name, p.ImpFile, ldr.TimeGroup, p.ImpTime
from people p
inner join grp ldr -- leader
on ldr.name is null and ldr.ImpFile = p.ImpFile
)
select Name, ImpFile, ImpTime
from grp
order by TimeGroup desc, Name
输出:
NAME IMPFILE IMPTIME
(null) Import12 2012-05-16 09:55:37.3870000
Bart Import12 2012-05-16 09:55:37.3870000
John Import12 2012-05-16 09:55:37.3840000
Sasha Import12 2012-05-16 09:55:37.3850000
(null) Imp01 2012-05-16 09:54:02.4780000
Ann Imp01 2012-05-16 09:54:02.4780000
Mark Imp01 2012-05-16 09:54:02.4770000
Sam Imp01 2012-05-16 09:54:02.4770000
查询的逻辑是，我们根据 ImpFile 将追随者(有名字的人)的时间与其领导者的时间(TimeGroup)对齐.Leader和它的follower有相同的时间组，所以当我们按时间排序时，他们会互相粘在一起；之后，我们按名称排序
The logic of the query is, we align the followers(those with name)'s time to their leader's time(TimeGroup) based on ImpFile. Leader and its followers have same time group, so when we sort them by time, they will stick to each other; then after that, we sort by name
现场测试:http://www.sqlfiddle.com/#!3/c7859/21
如果我们希望组长出现在它的追随者之后，只需在 ORDER BY 上放置一个案例:
If we want the group leader to appear after its followers, just put a case when on ORDER BY:
with grp(Name,ImpFile,TimeGroup,ImpTime) as
(
select cast(null as varchar(5)), ImpFile, max(ImpTime) as TimeGroup,
max(ImpTime) as ImpTime
from people
group by ImpFile
union all
select p.Name, p.ImpFile, ldr.TimeGroup, p.ImpTime
from people p
inner join grp ldr -- leader
on ldr.name is null and ldr.ImpFile = p.ImpFile
)
select Name, ImpFile, ImpTime
from grp
order by TimeGroup desc,
case
when Name is null then 2 -- leader last
else 1 -- followers first
end,
Name
输出:
NAME IMPFILE IMPTIME
Bart Import12 2012-05-16 09:55:37.3870000
John Import12 2012-05-16 09:55:37.3840000
Sasha Import12 2012-05-16 09:55:37.3850000
(null) Import12 2012-05-16 09:55:37.3870000
Ann Imp01 2012-05-16 09:54:02.4780000
Mark Imp01 2012-05-16 09:54:02.4770000
Sam Imp01 2012-05-16 09:54:02.4770000
(null) Imp01 2012-05-16 09:54:02.4780000
现场测试:http://www.sqlfiddle.com/#!3/c7859/23
工作原理:
with grp(Name,ImpFile,TimeGroup,ImpTime) as
(
select cast(null as varchar(5)), ImpFile, max(ImpTime) as TimeGroup,
max(ImpTime) as ImpTime
from people
group by ImpFile
union all
select p.Name, p.ImpFile, ldr.TimeGroup, p.ImpTime
from people p
inner join grp ldr -- leader
on ldr.name is null and ldr.ImpFile = p.ImpFile
)
select *
from grp
order by TimeGroup desc, Name;
输出:
NAME IMPFILE IMPTIME TIMEGROUP
(null) Import12 2012-05-16 09:55:37.3870000 2012-05-16 09:55:37.3870000
Bart Import12 2012-05-16 09:55:37.3870000 2012-05-16 09:55:37.3870000
John Import12 2012-05-16 09:55:37.3840000 2012-05-16 09:55:37.3870000
Sasha Import12 2012-05-16 09:55:37.3850000 2012-05-16 09:55:37.3870000
(null) Imp01 2012-05-16 09:54:02.4780000 2012-05-16 09:54:02.4780000
Ann Imp01 2012-05-16 09:54:02.4780000 2012-05-16 09:54:02.4780000
Mark Imp01 2012-05-16 09:54:02.4770000 2012-05-16 09:54:02.4780000
Sam Imp01 2012-05-16 09:54:02.4770000 2012-05-16 09:54:02.4780000
现场测试:http://www.sqlfiddle.com/#!3/c7859/25
这篇关于按多列排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
