我有一个Excel电子表格,它从SQL中提取记录。我想合并重复记录,并对匹配记录的qty字段求和。 我尝试在现有查询中添加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
我希望它看起来像这样:
item no.| item description | qty | date
1 red onion 15 20110405
2 yellow onion 5 20110406
这是我使用的查询语句:
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
item_no | item_desc_1 | qty_ordered |unit_weight |shipping_dt |inv_d
,然后我在Excel中添加一个字段,将SQL日期转换为Excel可以识别的格式。之前,我尝试使用Sum和Group By,像这样:SELECT OELINHST_SQL.item_no,OELINHST_SQL.item_desc_1, (SELECT sum(OELINHST_SQL.qty_ordered) FROM OELINHST_SQL group by OELINHST_SQL.item_no) FROM OELINHST_SQL
但是这给了我一个“子查询不能返回多个值”的错误。 - NX5