/ * - Originally: http://community.9cbs.net/expert/topic/3826/3826130.xml? Temp = .4175836 - * /
- Example Data - Construction Enterprise Name Create Table qiye (qiyemingcheng Char (16), Xingzhi Char (4), Shunxu Int) Insert Into Qiye Select 'Enterprise A', 'State', 1UNION All SELECT 'Enterprise B', 'State-owned', 2UNION All SELECT 'Enterprise Cerel', 'National'
- Construction Indicators Name table Create Table ZBMC (ZHIBIAOMING CHAR (20), SHUNXU INT, FLAG INT) INSERT INTO ZBMC SELECT 'Total Value', 1 UNION All SELECT 'Total Profit', 2, 1 UNION All Select '万 产 产 产Energy consumption ', 3, -1Union all select' per capita income ', 4, 1 union all Select' 10,000 yuan loss', 5, -1
- Construction specific indicator data sheet Enterprise Name, Indicator Name, Year, Month, French Numerical Create Table Zhibiao (Qyname Char (10), Zbname Char (16), Nian Int, Yue Int, EbiBiaozhi float) Insert Into ZhibiaOSelect ' A ',' total output value ', 2004, 2, 88888.99Union all Select' Enterprise A ',' General Profit ', 2004, 2,666.66Union All Select' Enterprise A ',' Wan Yuan Yizhen Value Energy Consumption ', 2004, 2, 2222.22 Union All Select 'Enterprise A', 'Per capita income', 2004, 2,2008.18ueion all Select 'Enterprise A', 'Wan Yuan Loss', 2004, 2,200.96Union All Select 'Enterprise A', 'total output value', 2005, 1, 9888.99Union All Select 'Enterprise A', 'General Profit', 2005, 1,866.66Union All Select 'Enterprise A', 'Wanyuan Yield Value Energy Consumption', 2005, 1, 20211Union All Select 'Enterprise A', 'per capita Income ', 2005, 1, 2208.87Union All Select' Enterprise A ',' Wan Yuan Loss ', 2005, 1, 203UNION All Select' Enterprise A ',' Total Value ', 2005, 2,99999.99Union All Select' Enterprise , 'General Profit', 2005, 2, 888.66Union All Select 'Enterprise A', 'Wan Yuan Yage Value Energy Consumption', 2005, 2, 2122.22UNION All Select 'Enterprise A', 'Per capita Revenue', 2005, 2, 2708.18Union All SELECT 'Enterprise,', 2005, 2,200.96Union All Select 'Enterprise B', 'total output value', 2004, 2, 7888.99une, 2004, 2, 7888.99Union All Select 'Enterprise B', 'General Profit', 2004, 2,666.66 All SELECT 'Enterprise B', 'Wanyuan Yield Value Energy Consumption', 2004, 2, 2222.22UNION All Select 'Enterprise B', 'Per capita Revenue', 2004, 2,2008.18Union All Select 'Enterprise B', '10,000 yuan loss ', 2004, 2,200. 96UNION All SELECT 'Enterprise B', 'total output value', 2005, 1, 7988.99Union all Select 'Enterprise B', 'General Profit', 2005, 1,876.66UNION All Select 'Enterprise B', '10,000 yuan output value ", 2005, 1, 2022.11Union All Select 'Enterprise B', 'Per capita income', 2005, 1, 2208.87UNION All Select 'Enterprise B', '10,000 Dollar Loss', 2005, 1, 203UNION All SELECT' Enterprise B ',' Yield Value ', 2005, 2, 96999.99Union All SELECT' Enterprise B ',' Total Profit '
2005, 2, 868.66Union All SELECT 'Enterprise B', 'Wanyuan Yield Value Energy Consumption', 2005, 2, 2122.22UNION All Select 'Enterprise B', 'Per capita Revenue', 2005, 2, 2608.18Union All Select 'Enterprise B' , '10,000 yuan loss', 2005, 2,200.96 union all select 'Enterprise C', 'total output value', 2004, 2, 5888.99Union all Select 'Enterprise C & I,' total profit ', 2004, 2, 666.66UNION All Select' Enterprise Cinent ',' 万 元 产 产 ', 2004, 2,2222.2212Union All Select' Enterprise C & I, 'per capita income', 2004, 2,2008.18 union all Select 'Enterprise C & I,' Wan Yuan Loss', 2004, 2,200.96 Union all select 'Enterprise Cinent,' total output value ', 2005, 1, 6888.99UNION All Select' Enterprise C ',' General Profit ', 2005, 1, 966.66Union All Select' Enterprise C & I, '10,000 yuan output value ", 2005, 1, 2022.11UNION All Select 'Enterprise C & I,' per capita income ', 2005, 1, 2208.87Union all Select' Enterprise C & I, '10,000 yuan loss', 2005, 1, 203UNION All Select 'Enterprise C & I,' total Value ', 2005, 2, 99999.99Union All Select' Enterprise C & I, 'General Profit', 2005, 2, 888.66Union All Select 'Enterprise C', 'Wanyuan Yield Value Energy Consumption', 2005, 2, 2122.22UNION All Select ' C ',' per capita income ', 2005, 2, 2708.18Union all Select' Enterprise C & I, '10,000 Dollar Loss', 2005, 2,100.96Union All Select 'Enterprise Ding', 'total output value', 2004, 2, 8488.99une SELECT 'Enterprise Ding', 'General Profit', 2004, 2,646.66Union All Select 'Enterprise Ding', 'Wanyuan Yield Value Energy Consumption', 2004, 2, 2222.22UNION All Select 'Enterprise Ding', 'Per capita Income', 2004, 2,2008.18 union all select 'Enterprise Ding', '万 元', 2004, 2,200.96 union all select 'Enterprise Ding', 'total output value', 2005, 1,94888.99une, 2005, 1,856.66une, 2005, 1, 856.66UNION All Select 'Enterprise Ding' Energy consumption ', 2005, 1, 2022.11Union All Select' Enterprise Ding ',' per capita income ', 2005, 1, 2208.87Union all Select' Enterprise Ding ',' 10,000 Dollar Loss', 2005, 1, 203Union All select 'Enterprise Ding', 'total output value
, 2005, 2, 99999.99Union All Select 'Enterprise,' , 'Per capita income', 2005, 2, 2708.18Union all Select 'Enterprise Ding', '10,000 Dollar Loss', 2005, 2,400.96GO / * - Treatment Requirements
Now we must count the index of industrial enterprises in the province, industrial enterprises must be divided into two categories of private enterprises and state-owned enterprises, and when statistics require state-owned enterprises in front of private enterprises, they must count the monthly situation in accordance with the indicator classification, and compared last month, The indicator is increased, this year, this year, this year, this year, the same period of this month, the increase in indicators of the indicator will subtract the last month of the last month, and then it is better to get it according to different indicators, because required indicators and units It must be displayed in advance in advance, so I have designed three tables, one is the company name QIYE, there is a corporate name in the inside, the company's nature (used to distinguish between state-owned enterprises or private), display order (because required order display and Print), one is the indicator name table ZBMC, there is an indicator name, display order, I want to add a field to indicate the mark of the indicator upgrade, such as the total output value increases, the indicator has risen, and the energy consumption is increased The indicator has fallen, but I haven't figured out how to define this field yet, so I use a tag, which indicates that the indicator is increasing, indicating that the indicator value is increased. Explain that the indicator has fallen, the last table is the specific indicator information table ZHibiao, there is a corporate name, indicator name, year, month, monthly indicator value
How to use what SQL statement can get the result of the following format: Suppose is the statistics of February this year
Indicator Name Unit Name Indicator Value and last month's compared to indicator improvement last year's same period last year ------------------------- -------------- total value province, national private enterprise, company, B, Cricket, China, Cricket, China, China, China, private enterprise, company, company, CET, Cricket, Ding Wanyuan, energy consumption, province, national private enterprise Company A company C enterpr company Ding - * /
- Inquiry stored procedure Create Proc P_Qry @ Year Int, - To query the year @Month Int - to query the month Asset NoCount OndeClare @Y Int, @ m int, @ YEAR-1IF @ Month = 1 SELECT @ y = @ Year-1, @ m = 12ELSE SELECT @ y = @ Year, @ m = @ MONTH-1
Select Case When a.s2 = 0 THEN A. EZHIBIAOMING ELSE '' END AS Indicator Name, a.qiyemingcheng AS Unit Name, Indicator Value = Cast (a. Indicator value as Decimal (10, 2)), last month indicator value = Cast (isnull (b. last, 0) as Decimal (10, 2)), and last month rather= case when ISNULL (b. last, 0) = 0 THEN '0.00%' else cast (Cast (a. indicator value * 100 / b. Upper As Decimal (10, 2)) as varchar) '%' end, indicator lifting = Substring ('- ↑', cast (Sign (a. Indicator value - INNULL (b. Last, 0)) * a.flag as int) 2, 1) Cast (ABS (Cast (a. Narrator - INULL (B. Previous, 0) AS Decimal (10, 2))) AS VARCHAR), this year Cumulative = Cast (a. Cumulative as Decimal (10, 2) this year) , qiyemingcheng = case when grouping (qy.xingzhi) = 1 Then N 'The province' when grouping (qyeemingcheng) = 1 Then qy.xingzhi else qy.qiyemingcheng end, indicator value = sum (case when c.yue = @ Month Then Zhibiaozhi end), this year's cumulative = SUM (zhibiaozhi), Flag = max (zb.flag), S1 = min (zb.shunxu), S2 = 1-grouping (qy.xingzhi), S3 = 1-grouping qy.qiyemingcheng, s4 = min (qy.shunxu) from zbmc zb, qiye qy, zhibiao c where c.nian=@year and c.yue <= @ MONTH and C.QYName = qy.qiyemingcheng and c.zbname = Zb.zhibi aoming group by zb.zhibiaoming, qy.xingzhi, qy.qiyemingcheng with rollup having grouping (zb.zhibiaoming) = 0) a left join (- of the select zb.zhibiaoming, qiyemingcheng = case when grouping (qy.xingzhi) = 1 THEN N 'The province' when grouping (qy.xingzhi else qy.qiyemingcheng end, last time = SUM (zhibiaozhi), S1 = min (zb.shunxu), S2 = 1-grouping (qy.xingzhi ), S3 = 1-grouping (qy.qiyemingcheng), S4 = min (qy.shunxu) from ZBMC ZB, qiye qy, zhibiao c where c.nian=@y and c.yue=@m and c.qyname = qy .qiyemingcheng and c.zbname =
zb.zhibiaoming group by zb.zhibiaoming, qy.xingzhi, qy.qiyemingcheng with rollup having grouping (zb.zhibiaoming) = 0) b on a.zhibiaoming = b.zhibiaoming and a.qiyemingcheng = b.qiyemingchengleft join (- year select zb.zhibiaoming, qiyemingcheng = case when grouping (qy.xingzhi) = 1 then N 'province' when grouping (qy.qiyemingcheng) = 1 then qy.xingzhi else qy.qiyemingcheng end, period = sum (zhibiaozhi), s1 = min (zb.shunxu), S2 = 1-grouping (qy.xingzhi), S3 = 1-grouping (qy.qiyemingcheng, s4 = min (qy.shunxu) from ZBMC ZB, QIYE qy, Ezhibiao C Where C. nian = @ y1 and c.yue=@month and c.qyname = qy.qiyemingcheng and c.zbname = zb.zhibiaoming group by zb.zhibiaoming, qy.xingzhi, qy.qiyemingcheng with rollup having grouping (zb.zhibiaoming) = 0) c on a.zhibiaoming = c.zhibiaoming and a.qiyemingcheng = C.qiyemingchengorder by A.s1, A.S2, A.S3, A.S4GO - call EXEC P_QRY 2005, 2GO
- Delete Test DROP TABLE qiye, ZBMC, ZHIBIAODROP PROC P_QRY
/ * Test results you look at * /
