如何结合两个SQL查询? [英] How to combine two sql queries?

问题描述

我有一个库存表，我想创建一个报告来显示订购商品的频率.

I have a stock table and I would like to create a report that will show how often were items ordered.

库存"表:

item_id |  pcs | operation
apples  |  100 | order
oranges |   50 | order
apples  | -100 | delivery
pears   |  100 | order
oranges |  -40 | delivery
apples  |   50 | order
apples  |   50 | delivery

基本上，我需要将这两个查询结合在一起.

Basically I need to join these two queries together.

打印库存余额的查询:

SELECT stock.item_id, Sum(stock.pcs) AS stock_balance
FROM stock
GROUP BY stock.item_id;

打印销售统计信息的查询

A query which prints sales statistics

SELECT stock.item_id, Sum(stock.pcs) AS pcs_ordered, Count(stock.item_id) AS number_of_orders
FROM stock
GROUP BY stock.item_id, stock.operation
HAVING stock.operation="order";

我认为某种JOIN可以完成这项工作，但我不知道如何将查询粘合在一起.

I think that some sort of JOIN would do the job but I have no idea how to glue queries together.

所需的输出:

item_id | stock_balance | pcs_ordered | number_of_orders
apples  |             0 |         150 |                2
oranges |            10 |          50 |                1
pears   |           100 |         100 |                1

这只是示例.也许我将需要添加更多条件，因为有更多列.是否存在将多个查询组合在一起的通用技术?

This is just example. Maybe, I will need to add more conditions because there is more columns. Is there a universal technique of combining multiple queries together?

推荐答案

SELECT a.item_id, a.stock_balance, b.pcs_ordered, b.number_of_orders
FROM
    (SELECT stock.item_id, Sum(stock.pcs) AS stock_balance 
    FROM stock 
    GROUP BY stock.item_id) a
LEFT OUTER JOIN
    (SELECT stock.item_id, Sum(stock.pcs) AS pcs_ordered, 
            Count(stock.item_id) AS number_of_orders 
    FROM stock
    WHERE stock.operation = "order"
    GROUP BY stock.item_id) b
ON a.item_id = b.item_id

这篇关于如何结合两个SQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！