找出本年注册用户最多的一天
请教如何用一条语句求出本年度注册用户最多的一天注册数量和时间
数据库里的注册时间格式是按照 2007-1-23 12:30:20 这种格式存储
求这条语句的写法！
[解决办法]
create table T(Name varchar(10), regDate datetime)
insert T select 'AA ', '2006-12-01 '
insert T select 'BB ', '2006-12-02 '
insert T select 'CC ', '2007-01-01 '
insert T select 'DD ', '2007-01-02 '
insert T select 'EE ', '2007-01-02 '
select top 1 regDate=convert(char(10), regDate, 120), [count]=count(*) from T
where year(regDate)=year(getdate())
group by convert(char(10), regDate, 120)
order by 2 desc
--result
regDate count
---------- -----------
2007-01-02 2
(1 row(s) affected)
[解决办法]
设你的日期列为 'recordDate '
select * from
(
select (convert(varchar(10),recordDate,120))as 'day ',count(*)as 'cont ' from s2
where year(recordDate)=year(getdate())
group by (convert(varchar(10),recordDate,120))
)as aa
where aa.cont=(select max(cont)from (
select (convert(varchar(10),recordDate,120))as 'day ',count(*)as 'cont ' from s2
where year(recordDate)=year(getdate())
group by (convert(varchar(10),recordDate,120))
)as bb)
[解决办法]
设你的日期列为 'recordDate '~表名为 s2~~~!!!
[解决办法]
回
select top 1 regDate=convert(char(10), regDate, 120), [count]=count(*) from T
where year(regDate)=year(getdate())
group by convert(char(10), regDate, 120)
order by 2 desc
~~
这样写不对啊~~~如果注册次数最高的天~~有两个以上怎么办....
[解决办法]
select top 1 recorddate,count(1)
from table1 group by recorddate order by count(1) desc
这样就可以了撒
[解决办法]
select*from 记录注册用户的表 where 记录注册人数的字段=(select max(记录注册人数的字段) from 记录注册用户的表 where year(时间字段)=年 ) and year(时间字段)=年
[解决办法]
create table T(Name varchar(10), regDate datetime)
insert T select 'AA ', '2006-12-01 '
insert T select 'BB ', '2006-12-02 '
insert T select 'CC ', '2007-01-01 '
insert T select 'DD ', '2007-01-01 '
insert T select 'EE ', '2007-01-02 '
insert T select 'FF ', '2007-01-03 '
insert T select 'GG ', '2007-01-03 '
select top 1 with ties regDate=convert(char(10), regDate, 120), [count]=count(*) from T
where year(regDate)=year(getdate())
group by convert(char(10), regDate, 120)
order by 2 desc
--result
regDate count
---------- -----------
2007-01-01 2
2007-01-03 2
(2 row(s) affected)
[解决办法]
select top 1 用戶， count(*) as total from t group by left([time],8) order by total desc
[解决办法]
select max(d.c) from (
select count(date(regDate)) as c from datatable group by date(regDate))d
[解决办法]
select top 1 * from
(
select count(date(LAST_UPD)) as c ,date(LAST_UPD) as d from KITE.M_MGR_TBL group by date(LAST_UPD)
)e
order by e.c desc
[解决办法]
select top 1 * from
(
select count(date(regDate)) as c ,date(regDate) as d from datatable group by date(regDate)
)e
order by e.c desc
