mysql—如何通过sql从查询组中获取结果而不返回重复

os8fio9y  于 2021-06-20  发布在  Mysql
关注(0)|答案(3)|浏览(373)

我有3个表将连接到1个表中。
表1

  1. id|prdct_name|qty
  2. 001 A 5
  3. 002 B 5
  4. 003 C 5

表2

  1. id|prdct_id|price
  2. 1 001 100
  3. 2 001 200
  4. 3 002 150
  5. 4 002 250

表3

  1. id|prdct_id|stock_in|stock_out
  2. 1 001 5 0
  3. 2 001 10 0
  4. 3 002 15 0
  5. 4 002 25 0

我尝试过这个sql(由于输入错误而更新)

  1. select a.prdct_name as Name, a.qty as QTY,b.price as PRICE, c.stock_in as SIN,c.stock_out as SOUT
  2. from table1 a
  3. left join table2 b on a.id=b.prdct_id
  4. left join table3 c on a.id=c.prdct_id
  5. where 1=1 group by b.id,c.id

但结果像这个表一样重复

  1. NAME|QTY|PRICE|SIN|SOUT
  2. A 5 100 5 0
  3. A 5 100 10 0
  4. A 5 200 5 0
  5. A 5 200 10 0
  6. B 5 150 15 0
  7. B 5 150 25 0
  8. B 5 250 15 0
  9. B 5 250 25 0

结果应该是

  1. NAME|QTY|PRICE|SIN|SOUT
  2. A 5 100 5 0
  3. A 5 200 10 0
  4. B 5 150 15 0
  5. B 5 250 25 0

有没有办法消除重复问题?尝试使用distinct也无济于事。谢谢

uttx8gqw

uttx8gqw1#

更改您的join key it should id=product\u id,但您正在尝试使用name&product\u id

  1. select a.prdct_name as Name, a.qty as QTY,b.price as PRICE, c.stock_in as SIN,c.stock_out as SOUT
  2. from table1 a
  3. left join table2 b on a.id=b.prdct_id
  4. left join table3 c on a.id=c.prdct_id
  5. where 1=1 group by b.id,c.id
deyfvvtc

deyfvvtc2#

看起来是你的第二个 join 应该打开 id --还有你的 join 反正情况看起来不太好。

  1. select a.prdct_name as Name, a.qty as QTY, b.price as PRICE, c.stock_in as SIN,c.stock_out as SOUT
  2. from table1 a left join
  3. table2 b
  4. on a.id = b.prdct_id left join
  5. table3 c
  6. on a.id = c.id
  7. where 1=1
  8. group by b.id, c.id
68de4m5k

68de4m5k3#

您可以使用sql关键字 DISTINCT 选择不重复的行。
例子:

  1. SELECT DISTINCT
  2. prdct_name as NAME,
  3. qty as QTY,
  4. price as PRICE,
  5. stock_in as SIN,
  6. stock_out as SOUT
  7. FROM
  8. table1 a
  9. INNER JOIN
  10. table2 b ON a.id=b.prdct_id
  11. LEFT JOIN
  12. table3 c ON b.id=c.id
  13. GROUP BY
  14. a.id, c.id

工作sql fiddle
更多关于distincthere的信息。

展开查看全部

相关问题