多谢两位的帮助,我不太能理解两位的意思.可能是我表达的不清楚. 原数据结构比较复杂,是多表的查询.我把我现有查询简化如下: SELECT T0.SlpName AS '销售', COUNT(DocNum) AS '订单数', SUM(T0.DocTotal) AS '订单总计', (SELECT SUM(T1.DocTotal) AS '凭证总计' FROM ORDR T1 WHERE T1.DocDate >= '05/07/01' AND T1.DocDate <= '05/07/31' AND T0.SlpName = T1.SlpName) AS '05年7月', (SELECT SUM(T1.DocTotal) AS '凭证总计' FROM ORDR T1 WHERE T1.DocDate >= '05/06/01' AND T1.DocDate <= '05/06/30' AND T0.SlpName = T1.SlpName) AS '05年7月', ... (SELECT SUM(T1.DocTotal) AS '凭证总计' FROM ORDR T1 WHERE T1.DocDate >= '05/01/01' AND T1.DocDate <= '05/01/31' AND T0.SlpName = T1.SlpName) AS '05年1月' FROM ORDR T0 WHERE T0.CANCELED = 'N' AND T0.DocDate >= '05/01/01' GROUP BY T0.SlpName with rollup 原查询的问题是每月要重新改,而且不能统计任意期间. 刚接触SQL,正在看书,但找不到头绪.望各位能帮忙.
年度 一月起始日期 二月起始日期 ... 十二月起始日期
-------------------------------------------------------------------------
2005 01/01/05-31/01/05 01/02/05-28/01/05 ... 01/12/05-31/12/05如此一查基本上需要14个Case语句,即在这种情况下,可能某个日期会不属于本年度,比如小于一月份会计周期的起始时间,则应该算做上一年度的12月份,若大于十二月的截止日期则当做下一年度的一月份(通常在国外的软件当中次25日做为一个月的结帐日,所以26及以后的日期就会算做下一个月,即12月26就有可能算做下一年度)。
SELECT T0.SlpName AS '销售', COUNT(DocNum) AS '订单数', SUM(T0.DocTotal) AS '订单总计',
(SELECT SUM(T1.DocTotal) AS '凭证总计' FROM ORDR T1 WHERE T1.DocDate >= '05/07/01' AND T1.DocDate <= '05/07/31' AND T0.SlpName = T1.SlpName) AS '05年7月',
(SELECT SUM(T1.DocTotal) AS '凭证总计' FROM ORDR T1 WHERE T1.DocDate >= '05/06/01' AND T1.DocDate <= '05/06/30' AND T0.SlpName = T1.SlpName) AS '05年7月',
...
(SELECT SUM(T1.DocTotal) AS '凭证总计' FROM ORDR T1 WHERE T1.DocDate >= '05/01/01' AND T1.DocDate <= '05/01/31' AND T0.SlpName = T1.SlpName) AS '05年1月'
FROM ORDR T0
WHERE T0.CANCELED = 'N' AND T0.DocDate >= '05/01/01'
GROUP BY T0.SlpName with rollup
原查询的问题是每月要重新改,而且不能统计任意期间. 刚接触SQL,正在看书,但找不到头绪.望各位能帮忙.
可以用存储过程实现。
组名 年月 统计数据
A组 01/05 100
A组 02/05 120
B组 01/05 200
B组 02/05 170然后再用行转列转成下面这种方式:
01/05 02/05 03/05 04/05 05/05 06/05 07/05
A组 100 120 200 150 160 170 300
B组 200 170 250 0 350 400 500
这样的话,就不用修改表的物理结构。行转列示例:declare @s nvarchar(4000)
set @s=''
select @s=@s+',['+cast([年月] as varchar)+']=max(case [年月] when '''+cast([年月] as varchar)+''' then [统计数据] else 0 end)'
from [表或视图名]
group by [年月]
select @s
exec('select [组名]'+@s+' from [表或视图名] where 你的条件 group by [组名]')