select '问题回复',count(一月) 一月,...count(十二月) 十二月 from (select case datepart(mm,dtCreate) when 1 then blnNewSolve end 一月, case datepart(mm,dtCreate) when 1 then blnNewSolve end 二月, ... case datepart(mm,dtCreate) when 1 then blnNewSolve end 十二月 from 表 where blnNewSolve=1 ) a union all select '问题未回复',count(一月) 一月,...count(十二月) 十二月 from (select case datepart(mm,dtCreate) when 1 then blnNewSolve end 一月, case datepart(mm,dtCreate) when 1 then blnNewSolve end 二月, ... case datepart(mm,dtCreate) when 1 then blnNewSolve end 十二月 from 表 where blnNewSolve=0 ) a
我现在的思路是现将其安月份列出: 月份 回... 未... ----------------------- 1 15 2 2 10 4 3 7 5 然后在行列转换. 代码如下: select lngMonth As '月份',isnull(lngNewQuestion,0) As '新增问题',isnull(lngReverNum,0) As '问题回复',isnull(lngNotReverNum,0) As '未回复问题' from ( select month(dtCreate) as 'lngMonth', sum(lngNewQuestion) as 'lngNewQuestion', sum(lngReverNum) as 'lngReverNum', sum(lngNotReverNum) as 'lngNotReverNum' from ( select dtCreate, case when lngParentForumID=0 and dtCreate between convert(datetime,'2007-1-1') and Convert(datetime,'2007-4-1') and dtCreate is not null then 1 else 0 end lngNewQuestion, case when lngRevertNum>0 and lngParentForumID=0 and dtCreate between convert(datetime,'2007-1-1') and Convert(datetime,'2007-4-1') then 1 else 0 end lngReverNum, case when (lngRevertNum=0 or lngRevertNum is Null) and lngParentForumID=0 and dtCreate between convert(datetime,'2007-1-1') and getdate() then 1 else 0 end lngNotReverNum from Forum where dtCreate is not null ) TmpTable2 group by month(dtCreate) ) TmpTable1 where lngMonth is not null 现在问题是未回复问题由于Group By的原因它按月分开计算了未回复问题并不是想要求的(未回复 是从功能上线开通日起计算的(1.1日),比方3月的“未回复问题”数字,是1.1日到3.31日的所有未回复问题。2月的“未回复问题”数字,是1.1日到2.31日的所有未回复问题。)请大虾指点,急!!!
(select case datepart(mm,dtCreate) when 1 then blnNewSolve end 一月,
case datepart(mm,dtCreate) when 1 then blnNewSolve end 二月,
...
case datepart(mm,dtCreate) when 1 then blnNewSolve end 十二月 from 表 where blnNewSolve=1 ) a
union all
select '问题未回复',count(一月) 一月,...count(十二月) 十二月 from
(select case datepart(mm,dtCreate) when 1 then blnNewSolve end 一月,
case datepart(mm,dtCreate) when 1 then blnNewSolve end 二月,
...
case datepart(mm,dtCreate) when 1 then blnNewSolve end 十二月 from 表 where blnNewSolve=0 ) a
月份 回... 未...
-----------------------
1 15 2
2 10 4
3 7 5
然后在行列转换.
代码如下:
select lngMonth As '月份',isnull(lngNewQuestion,0) As '新增问题',isnull(lngReverNum,0) As '问题回复',isnull(lngNotReverNum,0) As '未回复问题' from (
select month(dtCreate) as 'lngMonth',
sum(lngNewQuestion) as 'lngNewQuestion',
sum(lngReverNum) as 'lngReverNum',
sum(lngNotReverNum) as 'lngNotReverNum' from (
select dtCreate,
case when lngParentForumID=0 and dtCreate between convert(datetime,'2007-1-1') and Convert(datetime,'2007-4-1')
and dtCreate is not null then 1 else 0 end lngNewQuestion,
case when lngRevertNum>0 and lngParentForumID=0 and
dtCreate between convert(datetime,'2007-1-1') and Convert(datetime,'2007-4-1') then 1 else 0 end lngReverNum,
case when (lngRevertNum=0 or lngRevertNum is Null) and lngParentForumID=0 and
dtCreate between convert(datetime,'2007-1-1') and getdate() then 1 else 0 end lngNotReverNum
from Forum where dtCreate is not null
) TmpTable2 group by month(dtCreate)
) TmpTable1 where lngMonth is not null
现在问题是未回复问题由于Group By的原因它按月分开计算了未回复问题并不是想要求的(未回复 是从功能上线开通日起计算的(1.1日),比方3月的“未回复问题”数字,是1.1日到3.31日的所有未回复问题。2月的“未回复问题”数字,是1.1日到2.31日的所有未回复问题。)请大虾指点,急!!!