如何对项目范围执行SQL查询？ [英] How to execute a SQL query for range of items?
本文介绍了如何对项目范围执行SQL查询？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的数据库有一个主表（例如Table_A）。第1列包含项目名称，第6列是我的数据行的日期。第7列是第1列的项目类型，从A到z。
例如（Table_A的某些行）：
< pre lang =HTML> | 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 |
现在我想在特定日期显示第7列中每种类型的项目（ex.201703）然后低于第2列的总和，第3列的总和，第4列的总和，第5列的总和，然后低于在函数内计算的增长率（（月的总和/之前的总和）月）* 100）-100）
和上面的例子我想要的结果是：
| item_1 | 150 | 30 | 70 | 38 | 201703 | type_A |
| item_2 | 315 | 220 | 120 | 40 | 201703 | type_A |
|总和| 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 |
|汇率| 36.96 | 97.23 | -18.5 | 285.4 | 201703 | type_B |
我试过的：
（
SELECT col1，col2，col3，col4，col5，col6，col7
FROM Table_A INNER JOIN Table_B ON Table_A.col1 = Table_B.col1
WHERE Table_A.col6 = 201703 AND（Table_A.col7 ='type_A'）
）
UNION ALL
（
SELECT'and'，SUM（col2），SUM（col3），SUM（col4），SUM（col5），201703，'type_A'
FROM Table_A INNER JOIN Table_B ON Table_A.col1 = Table_B.col1
WHERE Table_A.col6 = 201703 AND（Table_A.col7 ='type_A'）
） - 数据的总数为'g'
UNION ALL
（
SELECT
N'Rate'，
（
ROUND
（
（
（
（
）（
SELECT CONVERT （FLOAT，SUM（col2））
FROM Table_A
WHERE Table_A.col6 = 201703 AND（Table_A.col7 ='type_A'）
）
/
（
SELECT CONVERT（FLOAT，SUM（col2））
FROM Table_A
WHERE Table_A.col6 =（201703 - 1）AND（Table_A.col7 ='type_A'）
）
）* 100
）-100
） ，2
）
）
，
（
ROUND
（
（
（
（
（
SELECT CONVERT（FLOAT，SUM（col3））
FROM Table_A
WHERE Table_A.col6 = 201703 AND（Table_A.col7 ='type_A'）
）
/
（
SELECT CONVERT（FLOAT，SUM（col3））
FROM Table_A
WHERE Table_A.col6 =（201703 - 1）AND（Table_A.col7 ='type_A' ）
）
）* 100
）-100
），2
）
）
，
（
ROUND
（
（
（
（
（
SELECT CONVERT（FLOAT，SUM（col4））
FROM Table_A
WHERE Table_A.col6 = 201703 AND（Table_A.col7 ='type_A'）
）
/
（
SELECT CONVERT（FLOAT，SUM（col4））
FRO M Table_A
WHERE Table_A.col6 =（201703 - 1）AND（Table_A.col7 ='type_A'）
）
）* 100
）-100
） ，2
）
）
，
（
ROUND
（
（
（
（
（
SELECT CONVERT（FLOAT，SUM（col5））
FROM Table_A
WHERE Table_A.col6 = 201703 AND（Table_A.col7 ='type_A'）
）
/
（
SELECT CONVERT（FLOAT，SUM（col5））
FROM Table_A
WHERE Table_A.col6 =（201703 - 1）AND（Table_A.col7 ='type_A' ）
）
）* 100
）-100
），2
）
）
，
NULL
，
'type_A'
）
但此代码仅显示第7列中的一种类型。
解决方案
尝试以下代码
SELECT COL1 ，COL2，COL3，COL4，COL5，COL6，COL7 FR OM TABLE_A WHERE COL7 = ' TYPE_A' AND COL6 = ' 201703'
UNION ALL
SELECT ' 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 CALCULATION
SELECT 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 < span class =code-keyword> AS FLOAT ））* 100）-100） AS COL5，
A.COL6 AS COL6，A.COL7 AS COL7
FROM
（ 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_AGROUP BY COL6，COL7 HAVING COL6 = < span class =code-string>' 201702' AND COL7 = ' TYPE_A'） AS B
UNION ALL
SELECT COL1，COL2，COL3，COL4，COL5，COL6，COL7 FROM TABLE_A WHERE COL7 = ' TYPE_B' AND COL6 = ' 201703'
UNION ALL
SELECT ' 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 ALL
SELECT 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.COL4PREVAS FLOAT ））* 100）-100） AS COL4，
（（（A.COL5 / CAST（B.COL5PREV AS FLOAT ））* 100）-100） AS COL5，
A.COL6 AS COL6，A .COL7 AS COL7
FROM
（ 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' < span class =code-keyword> 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 B
您想要的输出通过以下方式实现SQL查询..
- 准备样本数据
创建表#Table_A（col1 varchar（50），col2数字（18,2），col3数字（18,2），col4数字（18,2），col5数字（18,2），col6 varchar（10），col7 varchar（50））
插入#Table_A
值（'item_1'，200,120,300,40，'201702'，'type_A'）
插入#Table_A
值（'item_2'，210,320,340,10，'201702'，'type_A'）
insert成#Table_A
值（'item_1'，150,30,70,38，'201703'，'type_A'）
insert到#Table_A
值（'item_2'，315,220,120,40，'201703'，'type_A'）
插入#Table_A
值（'item_3'，40,500,110,35，'201702'，'type_B'）
插入#Table_A
值（'item_4'，758,78,152,61，'201702'，'type_B'）
插入#Table_A
值（'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, col7 FROM Table_A INNER JOIN Table_B ON Table_A.col1 = Table_B.col1 WHERE 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' FROM Table_A INNER JOIN Table_B ON Table_A.col1 = Table_B.col1 WHERE Table_A.col6 = 201703 AND (Table_A.col7 = 'type_A') ) --sum of data with 'g' UNION ALL ( SELECT N'Rate', ( ROUND ( ( ( ( ( SELECT CONVERT (FLOAT,SUM(col2)) FROM Table_A WHERE Table_A.col6 = 201703 AND (Table_A.col7 = 'type_A') ) / ( SELECT CONVERT (FLOAT,SUM(col2)) FROM Table_A WHERE Table_A.col6 = (201703 - 1) AND (Table_A.col7 = 'type_A') ) )*100 )-100 ),2 ) ) , ( ROUND ( ( ( ( ( SELECT CONVERT (FLOAT,SUM(col3)) FROM Table_A WHERE Table_A.col6 = 201703 AND (Table_A.col7 = 'type_A') ) / ( SELECT CONVERT (FLOAT,SUM(col3)) FROM Table_A WHERE Table_A.col6 = (201703 - 1) AND (Table_A.col7 = 'type_A') ) )*100 )-100 ),2 ) ) , ( ROUND ( ( ( ( ( SELECT CONVERT (FLOAT,SUM(col4)) FROM Table_A WHERE Table_A.col6 = 201703 AND (Table_A.col7 = 'type_A') ) / ( SELECT CONVERT (FLOAT,SUM(col4)) FROM Table_A WHERE Table_A.col6 = (201703 - 1) AND (Table_A.col7 = 'type_A') ) )*100 )-100 ),2 ) ) , ( ROUND ( ( ( ( ( SELECT CONVERT (FLOAT,SUM(col5)) FROM Table_A WHERE Table_A.col6 = 201703 AND (Table_A.col7 = 'type_A') ) / ( SELECT CONVERT (FLOAT,SUM(col5)) FROM Table_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 code
SELECT COL1,COL2,COL3 ,COL4 ,COL5 ,COL6 ,COL7 FROM TABLE_A WHERE COL7='TYPE_A' AND COL6 = '201703' UNION ALL SELECT '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 CALCULATION SELECT 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 COL7 FROM (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 B UNION ALL SELECT COL1,COL2,COL3 ,COL4 ,COL5 ,COL6 ,COL7 FROM TABLE_A WHERE COL7='TYPE_B' AND COL6 = '201703' UNION ALL SELECT '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 ALL SELECT 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 COL7 FROM (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 B
The output you want is achieved by below SQL query..
--Prepare sample data
create 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_A
values('item_1',200,120,300,40,'201702','type_A')
insert into #Table_A
values('item_2',210,320,340,10,'201702','type_A')
insert into #Table_A
values('item_1',150,30 ,70 ,38 ,'201703','type_A')
insert into #Table_A
values('item_2',315,220,120,40 ,'201703','type_A')
insert into #Table_A
values('item_3',40 ,500,110,35 ,'201702','type_B')
insert into #Table_A
values('item_4',758,78 ,152,61 ,'201702','type_B')
insert into #Table_A
values('item_3',778,920,12 ,330,'201703','type_B')
insert into #Table_A
values('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=@Month
union all
select 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,col7
union all
select 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查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
