合并重复记录并求和 QTY 的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7205679/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query to combine duplicate records and sum QTY
提问by NX5
I have an excel spreadsheet which pulls records from SQL. I want to combine the duplicate records and sum the qty field of matching records. I tried adding a select(sum) and group by command to my existing query but I didn't get very far as I have no real SQL experience. My table looks like this:
我有一个从 SQL 中提取记录的 Excel 电子表格。我想合并重复记录并对匹配记录的数量字段求和。我尝试将 select(sum) 和 group by 命令添加到我现有的查询中，但由于没有真正的 SQL 经验，所以我没有得到很远。我的桌子看起来像这样：
item no.| item description | qty | date
1 red onion 5 20110405
2 yellow onion 5 20110406
1 red onion 10 20110405
and I want it to look like this:
我希望它看起来像这样：
item no.| item description | qty | date
1 red onion 15 20110405
2 yellow onion 5 20110406
This is the query I use:
这是我使用的查询：
SELECT
OELINHST_SQL.item_no, OELINHST_SQL.item_desc_1, OELINHST_SQL.qty_ordered, oelinhst_sql.unit_weight, OEHDRHST_SQL.shipping_dt, OEHDRHST_SQL.inv_dt
FROM
OEHDRHST_SQL OEHDRHST_SQL,
OELINHST_SQL OELINHST_SQL
WHERE
OEHDRHST_SQL.ord_type *= OELINHST_SQL.ord_type AND
OEHDRHST_SQL.ord_no *= OELINHST_SQL.ord_no AND
(OELINHST_SQL.prod_cat <> '26' AND
OELINHST_SQL.prod_cat <> '25') AND
OELINHST_SQL.loc = 'fs2' AND
OELINHST_SQL.item_desc_1 IS NOT NULL AND
OEHDRHST_SQL.shipping_dt >= 20110101 AND
OELINHST_SQL.item_no NOT IN ('800-505-00', '800-083-00', '800-506-00', '400-511-39') AND (OELINHST_SQL.item_no NOT BETWEEN '800-000-00' AND '999-999-99')
ORDER BY
OELINHST_SQL.item_no ASC,
OEHDRHST_SQL.inv_dt DESC
回答by Derek Kromm
You need to use a GROUP BYclause and SUMthe relevant column.
您需要使用GROUP BY子句和SUM相关列。
Here's a simplified example that can be adapted to your situation.
这是一个可以根据您的情况进行调整的简化示例。
select t1.name, t2.name, t2.date, sum(t2.orders)
from table1 t1
inner join table2 t2 on t1.id = t2.t1_id
group by t1.name, t2.name, t2.date
回答by Jim B
Looks like you need to add a group by clause, and add in SUM(Qty) and MIN(date)
看起来您需要添加 group by 子句，并添加 SUM(Qty) 和 MIN(date)
Not too sure about your column names, but probably something like this:
不太确定你的列名，但可能是这样的：
SELECT
OELINHST_SQL.item_no, OELINHST_SQL.item_desc_1, SUM(OELINHST_SQL.qty_ordered), oelinhst_sql.unit_weight, MIN(OEHDRHST_SQL.shipping_dt), MIN(OEHDRHST_SQL.inv_dt)
FROM
OEHDRHST_SQL OEHDRHST_SQL,
OELINHST_SQL OELINHST_SQL
WHERE
OEHDRHST_SQL.ord_type *= OELINHST_SQL.ord_type AND
OEHDRHST_SQL.ord_no *= OELINHST_SQL.ord_no AND
(OELINHST_SQL.prod_cat <> '26' AND
OELINHST_SQL.prod_cat <> '25') AND
OELINHST_SQL.loc = 'fs2' AND
OELINHST_SQL.item_desc_1 IS NOT NULL AND
OEHDRHST_SQL.shipping_dt >= 20110101 AND
OELINHST_SQL.item_no NOT IN ('800-505-00', '800-083-00', '800-506-00', '400-511-39') AND (OELINHST_SQL.item_no NOT BETWEEN '800-000-00' AND '999-999-99')
GROUP BY OELINHST_SQL.item_no, OELINHST_SQL.item_desc_1, oelinhst_sql.unit_weight
ORDER BY
OELINHST_SQL.item_no ASC,
OEHDRHST_SQL.inv_dt DESC
回答by PaulStock
I think you want something like this:
我想你想要这样的东西：
SELECT
OELINHST_SQL.item_no,
OELINHST_SQL.item_desc_1,
SUM(OELINHST_SQL.qty_ordered),
SUM(oelinhst_sql.unit_weight),
MIN(OEHDRHST_SQL.shipping_dt),
MIN(OEHDRHST_SQL.inv_dt)
FROM
OEHDRHST_SQL OEHDRHST_SQL,
OELINHST_SQL OELINHST_SQL
WHERE
OEHDRHST_SQL.ord_type *= OELINHST_SQL.ord_type AND
OEHDRHST_SQL.ord_no *= OELINHST_SQL.ord_no AND
(OELINHST_SQL.prod_cat <> '26' AND
OELINHST_SQL.prod_cat <> '25') AND
OELINHST_SQL.loc = 'fs2' AND
OELINHST_SQL.item_desc_1 IS NOT NULL AND
OEHDRHST_SQL.shipping_dt >= 20110101 AND
OELINHST_SQL.item_no NOT IN ('800-505-00', '800-083-00', '800-506-00', '400-511-39') AND (OELINHST_SQL.item_no NOT BETWEEN '800-000-00' AND '999-999-99')
GROUP BY
OELINHST_SQL.item_no,
OELINHST_SQL.item_desc_1
ORDER BY
OELINHST_SQL.item_no ASC,
OEHDRHST_SQL.inv_dt DESC
But, this assumes that shipping date and int_date will always be the same or that you want the earliest dates in both cases.
但是，这假设发货日期和 int_date 将始终相同，或者在这两种情况下您都需要最早的日期。
回答by Rob Boek
I added simpler table aliases to make it easier to read. Basically, you need to GROUP BY all columns that aren't included in an aggregate function.
我添加了更简单的表别名以使其更易于阅读。基本上，您需要对未包含在聚合函数中的所有列进行 GROUP BY。
SELECT
l.item_no
,l.item_desc_1
,SUM(l.qty_ordered) AS qty_ordered
,l.unit_weight
,h.shipping_dt
,h.inv_dt
FROM OEHDRHST_SQL h
LEFT OUTER JOIN OELINHST_SQL l ON h.ord_type = l.ord_type AND h.ord_no = l.ord_no
WHERE l.prod_cat <> '26'
AND l.prod_cat <> '25'
AND l.loc = 'fs2'
AND l.item_desc_1 IS NOT NULL
AND h.shipping_dt >= 20110101
AND l.item_no NOT IN ('800-505-00', '800-083-00', '800-506-00', '400-511-39')
AND l.item_no NOT BETWEEN '800-000-00' AND '999-999-99'
GROUP BY l.item_no, l.item_desc_1, l.unit_weight, h.shipping_dt, h.inv_dt
ORDER BY l.item_no ASC, h.inv_dt DESC
