SQL:对一个查询中的两个select语句中的列执行计算

I want to select information from two unrelated SQL tables (formed by separate SQL queries) to give me one table with four columns: one as an identifier, one for values from the first table, one for values from the second table, and one for a calculation between them.

我想要从两个不相关的SQL表(由独立的SQL查询组成)中选择信息，以便为我提供一个包含四列的表:一个作为标识符，一个用于第一个表中的值，一个用于第二个表中的值，还有一个用于它们之间的计算。

A simplified example is:

一个简化的例子是:

tblSold

 Shop name | items sold
 Shop A    | 100   
 Shop B    | 50    
 Shop C    | 75    
 Shop D    | 80

tblReturned

  Shop name | Items returned
  Shop A    | 10
  Shop B    | 5   
  Shop C    | 7 
  Shop D    | 8

And i'm trying to get a table that looks like this

我想要一个像这样的表格

  Shop name | items sold | items returned | net (sold - returned)
  Shop A    | 100        | 10             | 90
  Shop B    | 50         | 5              | 45
  Shop C    | 75         | 7              | 68
  Shop D    | 80         | 8              | 72

In the above case, tblSold comes from a query that looks like this:

在上面的例子中，tblSold来自如下查询:

SELECT global.shopname, COUNT(stock.sold) 
FROM global
INNER JOIN  stock ON global.id = stock.shop
GROUP BY shopname

And tblReturned comes from a query that looks like this:

tblreturn来自如下查询:

SELECT global.shopname, COUNT(stock.returned) 
FROM global
INNER JOIN  stock ON global.id = stock.shop
WHERE stock.datereturned > 1.01.2010
GROUP BY shopname

From this previous question SQL: Two select statements in one query I got the following query:

从前面的问题SQL:一个查询中的两个select语句，我得到了下面的查询:

(SELECT global.shopname, COUNT(stock.sold) 
FROM global
INNER JOIN  stock ON global.id = stock.shop
GROUP BY shopname)

UNION

(SELECT global.shopname, COUNT(stock.returned) 
FROM global
INNER JOIN  stock ON global.id = stock.shop
WHERE stock.datereturned > 1.01.2010
GROUP BY shopname)

Which got me a table with only two columns, and no way to make the calculation. Is there a way to join these in some manner to make it work?

这样我得到了一个只有两列的表，而且没有办法计算。是否有办法以某种方式加入它们以使其工作?

3 个解决方案

#1

I think you just need conditional aggregation:

我认为你只需要条件聚合

SELECT g.shopname, COUNT(s.sold),
       SUM(CASE WHEN s.datereturned > '2010-01-01' AND s.returned IS NOT NULL THEN 1 ELSE 0 END) as returned,
       (COUNT(s.sold) - 
        SUM(CASE WHEN s.datereturned > '2010-01-01' AND s.returned IS NOT NULL THEN 1 ELSE 0 END)
       ) as net
FROM global INNER JOIN
     stock
     ON g.id = s.shop
GROUP BY g.shopname;

Having the date logic only apply to returns is strange. Also, I am guessing, though, that you want SUM() of the inventory and not COUNT(). So this may produce more accurate results:

将日期逻辑只应用于返回是很奇怪的。而且，我猜，您需要的是库存的SUM()而不是COUNT()。所以这可能会产生更准确的结果:

SELECT g.shopname, SUM(s.sold) as items_sold,
       SUM(CASE WHEN s.datereturned > '2010-01-01' THEN s.returned ELSE 0 END) as items_returned,
       SUM(CASE WHEN s.datereturned > '2010-01-01' THEN s.items_sold - s.returned
                ELSE s.items_sold END)
FROM global INNER JOIN
     stock
     ON g.id = s.shop
GROUP BY g.shopname;

#2

SELECT t1.shopname,
       t1.items_sold,
       t2.items_returned,(t1.items_sold-t2.items_returned) AS NET 
FROM (SELECT global.shopname, COUNT(stock.sold) as items_sold
      FROM global
      INNER JOIN  stock ON global.id = stock.shop
      GROUP BY shopname) t1
INNER JOIN  (SELECT global.shopname, COUNT(stock.returned) as items_returned
             FROM global
             INNER JOIN  stock ON global.id = stock.shop
             WHERE stock.datereturned > '2010-01-01'
             GROUP BY shopname) t2
ON t1.shopname = t2.shopname

#3

You can use CTE:

您可以使用CTE:

WITH tblSold AS (
    SELECT
      global.shopname,
      COUNT(stock.sold) AS sold
    FROM global
      INNER JOIN stock ON global.id = stock.shop
    GROUP BY shopname),
    tblReturned AS (
      SELECT
        global.shopname,
        COUNT(stock.returned) AS returned
      FROM global
        INNER JOIN stock ON global.id = stock.shop
      WHERE stock.datereturned > '1.01.2010'
      GROUP BY shopname)
SELECT
  shopname,
  sold,
  returned,
  sold - returned
FROM tblSold
  INNER JOIN tblReturned USING (shopname);

#1