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

uwopmtnx 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(204)

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

id|prdct_name|qty
001     A      5
002     B      5
003     C      5

表2

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

表3

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

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

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

但结果像这个表一样重复

NAME|QTY|PRICE|SIN|SOUT
 A    5   100   5    0
 A    5   100   10   0
 A    5   200   5    0
 A    5   200   10   0
 B    5   150   15   0
 B    5   150   25   0
 B    5   250   15   0
 B    5   250   25   0

结果应该是

NAME|QTY|PRICE|SIN|SOUT
 A    5   100   5    0
 A    5   200   10   0
 B    5   150   15   0 
 B    5   250   25   0

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

sql mysql duplicates group-by left-join

来源：https://stackoverflow.com/questions/52039119/how-to-get-result-from-query-group-by-sql-without-return-duplicate

3条答案

按热度按时间

aamkag611#

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

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

赞(0）回复(0）举报 2021-06-21

roqulrg32#

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

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

赞(0）回复(0）举报 2021-06-20

gojuced73#

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

SELECT DISTINCT
  prdct_name as NAME,
  qty as QTY,
  price as PRICE,
  stock_in as SIN,
  stock_out as SOUT
FROM
  table1 a
INNER JOIN
  table2 b ON a.id=b.prdct_id
LEFT JOIN
  table3 c ON b.id=c.id
GROUP BY
  a.id, c.id

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

赞(0）回复(0）举报 2021-06-20

我来回答

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

3条答案

相关问题

热门标签

最新问答