select * from ta where datetime = (select top 1 datetime from ta group by datetime order by count(1) desc)
select * from T where convert(varchar(10),INIME,120)=(select top 1 convert(varchar(10),INIME,120) INIME from T order by ount(ID) desc)
declare @tb table (id int,intime datetime) insert into @tb select 1,'2005-1-2' insert into @tb select 2,'2005-1-2' insert into @tb select 3,'2005-1-2' insert into @tb select 4,'2005-1-3' insert into @tb select 5,'2005-1-3' insert into @tb select 6,'2005-1-4' insert into @tb select 7,'2005-1-4' insert into @tb select 8,'2005-1-4' insert into @tb select 9,'2005-1-4'select * from @tb t where convert(varchar(10),intime,120)=( select top 1 convert(varchar(10),intime,120) from @tb group by convert(varchar(10),intime,120) order by count(1) desc )
select * from T where convert(varchar(10),INIME,120)=(select top 1 convert(varchar(10),INIME,120) INIME from T group by convert(varchar(10),INIME,120) order by ount(ID) desc)
/* -- Author:Flystone -- Version:V1.001 Date:2008-05-15 初稿 -- Version:V1.002 Date:2008-05-16 1、 处理空格带来的异常 -- 2、 增加了形如yyyy-mm-dd hh:mm:ss -- yyyy-m-d h:m:s 格式的处理 */-- Test Data: ta If object_id('ta') is not null Drop table ta Go Create table ta(ID int,INIME datetime) Go Insert into ta select 1,'2005-1-2 17:22:01' union all select 2,'2005-1-2 17:22:02' union all select 3,'2005-1-2 17:22:03' union all select 4,'2005-1-3 17:22:01' union all select 5,'2005-1-3 17:22:03' union all select 7,'2005-1-3 17:22:04' union all select 44,'2005-1-5 17:22:04' union all select 50,'2005-1-6 17:22:04' union all select 51,'2005-1-6 17:23:04' union all select 52,'2005-1-6 18:23:04' union all select 53,'2005-1-6 19:23:04' Go --Start select * from ta where convert(char(10),INIME,120) = (select top 1 convert(char(10),INIME,120) from ta group by convert(char(10),INIME,120) order by count(1) desc)--Result: /*ID INIME ----------- ------------------------------------------------------ 50 2005-01-06 17:22:04.000 51 2005-01-06 17:23:04.000 52 2005-01-06 18:23:04.000 53 2005-01-06 19:23:04.000(所影响的行数为 4 行) */ --End
谢谢! select * from moviedata where DATEDIFF(d,'2000-1-1',INIME )=(select top 1 DATEDIFF(d,'2000-1-1',INIME ) from moviedata group by DATEDIFF(d,'2000-1-1',INIME ) order by count(*) desc)
select * from @tb t where convert(varchar(10),intime,120)=( select top 1 convert(varchar(10),intime,120) from @tb group by convert(varchar(10),intime,120) order by count(1) desc )这个好,和我想的一样!
from ta
where datetime = (select top 1 datetime from ta group by datetime order by count(1) desc)
*
from
T
where
convert(varchar(10),INIME,120)=(select top 1 convert(varchar(10),INIME,120) INIME from T order by ount(ID) desc)
insert into @tb select 1,'2005-1-2'
insert into @tb select 2,'2005-1-2'
insert into @tb select 3,'2005-1-2'
insert into @tb select 4,'2005-1-3'
insert into @tb select 5,'2005-1-3'
insert into @tb select 6,'2005-1-4'
insert into @tb select 7,'2005-1-4'
insert into @tb select 8,'2005-1-4'
insert into @tb select 9,'2005-1-4'select * from @tb t
where convert(varchar(10),intime,120)=(
select top 1 convert(varchar(10),intime,120) from @tb
group by convert(varchar(10),intime,120)
order by count(1) desc
)
6 2005-01-04 00:00:00.000
7 2005-01-04 00:00:00.000
8 2005-01-04 00:00:00.000
9 2005-01-04 00:00:00.000
select
*
from
T
where
convert(varchar(10),INIME,120)=(select top 1 convert(varchar(10),INIME,120) INIME from T group by convert(varchar(10),INIME,120) order by ount(ID) desc)
-- Author:Flystone
-- Version:V1.001 Date:2008-05-15 初稿
-- Version:V1.002 Date:2008-05-16 1、 处理空格带来的异常
-- 2、 增加了形如yyyy-mm-dd hh:mm:ss
-- yyyy-m-d h:m:s 格式的处理
*/-- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(ID int,INIME datetime)
Go
Insert into ta
select 1,'2005-1-2 17:22:01' union all
select 2,'2005-1-2 17:22:02' union all
select 3,'2005-1-2 17:22:03' union all
select 4,'2005-1-3 17:22:01' union all
select 5,'2005-1-3 17:22:03' union all
select 7,'2005-1-3 17:22:04' union all
select 44,'2005-1-5 17:22:04' union all
select 50,'2005-1-6 17:22:04' union all
select 51,'2005-1-6 17:23:04' union all
select 52,'2005-1-6 18:23:04' union all
select 53,'2005-1-6 19:23:04'
Go
--Start
select *
from ta
where convert(char(10),INIME,120) = (select top 1 convert(char(10),INIME,120) from ta group by convert(char(10),INIME,120) order by count(1) desc)--Result:
/*ID INIME
----------- ------------------------------------------------------
50 2005-01-06 17:22:04.000
51 2005-01-06 17:23:04.000
52 2005-01-06 18:23:04.000
53 2005-01-06 19:23:04.000(所影响的行数为 4 行)
*/
--End
select * from moviedata where
DATEDIFF(d,'2000-1-1',INIME )=(select top 1 DATEDIFF(d,'2000-1-1',INIME ) from moviedata group by DATEDIFF(d,'2000-1-1',INIME ) order by count(*) desc)
where convert(varchar(10),intime,120)=(
select top 1 convert(varchar(10),intime,120) from @tb
group by convert(varchar(10),intime,120)
order by count(1) desc
)这个好,和我想的一样!