SELECT COUNT(user_number) AS Expr2,
convert(char(10), user_login_time, 120) AS Expr1
FROM table
GROUP BY convert(char(10), user_login_time, 120)
convert(char(10), user_login_time, 120) AS Expr1
FROM table
GROUP BY convert(char(10), user_login_time, 120)
SELECT COUNT(user_number) AS Expr2,
convert(char(7), user_login_time, 120) AS Expr1
FROM table
GROUP BY convert(char(7), user_login_time, 120)
年:
SELECT COUNT(user_number) AS Expr2,
convert(char(4), user_login_time, 120) AS Expr1
FROM table
GROUP BY convert(char(4), user_login_time, 120)
FROM table
GROUP BY convert(char(10), user_login_time, 120)
您看看行不行?
按年
SELECT COUNT(user_number) AS Expr1
FROM table
GROUP BY datepart(yyyy,user_login_time)按月
SELECT COUNT(user_number) AS Expr1
FROM table
GROUP BY datepart(mm,user_login_time)按日
SELECT COUNT(user_number) AS Expr1
FROM table
GROUP BY datepart(dd,user_login_time)但是有一个缺陷,就是按日统计的时候不会区分月份的问题,按月统计的时候不会区分年份
因为对3500多万调记录进行统计,所以效率就很重要了
SELECT COUNT(user_number) AS Expr1
FROM table
group by cast(datepart(yyyy,user_login_time) as varchar(4)) + cast(datepart(mm,user_login_time) as varchar(2)) + cast(datepart(dd,user_login_time) as varchar(2))
GROUP BY right( convert (char(10),user_login_time,120),4)按月统计的时候不会区分年份
GROUP BY convert(char(7), user_login_time, 120)