SELECT
*,
[2007的AMT数据]-[2006的AMT数据],
([2007的AMT数据]-[2006的AMT数据])/[2006的AMT数据]
FROM (
SELECT
cmcode,
SUM(CASE WHEN YY=2007 THEN AMT ELSE 0 END) AS [2007的AMT数据],
SUM(CASE WHEN YY=2007 THEN TS_AMT ELSE 0 END) AS [2007的TS_AMT数据],
SUM(CASE WHEN YY=2006 THEN AMT ELSE 0 END) AS [2006的AMT数据],
SUM(CASE WHEN YY=2006 THEN TS_AMT ELSE 0 END) AS [2006的TS_AMT数据],
FROM tb
GROUP BY cmcode
) AS T
*,
[2007的AMT数据]-[2006的AMT数据],
([2007的AMT数据]-[2006的AMT数据])/[2006的AMT数据]
FROM (
SELECT
cmcode,
SUM(CASE WHEN YY=2007 THEN AMT ELSE 0 END) AS [2007的AMT数据],
SUM(CASE WHEN YY=2007 THEN TS_AMT ELSE 0 END) AS [2007的TS_AMT数据],
SUM(CASE WHEN YY=2006 THEN AMT ELSE 0 END) AS [2006的AMT数据],
SUM(CASE WHEN YY=2006 THEN TS_AMT ELSE 0 END) AS [2006的TS_AMT数据],
FROM tb
GROUP BY cmcode
) AS T
如果固定就这7列可以这样：select cmcode,
sum(case when yy = 2007 then amt else 0 end) as 2007amt,
sum(case when yy = 2007 then ts_amt else 0 end) as 2007tsamt,
....
from ta
group by cmcode
在关键字 'FROM' 附近有语法错误。
数据表为LI
SELECT YY=2007的AMT数,YY=2007的TS_AMT数据,YY=2007的AMT数据,YY=2007的TS_AMT数据,
(YY=2007的AMT数据-YY=2006的AMT数据), (YY=2007的AMT数据-YY=2006的AMT数据)/YY=2006的AMT数据
FROM
(
SELECT cmcode,
sum(case when yy = 2007 then amt end) as YY=2007的AMT数据,
sum(case wen yy= 2007 then ts_amt end) as YY=2007的TS_AMT数据,
sum(case when yy = 2006then amt end) as YY=2007的AMT数据,
sum(case wen yy= 2006then ts_amt end) as YY=2007的TS_AMT数据
FROM LI
group byy cmcode) LO
select cmcode,
sum(case when yy = 2007 then amt else 0 end) as 2007amt,
sum(case when yy = 2007 then ts_amt else 0 end) as 2007tsamt,
sum(case when yy = 2006 then amt else 0 end) as 2006amt,
sum(case when yy = 2006 then ts_amt else 0 end) as 2006tsamt
from PG_ADD_CM
group by cmcode
服务器: 消息 170，级别 15，状态 1，行 2
第 2 行: '2007' 附近有语法错误。
SELECT
*,
[2007的AMT数据]-[2006的AMT数据],
([2007的AMT数据]-[2006的AMT数据])/[2006的AMT数据]
FROM (
SELECT
cmcode,
SUM(CASE WHEN YY=2007 THEN AMT ELSE 0 END) AS [2007的AMT数据],
SUM(CASE WHEN YY=2007 THEN TS_AMT ELSE 0 END) AS [2007的TS_AMT数据],
SUM(CASE WHEN YY=2006 THEN AMT ELSE 0 END) AS [2006的AMT数据],
SUM(CASE WHEN YY=2006 THEN TS_AMT ELSE 0 END) AS [2006的TS_AMT数据]
FROM PG_ADD_CM
GROUP BY cmcode
) AS T服务器: 消息 8134，级别 16，状态 1，行 1
遇到被零除错误。
这是最后 应该要得出来的数据 大大们 求教呀cmcode CM_2009.amt CM_2009.TS_AMT CM_2008.amt CM_2008.TS_AMT ADDAMT BL_AMT
76082010 21007486.76 2730973.23 ¥21,007,486.76 99.99
76061250 8497078.42 1104620.2 ¥8,497,078.42 99.99
83062990 21931867.43 2412505.48 16052207.5 1119351.86 ¥5,879,659.93 .3663
64029920 4820392.82 626651.07 ¥4,820,392.82 99.99
72202030 4663327.71 233166.42 ¥4,663,327.71 99.99
52084100 5394974.04 803796.61 1416550.04 173282.74 ¥3,978,424.00 2.8085
85161010 2358462.8 330184.8 ¥2,358,462.80 99.99
84292010 2222222.22 377777.78 ¥2,222,222.22 99.99
90184100 12950254.19 2201543.2 10891051.94 1851479.06 ¥2,059,202.25 .1891
85161020 1984132.43 277778.52 ¥1,984,132.43 99.99
38244010 1862134.33 204834.74 ¥1,862,134.33 99.99
96138000 4251059.84 552637.77 2444333.65 317763.4 ¥1,806,726.19 .7391
64062020 1670588.96 217176.56 ¥1,670,588.96 99.99
64029910 1656999.09 215409.92 ¥1,656,999.09 99.99
82073000 2496647.27 274631.19 939548.67 50362.07 ¥1,557,098.60 1.6573
84798999 1487362.61 208230.79 ¥1,487,362.61 99.99
84775900 1414384.69 198013.88 113754.7 14788.12 ¥1,300,629.99 11.4336
76082091 1042729.44 135554.82 ¥1,042,729.44 99.99
76042100 864499.08 112384.88 ¥864,499.08 99.99
51111990 780653.45 109291.48 ¥780,653.45 99.99
95030081 769769.55 107767.73 ¥769,769.55 99.99
38244090 756410.27 37820.5 ¥756,410.27 99.99
84571090 1144466.65 194559.29 405982.9 69017.1 ¥738,483.75 1.819
85042100 737786.32 125423.68 ¥737,786.32 99.99
...............
*,
[2007的AMT数据]-[2006的AMT数据],
NULLIF(([2007的AMT数据]-[2006的AMT数据]),0)/[2006的AMT数据]
FROM (
SELECT
cmcode,
SUM(CASE WHEN YY=2007 THEN AMT ELSE 0 END) AS [2007的AMT数据],
SUM(CASE WHEN YY=2007 THEN TS_AMT ELSE 0 END) AS [2007的TS_AMT数据],
SUM(CASE WHEN YY=2006 THEN AMT ELSE 0 END) AS [2006的AMT数据],
SUM(CASE WHEN YY=2006 THEN TS_AMT ELSE 0 END) AS [2006的TS_AMT数据]
FROM PG_ADD_CM
GROUP BY cmcode
) AS T
sum(case when yy = 2007 then amt else 0 end) as [2007amt],
sum(case when yy = 2007 then ts_amt else 0 end) as [2007tsamt],
sum(case when yy = 2006 then amt else 0 end) as [2006amt],
sum(case when yy = 2006 then ts_amt else 0 end) as [2006tsamt]
from PG_ADD_CM
group by cmcode
-- Author : HappyFlyStone
-- Date : 2009-05-31 17:52:26
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
Go
CREATE TABLE ta([cmcode] INT,[yy] int,[amt] NUMERIC(17,2),[ts_amt] NUMERIC(16,2),[time] DATETIME)
Go
INSERT INTO ta
SELECT 84659900,'2007',39592.07,2375.53,'2009-05-31 17:02:40.810' UNION ALL
SELECT 69089000,'2007',224296.59,12163.47,'2009-05-31 17:02:40.810' UNION ALL
SELECT 85452000,'2007',102366.09,13307.59,'2009-05-31 17:02:40.810' UNION ALL
SELECT 07129090,'2007',122991.14,15988.85,'2009-05-31 17:02:40.810' UNION ALL
SELECT 95049040,'2007',442678.89,50540.22,'2009-05-31 17:02:40.810' UNION ALL
SELECT 70109020,'2006',235845.21,30659.87,'2009-05-31 17:02:40.810' UNION ALL
SELECT 82159900,'2007',2193347.57,188067.26,'2009-05-31 17:02:40.810' UNION ALL
SELECT 64051000,'2007',98816.98,12221.44,'2009-05-31 17:02:40.810' UNION ALL
SELECT 52054400,'2007',3657652.26,402341.74,'2009-05-31 17:02:40.810' UNION ALL
SELECT 72299090,'2007',289333.64,14466.68,'2009-05-31 17:02:40.810' UNION ALL
SELECT 85131010,'2007',1794111.81,233234.55,'2009-05-31 17:02:40.810' UNION ALL
SELECT 96032900,'2007',31565.32,3553.72,'2009-05-31 17:02:40.810' UNION ALL
SELECT 84514000,'2007',1382981.76,179787.63,'2009-05-31 17:02:40.810' UNION ALL
SELECT 66019900,'2007',1502233.19,165245.61,'2009-05-31 17:02:40.810' UNION ALL
SELECT 90019000,'2006',3325.04,432.25,'2009-05-31 17:02:40.810' UNION ALL
SELECT 62089910,'2007',53589.91,5894.89,'2009-05-31 17:02:40.810' UNION ALL
SELECT 90251100,'2007',6745.40,876.90,'2009-05-31 17:02:40.810' UNION ALL
SELECT 84501110,'2007',1080129.89,140416.87,'2009-05-31 17:02:40.810' UNION ALL
SELECT 73239200,'2007',28092.31,1404.62,'2009-05-31 17:02:40.810' UNION ALL
SELECT 85161000,'2007',820903.62,106717.48,'2009-05-31 17:02:40.810'
GO
--Start
SELECT
*,
[2007的AMT数据]-[2006的AMT数据],
([2007的AMT数据]-[2006的AMT数据])/case when [2006的AMT数据] = 0 then 1 else [2006的AMT数据] end
FROM (
SELECT
cmcode,
SUM(CASE WHEN YY=2007 THEN AMT ELSE 0 END) AS [2007的AMT数据],
SUM(CASE WHEN YY=2007 THEN TS_AMT ELSE 0 END) AS [2007的TS_AMT数据],
SUM(CASE WHEN YY=2006 THEN AMT ELSE 0 END) AS [2006的AMT数据],
SUM(CASE WHEN YY=2006 THEN TS_AMT ELSE 0 END) AS [2006的TS_AMT数据]
FROM ta
GROUP BY cmcode
) AS T
--Result:
/*
cmcode 2007的AMT数据 2007的TS_AMT数据 2006的AMT数据 2006的TS_AMT数据
----------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
7129090 122991.14 15988.85 0.00 0.00 122991.14 122991.140000
52054400 3657652.26 402341.74 0.00 0.00 3657652.26 3657652.260000
62089910 53589.91 5894.89 0.00 0.00 53589.91 53589.910000
64051000 98816.98 12221.44 0.00 0.00 98816.98 98816.980000
66019900 1502233.19 165245.61 0.00 0.00 1502233.19 1502233.190000
69089000 224296.59 12163.47 0.00 0.00 224296.59 224296.590000
70109020 0.00 0.00 235845.21 30659.87 -235845.21 -1.000000
72299090 289333.64 14466.68 0.00 0.00 289333.64 289333.640000
73239200 28092.31 1404.62 0.00 0.00 28092.31 28092.310000
82159900 2193347.57 188067.26 0.00 0.00 2193347.57 2193347.570000
84501110 1080129.89 140416.87 0.00 0.00 1080129.89 1080129.890000
84514000 1382981.76 179787.63 0.00 0.00 1382981.76 1382981.760000
84659900 39592.07 2375.53 0.00 0.00 39592.07 39592.070000
85131010 1794111.81 233234.55 0.00 0.00 1794111.81 1794111.810000
85161000 820903.62 106717.48 0.00 0.00 820903.62 820903.620000
85452000 102366.09 13307.59 0.00 0.00 102366.09 102366.090000
90019000 0.00 0.00 3325.04 432.25 -3325.04 -1.000000
90251100 6745.40 876.90 0.00 0.00 6745.40 6745.400000
95049040 442678.89 50540.22 0.00 0.00 442678.89 442678.890000
96032900 31565.32 3553.72 0.00 0.00 31565.32 31565.320000(20 行受影响)*/
--End