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
tblSold
Shop name | items sold
Shop A | 100
Shop B | 50
Shop C | 75
Shop D | 80
tblReturned
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
4
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
0
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
0
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
4
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
0
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
0
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);