数据分组排序问题

yimi_love 2009-09-17 11:19:54
表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,1

1, 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,1

7, 0921,2009-09-12,2
9, 0921,2009-09-14,1


想得到这样的结果:按时间排序,按orderNO分组。
就是orderNO相同的排在一起,相同orderNO的找到时间最早的,
然后用时间排序。
...全文
115 9 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
--小F-- 2009-09-17
  • 打赏
  • 举报
回复
order by 
(select
CreateDate
from
tb
where
CreateDate=(select min(CreateDate) from tb a where orderNO=a.orderNO))
SQL77 2009-09-17
  • 打赏
  • 举报
回复
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 行)
soft_wsx 2009-09-17
  • 打赏
  • 举报
回复
呵可
htl258_Tony 2009-09-17
  • 打赏
  • 举报
回复

/*---------------------------------
-- 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 行受影响)

*/
yimi_love 2009-09-17
  • 打赏
  • 举报
回复
楼上都不是我要的结果,我想要的结果是相同orderNO的排在一起,但是每组中选择时间最早的,按选择出的时间把每组排序
zhengzeng 2009-09-17
  • 打赏
  • 举报
回复
你这个不是分组,还是排序的问题,只是 用两个列排。前几楼正解。
dawugui 2009-09-17
  • 打赏
  • 举报
回复
select * from tb order by orderNO,CreateDate,state desc
Rotaxe 2009-09-17
  • 打赏
  • 举报
回复
select * from tb1 order by orderNO,CreateDate
华夏小卒 2009-09-17
  • 打赏
  • 举报
回复

select * from tb order by orderno desc, CreateDate asc

--orderno 要么升,要么降
除非转换

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧