使用算术表达式创建列的 sql 查询 [英] sql query to create columns with arithmetic expressions

问题描述

我有我的 sql 表和查询，如下所示:

I have my sql tables and query as shown below :

CREATE TABLE #ABC([Year] INT, [Month] INT, Stores INT);
CREATE TABLE #DEF([Year] INT, [Month] INT, SalesStores INT);
CREATE TABLE #GHI([Year] INT, [Month] INT, Products INT);

INSERT #ABC VALUES (2013,1,1);
INSERT #ABC VALUES (2013,1,2);
INSERT #ABC VALUES (2013,2,3);

INSERT #DEF VALUES (2013,1,4);
INSERT #DEF VALUES (2013,1,5);
INSERT #DEF VALUES (2013,2,6);

INSERT #GHI VALUES (2013,1,7);
INSERT #GHI VALUES (2013,1,8);
INSERT #GHI VALUES (2013,2,9);
INSERT #GHI VALUES (2013,3,10);

我当前的查询是

SELECT T.[Year],
       T.[Month]
       -- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
       ,
       (SELECT SUM(Stores)
        FROM   #ABC
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]) AS [Sum_Stores],
       (SELECT SUM(SalesStores)
        FROM   #DEF
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]) AS [Sum_SalesStores],
       (SELECT SUM(Products)
        FROM   #GHI
        WHERE  [Year] = T.[Year]
               AND [Month] = T.[Month]) AS [Sum_Products]
FROM   (
       -- this selects a list of all possible dates.
       SELECT [Year],
              [Month]
       FROM   #ABC
       UNION
       SELECT [Year],
              [Month]
       FROM   #DEF
        UNION
        SELECT [Year],
               [Month]
        FROM   #GHI) AS T; 

哪个返回

+------+-------+------------+-----------------+--------------+
| Year | Month | Sum_Stores | Sum_SalesStores | Sum_Products |
+------+-------+------------+-----------------+--------------+
| 2013 |     1 | 3          | 9               |           15 |
| 2013 |     2 | 3          | 6               |            9 |
| 2013 |     3 | NULL       | NULL            |           10 |
+------+-------+------------+-----------------+--------------+

我想要做的是在我的查询中再添加两列，显示

What I want to do is to add two more columns to my query which shows

Sum_SalesStores/Sum_Products &Sum_SalesStores/Sum_Stores per每月，然后根据这两个表达式对查询进行排序.谁能告诉我这怎么可能?

Sum_SalesStores/Sum_Products & Sum_SalesStores/Sum_Stores per each month and then sort the query based on the two expressions. Can anyone tell me how its possible ?

推荐答案

一种方法是将整个现有查询放入 CTE，然后您可以从中选择并执行计算.

One way would just be to chuck your entire existing query into a CTE then you can select from that and perform the calculations.

;WITH CTE
     AS (
        /*Paste your existing query*/
        )
SELECT *,
       Sum_SalesStores / Sum_Products AS Foo,
       Sum_SalesStores / Sum_Stores   AS Bar
FROM   CTE
ORDER  BY Foo,
          Bar 

这篇关于使用算术表达式创建列的 sql 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！