首页 新闻 搜索 专区 学院

关于sql查询中name根据时间,查询最大时间(max)和第二大时间的次数(count)

0
悬赏园豆:5 [已解决问题] 解决于 2013-03-19 10:23

上面的Table有三个字段,id,Name,Time,

我想查询这张表里面的最大时间,

第二大时间的个数(没有查询)

如下图:查询效果

Count()不知道怎么查询

有经验的园友来帮帮忙了,谢谢!

夏忆(夏天的回忆)的主页 夏忆(夏天的回忆) | 初学一级 | 园豆:6
提问于:2013-03-18 15:00
< >
分享
最佳答案
0

同一行取出?

收获园豆:5
飞来飞去 | 老鸟四级 |园豆:2057 | 2013-03-18 15:13

select  cid,name,max(time)

from Table

group by cid,name

这样可以查询出最大时间

夏忆(夏天的回忆) | 园豆:6 (初学一级) | 2013-03-18 15:16

@夏忆(夏天的回忆): 我的意思是第二大时间的个数需要在同一行?

飞来飞去 | 园豆:2057 (老鸟四级) | 2013-03-18 15:24
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 

 

@飞来飞去: 

飞来飞去 | 园豆:2057 (老鸟四级) | 2013-03-18 15:40

@飞来飞去: 不需要,就只取第二大的时间天数部分相同次数

2012-08-21 11:45:18.000

2012-08-21 11:45:28.000

2012-08-21 11:45:38.000

夏忆(夏天的回忆) | 园豆:6 (初学一级) | 2013-03-19 09:18

@夏忆(夏天的回忆): 那你的规则有问题啊,难道你取第一大也只是取到天数?要是第二大的时间天数部分和第一大一样怎么弄?

飞来飞去 | 园豆:2057 (老鸟四级) | 2013-03-19 09:29

@飞来飞去: 你上面写的已经可以了!

夏忆(夏天的回忆) | 园豆:6 (初学一级) | 2013-03-19 09:55
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个!

夏忆(夏天的回忆) | 园豆:6 (初学一级) | 2013-03-19 10:07

@夏忆(夏天的回忆): 当然了...我写的时候不知道你的规则是天...

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 
飞来飞去 | 园豆:2057 (老鸟四级) | 2013-03-19 10:19

@飞来飞去: 恩,谢谢了!

夏忆(夏天的回忆) | 园豆:6 (初学一级) | 2013-03-19 10:23
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册