Sql 按日期统计

[hu_service]

需求：文章按日期进行统计
最终显示结果是：
部门名称 文章总数 本年数量 上月数量 本月数量 当天数量 排名
xx部门 250000 36000 1500 600 80 1
xx部门 56200 5600 560 6800 120 2

News表字段
NewID bumenID NewsTitle NewsContent AddTime

Bumen表字段
BumenID BumenName


请教高手Sql语句要怎么写？如果新闻表设计不合理，需要怎么改动呢，谢谢!

[hu_service]

急啊，怎么没人会吗,期待高手的出现

[nianran520]

select 
r.BumenName as 部门名称,
count(t.NewID) as 文章总数,
(select count(1) from News where BumenID=t.BumenID and year(AddTime)= year(getdate())) as 本年数量,
(select count(1) from News where BumenID=t.BumenID and datediff(month,AddTime,getdate()) = 1) as 上月数量,
(select count(1) from News where BumenID=t.BumenID and datediff(month,AddTime,getdate()) = 0) as 本月数量,
(select count(1) from News where BumenID=t.BumenID and datediff(day,AddTime,getdate()) = 0) as 当天数量
from News t join  Bumen r
on t.BumenID=r.BumenID
group by r.BumenName

[nianran520]

--加上排名
select 
r.BumenName as 部门名称,
t.num as 文章总数,
(select count(1) from News where BumenID=t.BumenID and year(AddTime)= year(getdate())) as 本年数量,
(select count(1) from News where BumenID=t.BumenID and datediff(month,AddTime,getdate()) = 1) as 上月数量,
(select count(1) from News where BumenID=t.BumenID and datediff(month,AddTime,getdate()) = 0) as 本月数量,
(select count(1) from News where BumenID=t.BumenID and datediff(day,AddTime,getdate()) = 0) as 当天数量,
t.排名
from 
(select BumenID,num,(select count(1) from (
select BumenID,count(1) as num from News
group by BumenID
) g where num<=h.num) as 排名
from
(
select BumenID,count(1) as num from News
group by BumenID
) h ) t 
join  Bumen r
on t.BumenID=r.BumenID

[aladdin_lidx]

楼上的基本可以，不过要注意，对月的判断要先判断是否在同一年下，同理，对当天判断要先判断是否在同一年的同一个月里。

[nianran520]

--排序加上
order by t.num desc

[Jack2013tong]

用group by 加子查询

[mytimes_walker]

WITH A AS(
SELECT B.[BumenName] AS [部门名称],COUNT(*) AS [文章总数],ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS 排名 FROM [News] AS N
INNER JOIN [Bumen] AS B
ON N.[BumenID] = B.[BumenID]
GROUP BY B.[BumenName]
)SELECT * FROM A
ORDER BY [文章总数] DESC
SELECT COUNT(*) FROM [News] WHERE DATEPART(yy,AddTime)=DATEPART(yy,GETDATE()) AND [BumenID]=1
SELECT COUNT(*) FROM [News] WHERE DATEPART(yy,AddTime)=DATEPART(yy,GETDATE()) AND DATEPART(mm,AddTime)=DATEPART(mm,GETDATE()) [BumenID]=1
SELECT COUNT(*) FROM [News] WHERE DATEPART(yy,AddTime)=DATEPART(yy,GETDATE()) AND DATEPART(mm,AddTime)=DATEPART(mm,GETDATE()) AND  DATEPART(dd,AddTime)=DATEPART(dd,GETDATE()) [BumenID]=1

以为的能力和时间，只能写成这样了，期待高人出现。

[不懂装懂]

上月数量统计不会,帮顶