22,297
社区成员
发帖
与我相关
我的任务
分享解决就给100分（排序问题，有点难度）
如题：表T，有
列c1（姓名），列c2（时间），原数据如下：
D 2009-11-1 20:20:11
A 2009-12-1 20:20:11
A 2009-12-1 20:21:12
B 2009-12-1 20:23:01
A 2009-12-1 20:23:09
B 2009-12-1 20:25:44
B 2009-12-1 20:26:56
C 2009-12-1 20:29:11
D 2009-12-2 20:33:19
A 2009-12-4 20:33:19
先按最前时间把D排序出来，然后把D的记录紧跟排在一起；再排比D最前时间下一个时间的A，再把A的记录紧跟排在一起；如此类推，排序下去……的结果如下：
D 2009-11-1 20:20:11
D 2009-12-2 20:33:19
A 2009-12-1 20:20:11
A 2009-12-1 20:21:12
A 2009-12-1 20:33:19
A 2009-12-4 20:23:09
B 2009-12-1 20:23:01
B 2009-12-1 20:25:44
B 2009-12-1 20:26:56
C 2009-12-1 20:29:11
列c1（姓名），列c2（时间），原数据如下：
D 2009-11-1 20:20:11
A 2009-12-1 20:20:11
A 2009-12-1 20:21:12
B 2009-12-1 20:23:01
A 2009-12-1 20:23:09
B 2009-12-1 20:25:44
B 2009-12-1 20:26:56
C 2009-12-1 20:29:11
D 2009-12-2 20:33:19
A 2009-12-4 20:33:19
先按最前时间把D排序出来，然后把D的记录紧跟排在一起；再排比D最前时间下一个时间的A，再把A的记录紧跟排在一起；如此类推，排序下去……的结果如下：
D 2009-11-1 20:20:11
D 2009-12-2 20:33:19
A 2009-12-1 20:20:11
A 2009-12-1 20:21:12
A 2009-12-1 20:33:19
A 2009-12-4 20:23:09
B 2009-12-1 20:23:01
B 2009-12-1 20:25:44
B 2009-12-1 20:26:56
C 2009-12-1 20:29:11
...全文
请发表友善的回复…
发表回复
cs88dn 2010-02-06
- 打赏
- 举报
学习了，不错！
ACMAIN_CHM 2010-02-06
- 打赏
- 举报
select a.列c1,a.列c2,max(b.列c2)
from 表T a, 表T b
where a.列c1=b.列c1
group by a.列c1,a.列c2
group by 3 desc,2 desc
from 表T a, 表T b
where a.列c1=b.列c1
group by a.列c1,a.列c2
group by 3 desc,2 desc
东那个升 2010-02-06
- 打赏
- 举报
if object_id('[Tb]') is not null drop table [Tb]
go
create table [Tb]([c1] varchar(1),[c2] datetime)
insert [Tb]
select 'D','2009-11-1 20:20:11' union all
select 'A','2009-12-1 20:20:11' union all
select 'A','2009-12-1 20:21:12' union all
select 'B','2009-12-1 20:23:01' union all
select 'A','2009-12-1 20:23:09' union all
select 'B','2009-12-1 20:25:44' union all
select 'B','2009-12-1 20:26:56' union all
select 'C','2009-12-1 20:29:11' union all
select 'D','2009-12-2 20:33:19' union all
select 'A','2009-12-4 20:33:19'
select *,identity(int,1,1) as rn into #tt from Tb
select a.c1,a.c2 from #tt a ,(
select c1,MIN(rn) as rn from #tt
group by c1) b
where a.c1=b.c1
order by b.rn ,a.rn
bzhi268 2010-02-06
- 打赏
- 举报
问题补充：
上面的数据只是表中的部分数据，实际数据有几万条
上面的数据只是表中的部分数据，实际数据有几万条
丰云 2010-02-04
- 打赏
- 举报
[Quote=引用 13 楼 foren_whb 的回复:]
简单的多条件排序而已，如下就可以了：
select c1,c2 from
order by c2,c1
[/Quote]
写漏了。。。
select c1,c2 from T
order by c2,c1
简单的多条件排序而已，如下就可以了：
select c1,c2 from
order by c2,c1
[/Quote]
写漏了。。。
select c1,c2 from T
order by c2,c1
bancxc 2010-02-04
- 打赏
- 举报
create table tb(c1 varchar(10),c2 datetime)
insert into tb select
'D','2009-11-1 20:20:11' union all select
'A','2009-12-1 20:20:11' union all select
'A','2009-12-1 20:21:12' union all select
'B','2009-12-1 20:23:01' union all select
'A','2009-12-1 20:23:09' union all select
'B','2009-12-1 20:25:44' union all select
'B','2009-12-1 20:26:56' union all select
'C','2009-12-1 20:29:11' union all select
'D','2009-12-2 20:33:19' union all select
'A','2009-12-4 20:33:19'
select * from tb
order by case c1 when 'D' then 0 else 1 end asc ,c1
c1 c2
---------- -----------------------
D 2009-11-01 20:20:11.000
D 2009-12-02 20:33:19.000
A 2009-12-04 20:33:19.000
A 2009-12-01 20:20:11.000
A 2009-12-01 20:21:12.000
A 2009-12-01 20:23:09.000
B 2009-12-01 20:25:44.000
B 2009-12-01 20:26:56.000
B 2009-12-01 20:23:01.000
C 2009-12-01 20:29:11.000
(10 行受影响)丰云 2010-02-04
- 打赏
- 举报
简单的多条件排序而已，如下就可以了：
select c1,c2 from
order by c2,c1
快乐_石头 2010-02-04
- 打赏
- 举报
用charindex有點牽強
黄_瓜 2010-02-04
- 打赏
- 举报
[Quote=引用 5 楼 chuifengde 的回复:]
SQL codeDECLARE@atable(c1varchar(20),bSMALLDATETIME)INSERT@aSELECT'D' ,'2009-11-1 20:20:11'unionallselect'A' ,'2009-12-1 20:20:11'unionallselect'A' ,'2009-12-1 20:21:12'unionallselect'B' ,?-
[/Quote]
学习
SQL codeDECLARE@atable(c1varchar(20),bSMALLDATETIME)INSERT@aSELECT'D' ,'2009-11-1 20:20:11'unionallselect'A' ,'2009-12-1 20:20:11'unionallselect'A' ,'2009-12-1 20:21:12'unionallselect'B' ,?-
[/Quote]
学习
bancxc 2010-02-04
- 打赏
- 举报
create table tb(c1 varchar(10),c2 datetime)
insert into tb select
'D','2009-11-1 20:20:11' union all select
'A','2009-12-1 20:20:11' union all select
'A','2009-12-1 20:21:12' union all select
'B','2009-12-1 20:23:01' union all select
'A','2009-12-1 20:23:09' union all select
'B','2009-12-1 20:25:44' union all select
'B','2009-12-1 20:26:56' union all select
'C','2009-12-1 20:29:11' union all select
'D','2009-12-2 20:33:19' union all select
'A','2009-12-4 20:33:19'
select * from tb
order by case c1 when 'D' then '' else c1 end
c1 c2
---------- -----------------------
D 2009-11-01 20:20:11.000
D 2009-12-02 20:33:19.000
A 2009-12-04 20:33:19.000
A 2009-12-01 20:20:11.000
A 2009-12-01 20:21:12.000
A 2009-12-01 20:23:09.000
B 2009-12-01 20:25:44.000
B 2009-12-01 20:26:56.000
B 2009-12-01 20:23:01.000
C 2009-12-01 20:29:11.000
(10 行受影响)
chuifengde 2010-02-04
- 打赏
- 举报
上面的改一点：
SELECT * FROM @a a
ORDER BY (SELECT min(b) FROM @a WHERE c1=a.c1),b
--小F-- 2010-02-04
- 打赏
- 举报
----------------------------------------------------------------
-- Author :fredrickhu(我是小F，向高手学习)
-- Date :2010-02-04 11:19:26
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([c1] varchar(1),[c2] datetime)
insert [tb]
select 'D','2009-11-1 20:20:11' union all
select 'A','2009-12-1 20:20:11' union all
select 'A','2009-12-1 20:21:12' union all
select 'B','2009-12-1 20:23:01' union all
select 'A','2009-12-1 20:23:09' union all
select 'B','2009-12-1 20:25:44' union all
select 'B','2009-12-1 20:26:56' union all
select 'C','2009-12-1 20:29:11' union all
select 'D','2009-12-2 20:33:19' union all
select 'A','2009-12-4 20:33:19'
--------------开始查询--------------------------
--select id=row_number()over(partition by c1 order by c2),* from tb
select
c1,c2
from
(select id=row_number()over(partition by c1 order by c2),* from tb)t
order by
charindex(','+c1+',',',d,a,b,c,'),c2
----------------结果----------------------------
/* ---- -----------------------
D 2009-11-01 20:20:11.000
D 2009-12-02 20:33:19.000
A 2009-12-01 20:20:11.000
A 2009-12-01 20:21:12.000
A 2009-12-01 20:23:09.000
A 2009-12-04 20:33:19.000
B 2009-12-01 20:23:01.000
B 2009-12-01 20:25:44.000
B 2009-12-01 20:26:56.000
C 2009-12-01 20:29:11.000
(10 行受影响)
*/快乐_石头 2010-02-04
- 打赏
- 举报
[Quote=引用 3 楼 josy 的回复:]
SQL code---测试数据---ifobject_id('[T]')isnotnulldroptable[T]gocreatetable[T]([c1]varchar(1),[c2]datetime)insert[T]select'D','2009-11-1 20:20:11'unionallselect'A','2009-12-1 20:20:11'unionallselect'A','20?-
[/Quote]
頂~~
SQL code---测试数据---ifobject_id('[T]')isnotnulldroptable[T]gocreatetable[T]([c1]varchar(1),[c2]datetime)insert[T]select'D','2009-11-1 20:20:11'unionallselect'A','2009-12-1 20:20:11'unionallselect'A','20?-
[/Quote]
頂~~
steptodream 2010-02-04
- 打赏
- 举报
chuifengde 2010-02-04
- 打赏
- 举报
DECLARE @a table(c1 varchar(20),b SMALLDATETIME)
INSERT @a SELECT 'D' ,'2009-11-1 20:20:11'
union all select 'A' ,'2009-12-1 20:20:11'
union all select 'A' ,'2009-12-1 20:21:12'
union all select 'B' ,'2009-12-1 20:23:01'
union all select 'A' ,'2009-12-1 20:23:09'
union all select 'B' ,'2009-12-1 20:25:44'
union all select 'B' ,'2009-12-1 20:26:56'
union all select 'C' ,'2009-12-1 20:29:11'
union all select 'D' ,'2009-12-2 20:33:19'
union all select 'A' ,'2009-12-4 20:33:19'
SELECT * FROM @a a
ORDER BY (SELECT min(b) FROM @a WHERE c1=a.c1)
--result
/*
c1 b
-------------------- ------------------------------------------------------
D 2009-11-01 20:20:00
D 2009-12-02 20:33:00
A 2009-12-04 20:33:00
A 2009-12-01 20:20:00
A 2009-12-01 20:21:00
A 2009-12-01 20:23:00
B 2009-12-01 20:26:00
B 2009-12-01 20:27:00
B 2009-12-01 20:23:00
C 2009-12-01 20:29:00
（所影响的行数为 10 行）
*/黄_瓜 2010-02-04
- 打赏
- 举报
select * from [tb]
order by charindex(','+[c1]+',',',D,A,B,C,'),[c2]百年树人 2010-02-04
- 打赏
- 举报
---测试数据---
if object_id('[T]') is not null drop table [T]
go
create table [T]([c1] varchar(1),[c2] datetime)
insert [T]
select 'D','2009-11-1 20:20:11' union all
select 'A','2009-12-1 20:20:11' union all
select 'A','2009-12-1 20:21:12' union all
select 'B','2009-12-1 20:23:01' union all
select 'A','2009-12-1 20:23:09' union all
select 'B','2009-12-1 20:25:44' union all
select 'B','2009-12-1 20:26:56' union all
select 'C','2009-12-1 20:29:11' union all
select 'D','2009-12-2 20:33:19' union all
select 'A','2009-12-4 20:33:19'
---查询---
select a.*
from t a,
(select c1,min(c2)c2 from t group by c1) b
where a.c1=b.c1
order by b.c2,a.c1,a.c2
---结果---
c1 c2
---- ------------------------------------------------------
D 2009-11-01 20:20:11.000
D 2009-12-02 20:33:19.000
A 2009-12-01 20:20:11.000
A 2009-12-01 20:21:12.000
A 2009-12-01 20:23:09.000
A 2009-12-04 20:33:19.000
B 2009-12-01 20:23:01.000
B 2009-12-01 20:25:44.000
B 2009-12-01 20:26:56.000
C 2009-12-01 20:29:11.000
（所影响的行数为 10 行）--小F-- 2010-02-04
- 打赏
- 举报
----------------------------------------------------------------
-- Author :fredrickhu(我是小F，向高手学习)
-- Date :2010-02-04 11:19:26
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([c1] varchar(1),[c2] datetime)
insert [tb]
select 'D','2009-11-1 20:20:11' union all
select 'A','2009-12-1 20:20:11' union all
select 'A','2009-12-1 20:21:12' union all
select 'B','2009-12-1 20:23:01' union all
select 'A','2009-12-1 20:23:09' union all
select 'B','2009-12-1 20:25:44' union all
select 'B','2009-12-1 20:26:56' union all
select 'C','2009-12-1 20:29:11' union all
select 'D','2009-12-2 20:33:19' union all
select 'A','2009-12-4 20:33:19'
--------------开始查询--------------------------
--select id=row_number()over(partition by c1 order by c2),* from tb
select
c1,c2
from
(select id=row_number()over(partition by c1 order by c2),* from tb)t
order by
case c1 when 'd' then 1 else 2 end
----------------结果----------------------------
/* c1 c2
---- -----------------------
D 2009-11-01 20:20:11.000
D 2009-12-02 20:33:19.000
A 2009-12-04 20:33:19.000
A 2009-12-01 20:20:11.000
A 2009-12-01 20:21:12.000
B 2009-12-01 20:23:01.000
A 2009-12-01 20:23:09.000
B 2009-12-01 20:25:44.000
B 2009-12-01 20:26:56.000
C 2009-12-01 20:29:11.000
(10 行受影响)
*/
nianran520 2010-02-04
- 打赏
- 举报
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([c1] varchar(1),[c2] datetime)
insert [tb]
select 'D','2009-11-1 20:20:11' union all
select 'A','2009-12-1 20:20:11' union all
select 'A','2009-12-1 20:21:12' union all
select 'B','2009-12-1 20:23:01' union all
select 'A','2009-12-1 20:23:09' union all
select 'B','2009-12-1 20:25:44' union all
select 'B','2009-12-1 20:26:56' union all
select 'C','2009-12-1 20:29:11' union all
select 'D','2009-12-2 20:33:19' union all
select 'A','2009-12-4 20:33:19'
select * from [tb]
order by charindex(','+[c1]+',',',D,A,B,C,'),
[c2]
--------------------------------
D 2009-11-01 20:20:11.000
D 2009-12-02 20:33:19.000
A 2009-12-01 20:20:11.000
A 2009-12-01 20:21:12.000
A 2009-12-01 20:23:09.000
A 2009-12-04 20:33:19.000
B 2009-12-01 20:23:01.000
B 2009-12-01 20:25:44.000
B 2009-12-01 20:26:56.000
C 2009-12-01 20:29:11.000
