我有 2 个表，一个是buy_table，另一个是sale_table。 在此查询中，我将不得不使用 SUM，分组依据和日期，但我无法理解，它显示 som 错误
//buy table
----------------------------------------------------------
| id | product_id | quantity | total_price | date |
----------------------------------------------------------
| 1 | 1 | 5 | 500 | 2014-12-05 |
----------------------------------------------------------
| 2 | 2 | 5 | 500 | 2014-12-15 |
----------------------------------------------------------
| 3 | 1 | 5 | 500 | 2014-12-01 |
//Sale_table
----------------------------------------------------------
| id | product_id | quantity | total_price | date |
----------------------------------------------------------
| 1 | 1 | 5 | 1800 | 2014-12-05 |
----------------------------------------------------------
| 2 | 2 | 5 | 500 | 2014-12-15 |
----------------------------------------------------------
| 3 | 3 | 5 | 500 | 2015-01-01 |
----------------------------------------------------------
| 4 | 3 | 5 | 500 | 2015-01-01 |
我需要这样。
----------------------------------------------------------------------
| id | product_id |buy_quantity| buy_total | sale_quantity|sale_total
----------------------------------------------------------------------
| 1 | 1 | 10 | 1000 | 5 | 1800
----------------------------------------------------------------------
| 2 | 2 | 5 | 500 | 5 | 500
----------------------------------------------------------------------
| 3 | 3 | 5 | 500 | 10 | 1000
----------------------------------------------------------------------
SELECT
A.ID,A.PRODUCT_ID,A.BUY_QUANTITY,B.SALE_QUANTITY,A.BUY_TOTAL,B.SALE_TOTAL,A.DATE
(select id,product_id,sum(quantity) as buy_quantity,sum(total_price) as
buy_total,date from buy_table group by product_id) A INNER JOIN (SELECT sum
(quantity) as SALE_quantity,sum(total_price) as SALE_total FROM SALE_TABLE group by product_id) B
ON A.PRODUCT_ID=B.PRODUCT_ID
我假设您在购买表中输入了错误的第三行，并且您打算在product_id中键入 3，并且所需结果集中的id列只是一个与任何数据无关的序列。以下查询可能是您查询的答案：
-- Simluating your tables
DECLARE @buyTable AS TABLE (id INT, product_id INT, quantity INT, total_price INT, date DATE)
DECLARE @saleTable AS TABLE (id INT, product_id INT, quantity INT, total_price INT, date DATE)
INSERT INTO @buyTable
VALUES(1, 1, 5, 500, '2014-12-05'), (2, 2, 5, 500, '2014-12-15'), (3, 3, 5, 500, '2014-12-01')
INSERT INTO @saleTable
VALUES(1, 1, 5, 1800, '2014-12-05'), (2, 2, 5, 500, '2014-12-15'), (3, 3, 5, 500, '2014-01-01'), (4, 3, 5, 500, '2015-01-01')
-- Simluation stops here
SELECT ROW_NUMBER() OVER(ORDER BY b.product_id) AS id, b.product_id, buy_quantity, buy_total, sale_quantity, sale_total FROM
(
SELECT product_id, SUM(quantity) AS buy_quantity, SUM(total_price) AS buy_total FROM @buyTable
GROUP BY product_id
) b
INNER JOIN
(
SELECT product_id, SUM(quantity) AS sale_quantity, SUM(total_price) AS sale_total FROM @saleTable
GROUP BY product_id
) s ON s.product_id = b.product_id
如果您想按产品显示购买/销售，则结果中的 id 列没有意义。
为了确保您始终获得所有产品，您需要使用您的产品表作为驱动表（假设您有一个产品表）
select
p.product_id,
(SELECT SUM(b.quantity) FROM buy b WHERE b.product_id = p.product_id) as buy_quantity,
(SELECT SUM(b.total_price) FROM buy b WHERE b.product_id = p.product_id) as buy_total,
(SELECT SUM(s.quantity) FROM sale s WHERE s.product_id = p.product_id) as sale_quantity,
(SELECT SUM(s.total_price) FROM sale s WHERE s.product_id = p.product_id) as sale_total
from product p