解决就给100分（排序问题，有点难度）

bzhi268 2010-02-04 11:17:46

如题：表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

...全文

127 19 打赏收藏转发到动态举报

写回复

用AI写文章

19 条回复

切换为时间正序

请发表友善的回复…

发表回复

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

东那个升 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

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]
学习

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]
頂~~

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