表名GZJLB数据
gzkssj gzxfsj zrdw
2010-4-6 2010-4-7 128
2010-3-31 2010-4-1 126
2010-3-23 2010-3-29 128
2010-3-17 2010-4-19 128
2010-4-5 2010-4-7 129
2010-3-25 2010-4-20 127
2010-3-22 2010-4-14 129
2009-3-11 2009-3-17 127
2009-4-2 2009-4-6 129
2010-4-14 2010-4-15 128
2010-4-6 2010-4-7 129
我想查某个单位某个月内总相差时间最多的前3位应该怎么查,相差时间是gzxfsj-gzkssj
比如上面总相差时间最多的前3个是128,129,127,那要写条Sql语句应该怎么写?
gzkssj gzxfsj zrdw
2010-4-6 2010-4-7 128
2010-3-31 2010-4-1 126
2010-3-23 2010-3-29 128
2010-3-17 2010-4-19 128
2010-4-5 2010-4-7 129
2010-3-25 2010-4-20 127
2010-3-22 2010-4-14 129
2009-3-11 2009-3-17 127
2009-4-2 2009-4-6 129
2010-4-14 2010-4-15 128
2010-4-6 2010-4-7 129
我想查某个单位某个月内总相差时间最多的前3位应该怎么查,相差时间是gzxfsj-gzkssj
比如上面总相差时间最多的前3个是128,129,127,那要写条Sql语句应该怎么写?
Select *
from tb t
where abs(datediff(mi,[gzkssj],[gzxfsj]))in
(select top 3 abs(datediff(mi,[gzkssj],[gzxfsj])) from tb where [zrdw]=t.[zrdw])
go
insert into tb select
'2010-4-6','2010-4-7',128 union all select
'2010-3-31','2010-4-1',126 union all select
'2010-3-23','2010-3-29',128 union all select
'2010-3-17','2010-4-19',128 union all select
'2010-4-5','2010-4-7',129 union all select
'2010-3-25','2010-4-20',127 union all select
'2010-3-22','2010-4-14',129 union all select
'2009-3-11','2009-3-17',127 union all select
'2009-4-2','2009-4-6',129 union all select
'2010-4-14','2010-4-15',128 union all select
'2010-4-6','2010-4-7',129
select top 3 * from
tb order by gzxfsj-gzkssj descgzkssj gzxfsj zrdw
----------------------- ----------------------- -----------
2010-03-17 00:00:00 2010-04-19 00:00:00 128
2010-03-25 00:00:00 2010-04-20 00:00:00 127
2010-03-22 00:00:00 2010-04-14 00:00:00 129(3 行受影响)
*
from
GZJLB t
where
zrdw in(select top 3 zrdw from tb where gzkssj=t.gzkssj and gzxfsj=t.gzxfsj)
select top 3 *
from tb
order by abs(datediff(mi,[gzkssj],[gzxfsj])) desc
(
select rn=row_number()over(partition by zrdw order by datediff(dd,gzxfsj,gzkssj)), * from GZJLB
)
select * from t where rn<=3
select * from gzjlb a inner join (
select top 3 zrdw from (
select zrdw from gzjlb group by zrdw
) a
order by zrdw DESC ) b on a.zrdw=b.zrdw这个你试试
go
insert into tb select
'2010-4-6','2010-4-7',128 union all select
'2010-3-31','2010-4-1',126 union all select
'2010-3-23','2010-3-29',128 union all select
'2010-3-17','2010-4-19',128 union all select
'2010-4-5','2010-4-7',129 union all select
'2010-3-25','2010-4-20',127 union all select
'2010-3-22','2010-4-14',129 union all select
'2009-3-11','2009-3-17',127 union all select
'2009-4-2','2009-4-6',129 union all select
'2010-4-14','2010-4-15',128 union all select
'2010-4-6','2010-4-7',129
select top 3 *,datediff(day,gzkssj,gzxfsj) as [相差天数] from
tb order by [相差天数] descgzkssj gzxfsj zrdw 相差天数
----------------------- ----------------------- ----------- -----------
2010-03-17 00:00:00 2010-04-19 00:00:00 128 33
2010-03-25 00:00:00 2010-04-20 00:00:00 127 26
2010-03-22 00:00:00 2010-04-14 00:00:00 129 23(3 行受影响)
select top 3 *
from tb
order by abs(datediff(day,[gzkssj],[gzxfsj])) desc
select * from gzjlb a inner join (
select top 3 zrdw from (
select zrdw from gzjlb group by zrdw
) a
order by zrdw DESC ) b on a.zrdw=b.zrdw这个你试试
from gzjlb a inner join (
---取分组只前3个数据
select top 3 zrdw
from (
---对天数进行分组
select zrdw
from gzjlb
group by zrdw
---END
) a order by zrdw DESC
---END
) b on a.zrdw=b.zrdw ---等于前3个数据的所有记录
go
insert into gzjlb select
'2010-4-6','2010-4-7',128 union all select
'2010-3-31','2010-4-1',126 union all select
'2010-3-23','2010-3-29',128 union all select
'2010-3-17','2010-4-19',128 union all select
'2010-4-5','2010-4-7',129 union all select
'2010-3-25','2010-4-20',127 union all select
'2010-3-22','2010-4-14',129 union all select
'2009-3-11','2009-3-17',127 union all select
'2009-4-2','2009-4-6',129 union all select
'2010-4-14','2010-4-15',128 union all select
'2010-4-6','2010-4-7',129select *
from gzjlb a inner join (
---取分组只前3个数据
select top 3 zrdw
from (
---对天数进行分组
select zrdw
from gzjlb
group by zrdw
---END
) a order by zrdw DESC
---END
) b on a.zrdw=b.zrdw ---等于前3个数据的所有记录drop table gzjlb
Top1的时候只显示129的数据,Top2的时候显示128的数据,Top3的时候显示127的数据,麻烦你了,感激不尽,弄好了单独发帖等你接分,谢谢了