SQL分组排序

szmeng 2008-02-12 12:12:26
例如
字段 编号 日期 日期时间
001 2008-02-02 2008-02-02 20:00:00
001 2008-02-02 2008-02-02 21:00:00
001 2008-02-02 2008-02-02 23:00:00

sql排序结果：
编号 日期 时间1 时间2 时间3
001 2008-02-02 20：00 21:00 23:00
...全文
1938 22 打赏 收藏 转发到动态 举报
写回复
用AI写文章
22 条回复
切换为时间正序
请发表友善的回复…
发表回复
kesoo 2009-08-10
  • 打赏
  • 举报
 回复
学习了
dawugui 2008-02-17
  • 打赏
  • 举报
 回复 
create table tb (编号 varchar(10),日期 varchar(10),日期时间 datetime)

insert into tb values('001', '2008-02-02', '2008-02-02 20:00:00') 

insert into tb values('001', '2008-02-02', '2008-02-02 21:00:00') 

insert into tb values('001', '2008-02-02', '2008-02-02 23:00:00')

go



--静态SQL，指一个编号最多有3个。

select 编号 , 

  max(case px when 1 then right(convert(varchar(16),日期时间,120),5) else '' end) 时间1,

  max(case px when 2 then right(convert(varchar(16),日期时间,120),5) else '' end) 时间2,

  max(case px when 3 then right(convert(varchar(16),日期时间,120),5) else '' end) 时间3

from

(

  select * , px = (select count(1) from tb where 编号=t.编号 and 日期=t.日期 and 日期时间<t.日期时间) + 1 from tb t

) m

group by 编号

/*

编号       时间1      时间2       时间3        

---------- ---------- ---------- ---------- 

001        20:00      21:00      23:00

*/



--动态SQL，指一个编号时间个数不定。

declare @sql varchar(8000)

set @sql = 'select 编号'

select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then right(convert(varchar(16),日期时间,120),5) else '' '' end) [时间' + cast(px as varchar) + ']'

from (select distinct px from (select * , px = (select count(1) from tb where 编号=t.编号 and 日期=t.日期 and 日期时间<t.日期时间) + 1 from tb t) m) as a

set @sql = @sql + ' from (select * , px = (select count(1) from tb where 编号=t.编号 and 日期=t.日期 and 日期时间<t.日期时间) + 1 from tb t) m group by 编号'

exec(@sql) 

/*

编号       时间1      时间2       时间3        

---------- ---------- ---------- ---------- 

001        20:00      21:00      23:00

*/



drop table tb
dawugui 2008-02-17
  • 打赏
  • 举报
 回复
接分.
-狙击手- 2008-02-17
  • 打赏
  • 举报
 回复 
create   table   tb(编号   varchar(5),日期   varchar(10),日期时间   datetime) 

insert   into   tb   

select   '001','2008-02-02','2008-02-02 10:00:00'   union   all 

select   '001','2008-02-02','2008-02-02 20:00:00'   union   all 

select   '001','2008-02-02','2008-02-02 21:00:00'   union   all 

select   '001','2008-02-02','2008-02-02 23:00:00'   union   all

select   '001','2008-02-03','2008-02-03 10:00:00'   union   all 

select   '001','2008-02-03','2008-02-03 20:00:00'   union   all 

select   '001','2008-02-03','2008-02-03 21:00:00'   union   all 

select   '001','2008-02-02','2008-02-02 23:00:00'   union   all

select   '002','2008-02-02','2008-02-02 08:00:00'   union   all     

select   '002','2008-02-02','2008-02-02 12:00:00'   union   all     

select   '002','2008-02-02','2008-02-02 13:00:00'   



go

declare @s nvarchar(4000),@i int

--用distinct去掉有重复的记录

set @s = ''

select @s = @s + ',max(case when con = '+ ltrim(con) +' then convert(varchar(10),日期时间,108) else null end) as 时间'+ltrim(con+1)

from (select  distinct con=(select count(distinct 日期时间) 

from tb where 编号=a.编号 and 日期=a.日期 and 日期时间 < a.日期时间) from tb a ) a







set @s='select 编号,日期'+@s+

' from (select *,con=(select count(distinct 日期时间) --distinct去掉有重复的记录

from tb where 编号=a.编号 and 日期=a.日期 and 日期时间 < a.日期时间) from tb a ) tmp group by 编号,日期'



--print @s --显示语句

exec (@s)

/*



编号    日期         时间1        时间2        时间3        时间4        

----- ---------- ---------- ---------- ---------- ---------- 

001   2008-02-02 10:00:00   20:00:00   21:00:00   23:00:00

002   2008-02-02 08:00:00   12:00:00   13:00:00   NULL

001   2008-02-03 10:00:00   20:00:00   21:00:00   NULL



警告: 聚合或其它 SET 操作消除了空值。

*/



drop table tb
中国风 2008-02-17
  • 打赏
  • 举报
 回复
把分数分配给大家就行了。。。
120已经在可用分里扣除了与现有分无关
szmeng 2008-02-17
  • 打赏
  • 举报
 回复
没有120分。结不了。点样减他哦
liuyann 2008-02-17
  • 打赏
  • 举报
 回复

怎样变回20分

no way!
szmeng 2008-02-17
  • 打赏
  • 举报
 回复
结帖错误。现在变成120分了。怎样变回20分
中国风 2008-02-16
  • 打赏
  • 举报
 回复
szmeng

等 级：
发表于：2008-02-14 21:04:2212楼 得分:0
bqb 在线时看下多几天的数据后面都是空的
zefuzhang2008 加上这个也不行.
-------------------
由于数据有记录用9时产生，是以最多的记录数作为列数
中国风 2008-02-16
  • 打赏
  • 举报
 回复 
create   table   tb(编号   varchar(5),日期   varchar(10),日期时间   datetime) 

insert   into   tb   

select   '001','2008-02-02','2008-02-02       10:00:00'   union   all 

select   '001','2008-02-02','2008-02-02       20:00:00'   union   all 

select   '001','2008-02-02','2008-02-02       21:00:00'   union   all 

select   '001','2008-02-02','2008-02-02       23:00:00'   union   all ---



select   '001','2008-02-03','2008-02-03       10:00:00'   union   all 

select   '001','2008-02-03','2008-02-03       20:00:00'   union   all 

select   '001','2008-02-03','2008-02-03       21:00:00'   union   all 

select   '001','2008-02-02','2008-02-02       23:00:00'   union   all --这条记录重复

     

select   '002','2008-02-02','2008-02-02       08:00:00'   union   all     

select   '002','2008-02-02','2008-02-02       12:00:00'   union   all     

select   '002','2008-02-02','2008-02-02       13:00:00'   



go

declare @s nvarchar(4000),@i int

--用distinct去掉有重复的记录

select top 1 @s='',@i=count(distinct 日期时间) from tb group by 编号,日期 order by count(distinct 日期时间) desc--最同一天同一编号最多的记录数

while @i>0

	select @s=',[时间'+rtrim(@i)+']=max(case when con='+rtrim(@i)+' then convert(varchar(5),日期时间,14) else '''' end)'+@s,

			@i=@i-1

set @s='select 编号,日期'+@s+

' from (select *,con=(select count(distinct 日期时间) --distinct去掉有重复的记录

from tb where 编号=a.编号 and 日期=a.日期 and 日期时间!>a.日期时间) from tb a )tmp group by 编号,日期'



--print @s --显示语句

exec (@s)

go

/*

号    日期         时间1   时间2   时间3   时间4   

----- ---------- ----- ----- ----- ----- 

001   2008-02-02 10:00 20:00 21:00 23:00

002   2008-02-02 08:00 12:00 13:00 

001   2008-02-03 10:00 20:00 21:00 





*/
ojuju10 2008-02-14
  • 打赏
  • 举报
 回复 


Create table t(Id varchar(10),Createtime varchar(10),date datetime )

insert into t select '001','2008-02-02','2008-02-02   20:00:00'

insert into t select '001','2008-02-02','2008-02-02   21:00:00'

insert into t select '001','2008-02-02','2008-02-02   23:00:00'



declare @sql varchar(8000)

set @sql='select Id,createtime'

select @sql=@sql+',max(case when date=cast('''+ltrim(date)+''' as varchar(30)) then convert(varchar(10),date,108) else null end ) as 时间'+ltrim(num)

from (select distinct date,(select count(1) from t b where a.id=b.id and a.createtime=b.createtime and b.date<=a.date ) as num from  t a) a

set @sql=@sql+' from  t group by id,createtime'

exec(@sql)



/*

Id         createtime 时间1        时间2        时间3

---------- ---------- ---------- ---------- ----------

001        2008-02-02 20:00:00   21:00:00   23:00:00

*/
zefuzhang2008 2008-02-14
  • 打赏
  • 举报
 回复
group by 编号,日期,日期时间
加上这个，楼上的
szmeng 2008-02-14
  • 打赏
  • 举报
 回复
还是有问题.现在只是一天.如里你3号进来还是会的


if object_id('tb') is not null
drop table tb
go

create table tb(编号 varchar(5),日期 varchar(10),日期时间 datetime)
insert into tb
select '001','2008-02-02','2008-02-02 10:00:00' union all
select '001','2008-02-02','2008-02-02 20:00:00' union all
select '001','2008-02-02','2008-02-02 21:00:00' union all
select '001','2008-02-02','2008-02-02 23:00:00' union all
select '001','2008-02-03','2008-02-03 10:00:00' union all
select '001','2008-02-03','2008-02-03 20:00:00' union all
select '001','2008-02-03','2008-02-03 21:00:00' union all
select '001','2008-02-02','2008-02-02 23:00:00' union all
select '002','2008-02-02','2008-02-02 08:00:00' union all
select '002','2008-02-02','2008-02-02 12:00:00' union all
select '002','2008-02-02','2008-02-02 13:00:00'
go
bqb 2008-02-14
  • 打赏
  • 举报
 回复
改一下！这样就可以了！
if object_id('tb') is not null

drop table tb

go

 

create table tb(编号 varchar(5),日期 varchar(10),日期时间 datetime)

insert into tb 

select '001','2008-02-02','2008-02-02   10:00:00' union all

select '001','2008-02-02','2008-02-02   20:00:00' union all

select '001','2008-02-02','2008-02-02   21:00:00' union all

select '001','2008-02-02','2008-02-02   23:00:00' union all  

select '002','2008-02-02','2008-02-02   08:00:00' union all  

select '002','2008-02-02','2008-02-02   12:00:00' union all  

select '002','2008-02-02','2008-02-02   13:00:00'  



go



declare @i int,@max int,@s nvarchar(2000)

select @i=1,@s='',@max=(select max(num) from (select count(1) as num from tb group by 编号) dd)

while @i<=@max

    select @s=@s+',[时间'+rtrim(@i)+']=max(case when con='+rtrim(@i)+' then convert(char(5),日期时间,108) else '''' end)',@i=@i+1

set @s='select 编号,日期'+@s+' from (select *,con=(select count(1) from tb where 编号=t.编号 and 日期=t.日期 and 日期时间<=t.日期时间) from tb t)tmp group by 编号,日期'

exec(@s)



/*



编号     日期           时间1    时间2    时间3   时间4

--------------------------------------------------------  

001	2008-02-02	10:00	20:00	21:00	23:00

002	2008-02-02	08:00	12:00	13:00	     







*/
zefuzhang2008 2008-02-14
  • 打赏
  • 举报
 回复
/*****************************************
行列转换的问题
*********************************************/
--动态SQL方案
declare @CJ table([Name] varchar(10),[Subject] varchar(20),Result int)
insert into @CJ([Name],[Subject],Result)
select '张三','语文',80 union all
select '张三','数学',90 union all
select '张三','物理',85 union all
select '李四','语文',85 union all
select '李四','数学',92 union all
select '李四','物理',82
declare @sql varchar(4000)
set @sql = 'select Name'
select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']' from (select distinct Subject from @CJ) as a
select @sql = @sql+' from @cj group by name'
select @sql
exec(@sql)

--2005透视语法方案
create table tab
(
学号 int,
课程 varchar(20),
成绩 int
)
insert into tab select 1,'语文',60
union all select 1,'数学',70
union all select 1,'化学',90
union all select 1,'生物',80
union all select 2,'语文',60
union all select 2,'数学',65
union all select 2,'化学',86
union all select 2,'生物',97

select * from tab
pivot(max(成绩)for 课程 in([语文],[数学],[化学],[生物])) as tab

drop table tab

--详解
create table tb
(
Name varchar(10) ,
Subject varchar(10) ,
Result int
)

insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
go

--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理
from tb
group by name
/*
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/

--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql)
/*
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74
*/

-------------------------------------------------------------------
/*加个平均分，总分
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/

--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理,
cast(avg(result*1.0) as decimal(18,2)) 平均分,
sum(result) 总分
from tb
group by name
/*
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/

--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar(8000)
set @sql1 = 'select Name as ' + '姓名'
select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
exec(@sql1)
/*
姓名 数学 物理 语文 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 84 94 74 84.00 252
张三 83 93 74 83.33 250
*/

drop table tb

---------------------------------------------------------
---------------------------------------------------------
/*
如果上述两表互相换一下：即

姓名 语文 数学 物理
张三 74　　83　　93
李四 74　　84　　94

想变成
Name Subject Result
---------- ------- -----------
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
*/

create table tb1
(
姓名 varchar(10) ,
语文 int ,
数学 int ,
物理 int
)

insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)

select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end

--------------------------------------------------------------------
/*加个平均分,总分
Name Subject Result
---------- ------- --------------------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
*/

select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
union all
select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1
union all
select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end

drop table tb1
szmeng 2008-02-14
  • 打赏
  • 举报
 回复
bqb 在线时看下多几天的数据后面都是空的
zefuzhang2008 加上这个也不行.
szmeng 2008-02-14
  • 打赏
  • 举报
 回复
ojuju10 的不行哦?你重复插入数据看看.执行几次下面的语句看下
insert into t select '001','2008-02-02','2008-02-02 20:00:00'
insert into t select '001','2008-02-02','2008-02-02 21:00:00'
insert into t select '001','2008-02-02','2008-02-02 23:00:00'
szmeng 2008-02-14
  • 打赏
  • 举报
 回复
1楼朋友的好像固定了时间次数
szmeng 2008-02-14
  • 打赏
  • 举报
 回复
如果加多一个编号就会像这张图一样了时间有空格
bqb 的
bqb 2008-02-12
  • 打赏
  • 举报
 回复 
if object_id('tb') is not null

drop table tb

 

create table tb(编号 varchar(5),日期 varchar(10),日期时间 datetime)

insert into tb 

select '001','2008-02-02','2008-02-02   20:00:00' union all

select '001','2008-02-02','2008-02-02   21:00:00' union all

select '001','2008-02-02','2008-02-02   23:00:00'  

 





declare @exec varchar(8000),@i int

select @exec='',@i=1

select @exec=@exec+',[时间'+ltrim(cast(@i as char(2)))+']=max(case when 日期时间= '''+convert(varchar(20),日期时间)+'''  then convert(char(5),日期时间,108) else '''' end)',@i=@i+1  from  tb  group by  日期时间

set @exec='select 编号, 日期'+@exec+' from tb group by 编号,日期 order by  编号 '

exec (@exec)





/*

编号	日期		时间1	时间2	时间3

------------------------------------------------  

001	2008-02-02	20:00	21:00	23:00

*/
加载更多回复（2）
SQL分组排序函数（组内分别排序）
SQL分组排序函数的粗浅总结
SQL 分组排序后取值
SQL 分组排序后取值 场景前要： 从前有三个班级，每个班级的学生成绩各异，有一天睿智的校长希望知道这一年中，每个班级近一年里所有的学生成绩信息，和查询每个班级近一年里所有第一名的学生成绩信息，故事由此...
sql分组排序mysql_SQL分组排序
面试的时候，只要这个工作要求SQL能力，面试官必然会问SQL怎么实现分组排序？刚开始，我一直以为是没有分组排序这个函数的。直到第一次去面试腾讯时，笔试题目中有一题是分组排序，我写得是变量赋值排序。面试官一脸...
sql分组排序，查询每组第一个数据
sql分组排序，查询每组第一个数据
sql分组排序取top n
数据库查询*分组排序 要求：按照课程分组，查找每个课程最高的两个成绩。 数据文件如下： 第一列no为学号，第二列course为课程，第三列score为分数 mysql> select * from lesson; +-------+---------+-------+ | no ...
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章