34,838
社区成员




order by
(select
CreateDate
from
tb
where
CreateDate=(select min(CreateDate) from tb a where orderNO=a.orderNO))
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[orderNO] NVARCHAR(10),[CreateDate] DATETIME,[state] INT)
INSERT [tb]
SELECT 1,'0918',N'2009-09-09',1 UNION ALL
SELECT 2,'0917',N'2009-09-10',2 UNION ALL
SELECT 3,'0918',N'2009-09-15',1 UNION ALL
SELECT 4,'0918',N'2009-09-11',1 UNION ALL
SELECT 5,'0917',N'2009-09-13',1 UNION ALL
SELECT 6,'0919',N'2009-09-13',1 UNION ALL
SELECT 7,'0921',N'2009-09-12',2 UNION ALL
SELECT 8,'0919',N'2009-09-07',1 UNION ALL
SELECT 9,'0921',N'2009-09-14',1
GO
SELECT * FROM TB T ORDER BY
CASE WHEN EXISTS(SELECT 1 FROM TB WHERE ORDERNO=T.ORDERNO)
THEN (SELECT MIN(CreateDate) FROM TB WHERE ORDERNO=T.ORDERNO) ELSE ORDERNO END,CreateDate
(所影响的行数为 9 行)
id orderNO CreateDate state
----------- ---------- ------------------------------------------------------ -----------
8 0919 2009-09-07 00:00:00.000 1
6 0919 2009-09-13 00:00:00.000 1
1 0918 2009-09-09 00:00:00.000 1
4 0918 2009-09-11 00:00:00.000 1
3 0918 2009-09-15 00:00:00.000 1
2 0917 2009-09-10 00:00:00.000 2
5 0917 2009-09-13 00:00:00.000 1
7 0921 2009-09-12 00:00:00.000 2
9 0921 2009-09-14 00:00:00.000 1
(所影响的行数为 9 行)
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-17 11:32:19
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
---------------------------------*/
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[orderNO] NVARCHAR(10),[CreateDate] DATETIME,[state] INT)
INSERT [tb]
SELECT 1,'0918',N'2009-09-09',1 UNION ALL
SELECT 2,'0917',N'2009-09-10',2 UNION ALL
SELECT 3,'0918',N'2009-09-15',1 UNION ALL
SELECT 4,'0918',N'2009-09-11',1 UNION ALL
SELECT 5,'0917',N'2009-09-13',1 UNION ALL
SELECT 6,'0919',N'2009-09-13',1 UNION ALL
SELECT 7,'0921',N'2009-09-12',2 UNION ALL
SELECT 8,'0919',N'2009-09-07',1 UNION ALL
SELECT 9,'0921',N'2009-09-14',1
GO
--SELECT * FROM [tb]
-->SQL查询如下:
SELECT *
FROM tb t
ORDER BY
(SELECT MIN(CreateDate) FROM tb WHERE orderNO=T.orderNO),
CreateDate
/*
id orderNO CreateDate state
----------- ---------- ----------------------- -----------
8 0919 2009-09-07 00:00:00.000 1
6 0919 2009-09-13 00:00:00.000 1
1 0918 2009-09-09 00:00:00.000 1
4 0918 2009-09-11 00:00:00.000 1
3 0918 2009-09-15 00:00:00.000 1
2 0917 2009-09-10 00:00:00.000 2
5 0917 2009-09-13 00:00:00.000 1
7 0921 2009-09-12 00:00:00.000 2
9 0921 2009-09-14 00:00:00.000 1
(9 行受影响)
*/
select * from tb order by orderno desc, CreateDate asc
--orderno 要么升,要么降
除非转换