表tb1,有字段
id(主键),orderNO,CreateDate,state
1, 0918,2009-09-09,1
2, 0917,2009-09-10,2
3, 0918,2009-09-15,1
4, 0918,2009-09-11,1
5, 0917,2009-09-13,1
6, 0919,2009-09-13,1
7, 0921,2009-09-12,2
8, 0919,2009-09-07,1
9, 0921,2009-09-14,1结果:8, 0919,2009-09-07,1
6, 0919,2009-09-13,11, 0918,2009-09-09,1
4, 0918,2009-09-11,1
3, 0918,2009-09-15,1
2, 0917,2009-09-10,2
5, 0917,2009-09-13,17, 0921,2009-09-12,2
9, 0921,2009-09-14,1
想得到这样的结果:按时间排序,按orderNO分组。
就是orderNO相同的排在一起,相同orderNO的找到时间最早的,
然后用时间排序。
id(主键),orderNO,CreateDate,state
1, 0918,2009-09-09,1
2, 0917,2009-09-10,2
3, 0918,2009-09-15,1
4, 0918,2009-09-11,1
5, 0917,2009-09-13,1
6, 0919,2009-09-13,1
7, 0921,2009-09-12,2
8, 0919,2009-09-07,1
9, 0921,2009-09-14,1结果:8, 0919,2009-09-07,1
6, 0919,2009-09-13,11, 0918,2009-09-09,1
4, 0918,2009-09-11,1
3, 0918,2009-09-15,1
2, 0917,2009-09-10,2
5, 0917,2009-09-13,17, 0921,2009-09-12,2
9, 0921,2009-09-14,1
想得到这样的结果:按时间排序,按orderNO分组。
就是orderNO相同的排在一起,相同orderNO的找到时间最早的,
然后用时间排序。
select * from tb order by orderno desc, CreateDate asc--orderno 要么升,要么降
除非转换
/*---------------------------------
-- 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)---------------------------------*/
--> 生成测试数据表:tbIF 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 行受影响)*/
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
GOSELECT * 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 行)
(select
CreateDate
from
tb
where
CreateDate=(select min(CreateDate) from tb a where orderNO=a.orderNO))