SQL 左连接不显示左侧的所有行

发布于 2024-10-26 18:47:26 字数 494 浏览 4 评论 0原文

因此，我所关注的两个表的粗略结构如下：

Table one: Services
services.id
services.name

Table two: Orders
orders.id
orders.item
orders.service

表一包含服务列表。表二是订单列表。我试图从表二（订单）中生成所有服务订单的列表，但还包括（零）尚未订购的服务。我知道这就是 LEFT JOIN 的来源，但它似乎根本不起作用。它显示了大多数服务，但有一两个记录（来自服务）未显示。这是我到目前为止使用的查询..

非常感谢任何指导，谢谢！

select services.name,count(orders.service)
from services
LEFT JOIN orders ON services.id=orders.service
WHERE item IN (1,2,3,4)
group by statuses.service;

原文

So a rough structure of my two tables concered is as follows:

Table one: Services
services.id
services.name

Table two: Orders
orders.id
orders.item
orders.service

Table one contains a list of services. And table two is a list of orders. I am trying to generate a list of all orders for services from table two (orders) but also include (Zero) the services that haven't been ordered. I'm aware that that's where the LEFT JOIN comes but it doesn't seem to be working at all. It displays most of the services but there's one or two records (from services) not being displayed. Here's the query i'm using so far..

Any guidance at all is much appreciated, thanks!

select services.name,count(orders.service)
from services
LEFT JOIN orders ON services.id=orders.service
WHERE item IN (1,2,3,4)
group by statuses.service;

分享到QQ

分享到微博

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

发布评论

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

離殇 2024-11-02 18:47:26

您最初的选择很好，应该允许服务表中的所有记录通过。但是，您可以通过 where 子句来限制这一点。如果特定行没有连接，item 中将有一个 NULL，您的 WHERE 子句将过滤掉该值。

SELECT services.name
   ,COUNT(orders.service)
FROM services
LEFT JOIN orders ON services.id = orders.service
WHERE item IS NULL
    OR item IN ( 1, 2, 3, 4 )
GROUP BY statuses.service;

如果我有一点错误，请原谅我，我来自 SQL Server 背景。

Your original selection is fine and should allow all records from the services table through. However you're then restricting this by your where clause. If there was no join for a specific row, item will have a NULL in it which your WHERE clause is filtering out.

SELECT services.name
   ,COUNT(orders.service)
FROM services
LEFT JOIN orders ON services.id = orders.service
WHERE item IS NULL
    OR item IN ( 1, 2, 3, 4 )
GROUP BY statuses.service;

Forgive me if slightly wrong, I'm coming from SQL Server background.

回复收藏 0 原文

春庭雪 2024-11-02 18:47:26

首先，我相信你有一个拼写错误 - statuses.service 应该是 services.name，对吗？

由于 item 是订单表中的一列，因此您应该将其放入连接条件中：

select services.name,count(orders.service)  
from services  
LEFT JOIN orders ON services.id=orders.service and services.item IN (1,2,3,4)  
group by statuses.service;

否则，您将过滤掉没有订单的服务或订单中仅包含不在 (1,2,3,4) 中的项目的服务。

亲切的问候，弗兰克

First of all, I believe you've got a typo - statuses.service should be services.name, right?

Since item is a column from the orders table, you should put it in the join condition:

select services.name,count(orders.service)  
from services  
LEFT JOIN orders ON services.id=orders.service and services.item IN (1,2,3,4)  
group by statuses.service;

Otherwise, you filter out the services without orders or whose orders only have items not in (1,2,3,4).

Kind regards, Frank

回复收藏 0 原文

~没有更多了~