如何对一系列项执行SQL查询？

My database has a main table (ex. Table_A). Column 1 contains the 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.

我的数据库有一个主表(例如Table_A)。第1列包含项目的名称,第6列是我的数据行的日期。第7列是第1列的项目类型,它们是从A到z。

For example (some rows of Table_A):

例如(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  |

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)

我希望从特定日期(ex.201703)显示第7列中每种类型的项目,然后在第2列的总和,第3列的总和,第4列的总和,第5列的总和之下,然后低于第5列的总和。在函数内计算的增长(((月的总和/上个月的总和)* 100)-100)

For the above example, the result I expect is this:

对于上面的例子,我期望的结果如下:

|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  |

I have tried this code:

我试过这段代码:

(
    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

但此代码仅显示第7列中的一种类型

2 个解决方案

#1

well.. i think this is what you are trying to do but i am not sure.. there are bits you will need to tweak (like i am not sure how those dates are stored - i am guessing text?):

好吧..我认为这是你想要做的但是我不确定..有些位你需要调整(就像我不确定那些日期是如何存储的 - 我猜文字?):

select 
    item
    ,co11
    ,col2
    ,col3
    ,col4
    ,date
    ,type
from 
    table_a

union

select 
    "sum" as item
    ,sum(col1) as sc1
    ,sum(col2) as sc2
    ,sum(col3) as sc3
    ,sum(col4) as sc4
    ,date
    ,type
from 
    table_a
group by
    type
    ,date

union

select 
    "rate" as item
    ,round(((c.sc1-p.sc1)*100)-100,2)
    ,round(((c.sc2-p.sc2)*100)-100,2)
    ,round(((c.sc3-p.sc3)*100)-100,2)
    ,round(((c.sc4-p.sc4)*100)-100,2)
    ,date
    ,type
from 
    (
        select 
            ,sum(col1) as sc1
            ,sum(col2) as sc2
            ,sum(col3) as sc3
            ,sum(col4) as sc4
            ,date
            ,type
        from 
            table_a
        group by
            type
            ,date
    ) as c
    left outer join (
        select 
            ,sum(col1) as sc1
            ,sum(col2) as sc2
            ,sum(col3) as sc3
            ,sum(col4) as sc4
            ,date
            ,type
        from 
            table_a
        group by
            type
            ,date
    ) as p
        on c.type = p.type
        and (1,cast(c.date as int)) = (1,cast(p.date as int))+1

Also it is a bit weird to do this in the DB.. it would normally be easier to do with with two or three queries and put the data together elsewhere - especially as you have different things in each column and you will probably have to wrap the whole thing in another selection to get the ordering that would make this work:

在DB中执行此操作也有点奇怪..通常更容易使用两个或三个查询并将数据放在其他地方 - 特别是因为每列中有不同的东西而你可能需要换行在另一个选择中的整个事情,以获得使这项工作的顺序:

select 
    item
    ,co11
    ,col2
    ,col3
    ,col4
    ,date
    ,type
from(   
    select 
        item
        ,co11
        ,col2
        ,col3
        ,col4
        ,date
        ,type
        ,0 as sortSeq
    from 
        table_a

    union

    select 
        "sum" as item
        ,sum(col1) as sc1
        ,sum(col2) as sc2
        ,sum(col3) as sc3
        ,sum(col4) as sc4
        ,date
        ,type
        ,1 as sortSeq
    from 
        table_a
    group by
        type
        ,date

    union

    select 
        "rate" as item
        ,round(((c.sc1-p.sc1)*100)-100,2)
        ,round(((c.sc2-p.sc2)*100)-100,2)
        ,round(((c.sc3-p.sc3)*100)-100,2)
        ,round(((c.sc4-p.sc4)*100)-100,2)
        ,date
        ,type
        ,3 as sortSeq
    from 
        (
            select 
                ,sum(col1) as sc1
                ,sum(col2) as sc2
                ,sum(col3) as sc3
                ,sum(col4) as sc4
                ,date
                ,type
            from 
                table_a
            group by
                type
                ,date
        ) as c
        left outer join (
            select 
                ,sum(col1) as sc1
                ,sum(col2) as sc2
                ,sum(col3) as sc3
                ,sum(col4) as sc4
                ,date
                ,type
            from 
                table_a
            group by
                type
                ,date
        ) as p
            on c.type = p.type
            and (1,cast(c.date as int)) = (1,cast(p.date as int))+1
) as a
order by
    date
    ,type
    ,sortSeq
    ,item

#2

This is full working example. You can just add some ordering if you need:

这是完整的工作示例。如果需要,您可以添加一些订购:

DECLARE @DataSource TABLE
(
    [col1] VARCHAR(12)
   ,[col2] INT
   ,[col3] INT
   ,[col4] INT
   ,[col5] INT
   ,[col6] VARCHAR(12)
   ,[col7] VARCHAR(12)
);

INSERT INTO @DataSource ([col1], [col2], [col3], [col4], [col5], [col6], [col7])
VALUES ('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');

DECLARE @curr_month VARCHAR(12) = '201703'
       ,@prev_month VARCHAR(12) = '201702';

SELECT ISNULL([col1], 'sum')
      ,SUM([col2])
      ,SUM([col3])
      ,SUM([col4])
      ,SUM([col5])
      ,[col6]
      ,[col7]
FROM @DataSource
WHERE [col6] = @curr_month
GROUP BY GROUPING SETS
(
     ([col1], [col6], [col7])
    ,([col6], [col7])
)
UNION ALL
SELECT 'Rate'
      ,CAST((SUM(IIF([col6] = @curr_month, [col2], 0.0)) / SUM(IIF([col6] = @prev_month, [col2], 0.0)) * 100) - 100 AS DECIMAL(9,2))
      ,CAST((SUM(IIF([col6] = @curr_month, [col3], 0.0)) / SUM(IIF([col6] = @prev_month, [col3], 0.0)) * 100) - 100 AS DECIMAL(9,2))
      ,CAST((SUM(IIF([col6] = @curr_month, [col4], 0.0)) / SUM(IIF([col6] = @prev_month, [col4], 0.0)) * 100) - 100 AS DECIMAL(9,2))
      ,CAST((SUM(IIF([col6] = @curr_month, [col5], 0.0)) / SUM(IIF([col6] = @prev_month, [col5], 0.0)) * 100) - 100 AS DECIMAL(9,2))
      ,@curr_month
      ,[col7]
FROM @DataSource 
WHERE EXISTS(SELECT 1 FROM @DataSource DS WHERE DS.[col1] = [col1] AND [col6] = @curr_month)
GROUP BY [col7];

You can play around with the DECIMAL casting, too - currently, I am casting to DECIMAL(9,2) but you can change the query to match your needs.

您也可以使用DECIMAL强制转换 - 目前,我正在转换为DECIMAL(9,2)但您可以更改查询以满足您的需求。

Also, note I have added to variables to hold the current and the previous month. They can be populated with SQL, too but I hard-coded them.

另外,请注意我已添加到变量以保持当前和上个月。它们也可以用SQL填充,但我对它们进行了硬编码。

#1