如何对项目范围执行SQL查询？

值（'item_3'，778,920,12,330，'201703'，'type_B'） 插入#Table_A val ues（'item_4'，315,220,120,40，'201703'，'type_B'） ------------------------ --------- - 输入参数--------------- 声明@Month varchar（6 ），@ prevMonth varchar（6） set @ Month ='201703' set @ prevMonth ='201702' ---- ----------------------------- select * from（ select ROW_NUMBER（）结束（col7按col1排序）作为SlNo，col1，col2，col3，col4，col5，col6，col7来自#Table_A，其中col6 = @ Month union all 选择计数（col1）+1为SlNo，'Sum'为col1，sum（col2）为col2，sum（col3）为col3，sum（col4）为col4，sum（col5）为col5，col6，来自#Table_A的col7 = col6 = @ month group by col6，col7 union all 选择count（tmp2.col2）+2为SlNo，'Rate'为col1， （（sum（tmp2.col2）/（ 从#Table_A中选择总和（col2），其中col6 = @ prevMonth和col7 = tmp2.col7））* 100） -100为col2， （（sum（tmp2.col3）/（ ）从#Table_中选择总和（col3）其中col6 = @ prevMonth和col7 = tmp2.col7））* 100）-100为col3， （（sum（tmp2.col4）/（ ）选择总和（col4）来自#Table_A，其中col6 = @ prevMonth，col7 = tmp2.col7））* 100）-100 as col4， （（sum（tmp2.col5）/（ 从#Table_A中选择sum（col5），其中col6 = @ prevMonth，col7 = tmp2.col7））* 100）-100 as col5， tmp2.col6，tmp2.col7来自#Table_A tmp2其中tmp2.col6 = @Month group by tmp2.col6，tmp2.col7）t by col7，SlNo --Drop临时数据----- drop table #Table_A ---- END ------------- my database has a main table (ex. Table_A). column 1 contains name of items and column 6 is date of my data row. Column 7 is type of items of column 1 which are from A to z.for example (some rows of Table_A):|item_1 |200 |120 |300 |40 |201702 |type_A ||item_2 |210 |320 |340 |10 |201702 |type_A ||item_1 |150 |30 |70 |38 |201703 |type_A ||item_2 |315 |220 |120 |40 |201703 |type_A ||item_3 |40 |500 |110 |35 |201702 |type_B ||item_4 |758 |78 |152 |61 |201702 |type_B ||item_3 |778 |920 |12 |330 |201703 |type_B ||item_4 |315 |220 |120 |40 |201703 |type_B |now i want to show items from every type in column 7 from a specific date (ex.201703) and then below that sum of column 2, sum of column 3, sum of column 4, sum of column 5. and then below that rate of growth which calculate within function (((sum of month / sum of previous month)*100)-100)and for the above example the result i want is:|item_1 |150 |30 |70 |38 |201703 |type_A ||item_2 |315 |220 |120 |40 |201703 |type_A ||sum |465 |250 |190 |78 |201703 |type_A ||rate |13.1 |-43.1|-74.3|56 |201703 |type_A ||item_3 |778 |920 |12 |330 |201703 |type_B ||item_4 |315 |220 |120 |40 |201703 |type_B ||sum |1093 |1140 |132 |370 |201703 |type_B ||rate |36.96|97.23|-18.5|285.4|201703 |type_B |What I have tried:(SELECT col1, col2, col3, col4, col5, col6, col7FROMTable_A INNER JOIN Table_B ON Table_A.col1 = Table_B.col1WHERE Table_A.col6 = 201703 AND (Table_A.col7 = 'type_A'))UNION ALL(SELECT 'sum', SUM(col2), SUM(col3), SUM(col4), SUM(col5), 201703, 'type_A'FROMTable_A INNER JOIN Table_B ON Table_A.col1 = Table_B.col1WHERE Table_A.col6 = 201703 AND (Table_A.col7 = 'type_A')) --sum of data with 'g'UNION ALL(SELECT N'Rate',(ROUND(((((SELECTCONVERT (FLOAT,SUM(col2))FROMTable_A WHERE Table_A.col6 = 201703 AND (Table_A.col7 = 'type_A'))/(SELECTCONVERT (FLOAT,SUM(col2))FROMTable_A WHERE Table_A.col6 = (201703 - 1) AND (Table_A.col7 = 'type_A')) )*100)-100),2)),(ROUND(((((SELECTCONVERT (FLOAT,SUM(col3))FROMTable_A WHERE Table_A.col6 = 201703 AND (Table_A.col7 = 'type_A'))/(SELECTCONVERT (FLOAT,SUM(col3))FROMTable_A WHERE Table_A.col6 = (201703 - 1) AND (Table_A.col7 = 'type_A')))*100)-100),2)),(ROUND(((((SELECTCONVERT (FLOAT,SUM(col4))FROMTable_A WHERE Table_A.col6 = 201703 AND (Table_A.col7 = 'type_A'))/(SELECTCONVERT (FLOAT,SUM(col4))FROMTable_A WHERE Table_A.col6 = (201703 - 1) AND (Table_A.col7 = 'type_A')))*100)-100),2)),(ROUND(((((SELECTCONVERT (FLOAT,SUM(col5))FROMTable_A WHERE Table_A.col6 = 201703 AND (Table_A.col7 = 'type_A'))/(SELECTCONVERT (FLOAT,SUM(col5))FROMTable_A WHERE Table_A.col6 = (201703 - 1) AND (Table_A.col7 = 'type_A')))*100)-100),2)),NULL,'type_A')but this code shows only one type from column 7. 解决方案 Try followig codeSELECT COL1,COL2,COL3 ,COL4 ,COL5 ,COL6 ,COL7 FROM TABLE_A WHERE COL7='TYPE_A' AND COL6 = '201703'UNION ALLSELECT 'SUM' AS COL1,SUM(COL2),SUM(COL3) ,SUM(COL4) ,SUM(COL5) ,COL6 ,MAX(COL7) FROM TABLE_A GROUP BY COL6,COL7 HAVING COL6 ='201703' AND COL7='TYPE_A'UNION ALL---RATE CALCULATIONSELECT A.COL1,(((A.COL2/CAST(B.COL2PREV AS FLOAT ))*100)-100) AS COL2 ,(((A.COL3/CAST(B.COL3PREV AS FLOAT ))*100)-100) AS COL3 ,(((A.COL4/CAST(B.COL4PREV AS FLOAT ))*100)-100) AS COL4 ,(((A.COL5/CAST(B.COL5PREV AS FLOAT ))*100)-100) AS COL5 ,A.COL6 AS COL6,A.COL7 AS COL7FROM(SELECT 'RATE' AS COL1,SUM(COL2) COL2,SUM(COL3) COL3 ,SUM(COL4) COL4,SUM(COL5) COL5 ,COL6 ,MAX(COL7) COL7 FROM TABLE_A GROUP BY COL6,COL7 HAVING COL6 ='201703' AND COL7='TYPE_A') AS A,(SELECT 'RATE' AS COL1PREV,SUM(COL2) COL2PREV,SUM(COL3) COL3PREV ,SUM(COL4) COL4PREV,SUM(COL5) COL5PREV,COL6 ,MAX(COL7) AS COL7PREV FROM TABLE_A GROUP BY COL6,COL7 HAVING COL6 ='201702' AND COL7='TYPE_A') AS BUNION ALLSELECT COL1,COL2,COL3 ,COL4 ,COL5 ,COL6 ,COL7 FROM TABLE_A WHERE COL7='TYPE_B' AND COL6 = '201703'UNION ALLSELECT 'SUM' AS COL1,SUM(COL2),SUM(COL3) ,SUM(COL4) ,SUM(COL5) ,COL6 ,MAX(COL7) FROM TABLE_A GROUP BY COL6,COL7 HAVING COL6 ='201703' AND COL7='TYPE_B'UNION ALLSELECT A.COL1,(((A.COL2/CAST(B.COL2PREV AS FLOAT ))*100)-100) AS COL2 ,(((A.COL3/CAST(B.COL3PREV AS FLOAT ))*100)-100) AS COL3 ,(((A.COL4/CAST(B.COL4PREV AS FLOAT ))*100)-100) AS COL4 ,(((A.COL5/CAST(B.COL5PREV AS FLOAT ))*100)-100) AS COL5 ,A.COL6 AS COL6,A.COL7 AS COL7FROM(SELECT 'RATE' AS COL1,SUM(COL2) COL2,SUM(COL3) COL3 ,SUM(COL4) COL4,SUM(COL5) COL5 ,COL6 ,MAX(COL7) COL7 FROM TABLE_A GROUP BY COL6,COL7 HAVING COL6 ='201703' AND COL7='TYPE_B') AS A,(SELECT 'RATE' AS COL1PREV,SUM(COL2) COL2PREV,SUM(COL3) COL3PREV ,SUM(COL4) COL4PREV,SUM(COL5) COL5PREV,COL6 ,MAX(COL7) AS COL7PREV FROM TABLE_A GROUP BY COL6,COL7 HAVING COL6 ='201702' AND COL7='TYPE_B') AS BThe output you want is achieved by below SQL query..--Prepare sample datacreate table #Table_A(col1 varchar(50), col2 numeric(18,2), col3 numeric(18,2), col4 numeric(18,2), col5 numeric(18,2), col6 varchar(10), col7 varchar(50))insert into #Table_Avalues('item_1',200,120,300,40,'201702','type_A')insert into #Table_Avalues('item_2',210,320,340,10,'201702','type_A')insert into #Table_Avalues('item_1',150,30 ,70 ,38 ,'201703','type_A')insert into #Table_Avalues('item_2',315,220,120,40 ,'201703','type_A')insert into #Table_Avalues('item_3',40 ,500,110,35 ,'201702','type_B')insert into #Table_Avalues('item_4',758,78 ,152,61 ,'201702','type_B')insert into #Table_Avalues('item_3',778,920,12 ,330,'201703','type_B')insert into #Table_Avalues('item_4',315,220,120,40 ,'201703','type_B')-----------------------------------Input parameters---------------Declare @Month varchar(6),@prevMonth varchar(6)set @Month='201703'set @prevMonth='201702'---------------------------------select * from (select ROW_NUMBER() over(partition by col7 order by col1) as SlNo,col1,col2,col3,col4,col5,col6,col7 from #Table_A where col6=@Monthunion allselect count(col1)+1 as SlNo,'Sum' as col1,sum(col2) as col2,sum(col3) as col3,sum(col4) as col4,sum(col5) as col5,col6,col7 from #Table_A where col6=@Month group by col6,col7union allselect count(tmp2.col2)+2 as SlNo,'Rate' as col1,((sum(tmp2.col2)/(select sum(col2) from #Table_A where col6=@prevMonth and col7=tmp2.col7))*100)-100 as col2,((sum(tmp2.col3)/(select sum(col3) from #Table_A where col6=@prevMonth and col7=tmp2.col7))*100)-100 as col3,((sum(tmp2.col4)/(select sum(col4) from #Table_A where col6=@prevMonth and col7=tmp2.col7))*100)-100 as col4,((sum(tmp2.col5)/(select sum(col5) from #Table_A where col6=@prevMonth and col7=tmp2.col7))*100)-100 as col5,tmp2.col6,tmp2.col7 from #Table_A tmp2 where tmp2.col6=@Month group by tmp2.col6,tmp2.col7) t order by col7,SlNo--Drop temporary data-----drop table #Table_A----END------------- 这篇关于如何对项目范围执行SQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！