mysql 连接 2 个表 - 显示一张表中的所有行

发布于 2024-11-08 20:26:57 字数 486 浏览 0 评论 0原文

我有两张表，如下所示：表“a”包含：

id|name

stock1|fullname
stock2|fullname2
stock3|fullname3

表“b”包含给定库存的产品数量。

id|stock_id|quantity|product_id|
1|stock1|3|13
2|stock3|4|13
3|stock1|1|5
4|stock2|2|2

现在我需要合并这两个表，以便每个产品从表“a”中获取其库存全名，如果没有给出库存数量，它仍然会显示数量为 0 的行。

所以从我的例如，product_id 13 将显示为：

stock|quanitity|product_id|stock_fullname
stock1|3|13|fullname1
stock2|0|13|fullname2
stock3|4|13|fullname3

原文

I have two tables, like so:
table "a" contains:

id|name

stock1|fullname
stock2|fullname2
stock3|fullname3

table "b" contains product quantities for given stock.

id|stock_id|quantity|product_id|
1|stock1|3|13
2|stock3|4|13
3|stock1|1|5
4|stock2|2|2

Now I would need to combine those two tables, so that each product takes its stock full name from table "a", and if its quanitity is not given for stock, it would still show the row with the quanitity as 0.

So from my example, product_id 13 would show as:

stock|quanitity|product_id|stock_fullname
stock1|3|13|fullname1
stock2|0|13|fullname2
stock3|4|13|fullname3

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

无敌元气妹 2024-11-15 20:26:57

您应该能够使用 LEFT JOIN 来实现此目的。

SELECT a.id AS stock, COALESCE(b.quanitity,0), b.product_id, a.name AS stock_fullname
FROM a
LEFT JOIN b
  ON a.id = b.stock_id
  AND b.product_id = 13

You should be able to use a LEFT JOIN to achieve this.

SELECT a.id AS stock, COALESCE(b.quanitity,0), b.product_id, a.name AS stock_fullname
FROM a
LEFT JOIN b
  ON a.id = b.stock_id
  AND b.product_id = 13

回复收藏 0 原文

爱的故事 2024-11-15 20:26:57

听起来您需要使用 LEFT JOIN，尽管记录任何数量都可能显示为 NULL 而不是零。像这样的东西：

SELECT a.*, b.* 
FROM table_a a
    LEFT JOIN table_b b ON a.stock_id = b.stock_id

It sounds like you need to use a LEFT JOIN, although the records with no quantity might show as NULL rather than zero. Something like:

SELECT a.*, b.* 
FROM table_a a
    LEFT JOIN table_b b ON a.stock_id = b.stock_id

回复收藏 0 原文

十六岁半 2024-11-15 20:26:57

试试这个：

SELECT stock,COALESCE(quanitity,0),product_id,stock_fullname FROM stock JOIN product

try this:

SELECT stock,COALESCE(quanitity,0),product_id,stock_fullname FROM stock JOIN product

回复收藏 0 原文

濫情▎り 2024-11-15 20:26:57

您需要一个外连接，以便仍考虑 a 表中没有 b 中对应行的行。相比之下，内部联接坚持认为您有匹配的行。如果从没有行的表中提取值，则会得到 NULL。数据库之间的语法有所不同，并且根据是左侧表还是右侧表获取假行来进行区分。

有关语法，请参阅其他答案。

回复收藏 0 原文

淡淡の花香 2024-11-15 20:26:57

我认为这个查询应该适用于您的示例：

SELECT a.id stock if(b.quantity IS NULL, 0, b.quantity), 
       b.product_id, a.name stock_fullname
FROM b
LEFT JOIN a b.stock = a.id
WHERE b.product_id = 13;

I think this query should work for your example:

SELECT a.id stock if(b.quantity IS NULL, 0, b.quantity), 
       b.product_id, a.name stock_fullname
FROM b
LEFT JOIN a b.stock = a.id
WHERE b.product_id = 13;

回复收藏 0 原文

情独悲 2024-11-15 20:26:57

您应该能够使用LEFT JOIN来实现这一点。

SELECT a.id AS stock, COALESCE(b.quanitity,0), b.product_id, a.name AS stock_fullname
FROM a
LEFT JOIN b
  ON a.id = b.stock_id
  AND b.product_id = 13

You should be able to use a LEFT JOIN to achieve this.

SELECT a.id AS stock, COALESCE(b.quanitity,0), b.product_id, a.name AS stock_fullname
FROM a
LEFT JOIN b
  ON a.id = b.stock_id
  AND b.product_id = 13

回复收藏 0 原文

~没有更多了~