关于sql查询中name根据时间,查询最大时间(max)和第二大时间的次数(count)
0
最佳答案
0
同一行取出?
收获园豆：5
select cid,name,max(time)
from Table
group by cid,name
这样可以查询出最大时间
@夏忆（夏天的回忆）: 我的意思是第二大时间的个数需要在同一行?
create table _20130318_tb1(cid varchar(50),name varchar(20),time datetime) insert into _20130318_tb1 values('t-1','test1','2013-3-19') insert into _20130318_tb1 values('t-1','test1','2013-3-18') insert into _20130318_tb1 values('t-1','test1','2013-3-18') insert into _20130318_tb1 values('t-1','test1','2013-3-18') insert into _20130318_tb1 values('t-1','test1','2013-3-17') insert into _20130318_tb1 values('t-1','test1','2013-3-17') select a.cid,a.name,a.bt,(select top 1 count(1) from _20130318_tb1 b where b.cid=a.cid and b.name=a.name and b.time<a.bt group by time order by time desc) as ct from (select cid,name,max(time) as bt from _20130318_tb1 group by cid,name) a
@飞来飞去:
@飞来飞去: 不需要,就只取第二大的时间天数部分相同次数
2012-08-21 11:45:18.000
2012-08-21 11:45:28.000
2012-08-21 11:45:38.000
@夏忆（夏天的回忆）: 那你的规则有问题啊,难道你取第一大也只是取到天数?要是第二大的时间天数部分和第一大一样怎么弄?
@飞来飞去: 你上面写的已经可以了!
create table Table2(cid varchar(50),name varchar(20),time datetime) insert into Table2 values('t-1','test1','2013-3-20 12:23:34') insert into Table2 values('t-1','test1','2013-3-20 12:24:34' ) insert into Table2 values('t-1','test1','2013-3-18 12:27:34') insert into Table2 values('t-1','test1','2013-3-18 12:32:34') insert into Table2 values('t-1','test1','2013-3-18 12:45:34') insert into Table2 values('t-1','test1','2013-3-17 11:23:34') insert into Table2 values('t-2','test2','2013-4-1 12:45:34') insert into Table2 values('t-2','test2','2013-3-22 12:45:34') insert into Table2 values('t-2','test2','2013-3-31 12:45:34') insert into Table2 values('t-2','test2','2013-3-18 12:45:34') insert into Table2 values('t-2','test2','2013-3-17 12:45:34') insert into Table2 values('t-2','test2','2013-3-17 12:45:34') insert into Table2 values('t-3','test3','2013-5-19 12:45:34') insert into Table2 values('t-3','test3','2013-5-18 12:45:34') insert into Table2 values('t-3','test3','2013-5-18 12:45:34') insert into Table2 values('t-3','test3','2013-5-18 12:45:34') insert into Table2 values('t-3','test3','2013-5-17 12:45:34') insert into Table2 values('t-3','test3','2013-5-17 12:45:34') select * from Table2 select a.cid,a.name,a.bt,(select top 1 count(1) from Table2 b where b.cid=a.cid and b.name=a.name and b.time<a.bt group by time order by time desc) as ct from (select cid,name,max(time) as bt from Table2 group by cid,name) a
上面的次数不是很准确!
比如当test1的最大时间为2013-3-20时,那么他的第二大时间就为
2013-3-18
时间为2013-3-18有3个
查询出来却只有1个!
@夏忆（夏天的回忆）: 当然了...我写的时候不知道你的规则是天...
create table _20130319_Table2(cid varchar(50),name varchar(20),time datetime) insert into _20130319_Table2 values('t-1','test1','2013-3-20 12:23:34') insert into _20130319_Table2 values('t-1','test1','2013-3-20 12:24:34' ) insert into _20130319_Table2 values('t-1','test1','2013-3-18 12:27:34') insert into _20130319_Table2 values('t-1','test1','2013-3-18 12:32:34') insert into _20130319_Table2 values('t-1','test1','2013-3-18 12:45:34') insert into _20130319_Table2 values('t-1','test1','2013-3-17 11:23:34') insert into _20130319_Table2 values('t-2','test2','2013-4-1 12:45:34') insert into _20130319_Table2 values('t-2','test2','2013-3-22 12:45:34') insert into _20130319_Table2 values('t-2','test2','2013-3-31 12:45:34') insert into _20130319_Table2 values('t-2','test2','2013-3-18 12:45:34') insert into _20130319_Table2 values('t-2','test2','2013-3-17 12:45:34') insert into _20130319_Table2 values('t-2','test2','2013-3-17 12:45:34') insert into _20130319_Table2 values('t-3','test3','2013-5-19 12:45:34') insert into _20130319_Table2 values('t-3','test3','2013-5-18 12:45:34') insert into _20130319_Table2 values('t-3','test3','2013-5-18 12:45:34') insert into _20130319_Table2 values('t-3','test3','2013-5-18 12:45:34') insert into _20130319_Table2 values('t-3','test3','2013-5-17 12:45:34') insert into _20130319_Table2 values('t-3','test3','2013-5-17 12:45:34') select *,convert(varchar,time,111) from _20130319_Table2 select a.cid,a.name,a.bt,(select top 1 count(1) from _20130319_Table2 b where b.cid=a.cid and b.name=a.name and b.time<a.bt group by convert(varchar,time,111) order by convert(varchar,time,111) desc) as ct from (select cid,name,max(convert(varchar,time,111)) as bt from _20130319_Table2 group by cid,name) a
@飞来飞去: 恩,谢谢了!
