gpt4 book ai didi

MySql 查询连接 3 个空行的表

转载 作者:太空宇宙 更新时间:2023-11-03 12:13:24 24 4
gpt4 key购买 nike

我想按用户 ID 列出数据库中的每个产品以及访问者跟踪和销售信息。

这是我目前得到的:
http://www.sqlfiddle.com/#!2/ec506/19

SELECT products.product_name,
tracking.unique_hits,
tracking.total_hits,
COUNT(sales.product_id) as total_sales,
SUM(sales.amount) as total_revenue
FROM products
INNER JOIN tracking ON products.id = tracking.product_id
INNER JOIN sales ON products.id = sales.product_id
WHERE products.vendor_id = 0;

它输出这个:

|   PRODUCT_NAME | UNIQUE_HITS | TOTAL_HITS | TOTAL_SALES | TOTAL_REVENUE |
|----------------|-------------|------------|-------------|---------------|
| test product 1 | 42 | 52 | 3 | 30 |

但我希望它也输出没有销售的产品,所以它应该输出这样的东西:

|   PRODUCT_NAME | UNIQUE_HITS | TOTAL_HITS | TOTAL_SALES | TOTAL_REVENUE |
|----------------|-------------|------------|-------------|---------------|
| test product 1 | 42 | 52 | 3 | 30 |
| test product 2 | 10 | 13 | 0 | 0 |

或者

|   PRODUCT_NAME | UNIQUE_HITS | TOTAL_HITS | TOTAL_SALES | TOTAL_REVENUE |
|----------------|-------------|------------|-------------|---------------|
| test product 1 | 42 | 52 | 3 | 30 |
| test product 2 | 0 | 0 | 0 | 0 |

如果表中甚至没有访问者跟踪数据。

我不知道该怎么做。需要一些帮助! :)

最佳答案

只需切换到左外连接:

SELECT products.product_name,
tracking.unique_hits,
tracking.total_hits,
COUNT(sales.product_id) as total_sales,
coalesce(SUM(sales.amount), 0) as total_revenue
FROM products
LEFT OUTER JOIN tracking ON products.id = tracking.product_id
LEFT OUTER JOIN sales ON products.id = sales.product_id
WHERE products.vendor_id = 0
GROUP BY products.product_name, tracking.unique_hits, tracking.total_hits;

编辑:

感谢 M Khalid 提供的group by。我会用表别名编写此查询以使其更易于阅读:

SELECT p.product_name, t.unique_hits, t.total_hits,
COUNT(s.product_id) as total_sales,
coalesce(SUM(s.amount), 0) as total_revenue
FROM products p LEFT OUTER JOIN
tracking t
ON p.id = t.product_id LEFT OUTER JOIN
sales s
ON p.id = s.product_id
WHERE p.vendor_id = 0
GROUP BY p.product_name, t.unique_hits, t.total_hits;

关于MySql 查询连接 3 个空行的表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/23278580/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com