在mysql查询中选择唯一的行

发布于 2024-11-28 10:50:52 字数 340 浏览 0 评论 0原文

我有疑问

SELECT DISTINCT employer.id, employer.name
FROM employers 
LEFT JOIN positions ON positions.id = employers.id 
LEFT JOIN statuses ON statuses.position = positions.some 
WHERE statuses.number = 2

，问题是我需要雇主提供唯一的记录，但我收到重复的记录，因为 statuses.number 确实像 222 6 777 等重复，我只需要获取它们一次。我不想使用 DISTINCT 还有其他方法吗？

原文

I have query

SELECT DISTINCT employer.id, employer.name
FROM employers 
LEFT JOIN positions ON positions.id = employers.id 
LEFT JOIN statuses ON statuses.position = positions.some 
WHERE statuses.number = 2

And the thing is some i need unique records from employers but i am getting duplicates because statuses.number do repeat like 222 6 777 etc i need to grab them only once.
I dont want to use DISTINCT is there other way?

分享到QQ

分享到微博

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

发布评论

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

清风疏影 2024-12-05 10:50:52

使用 GROUP BY 语句

   SELECT employer.id, employer.name
    FROM employers 
     LEFT JOIN positions ON positions.id = employers.id 
     LEFT JOIN statuses ON statuses.position = positions.some 
    WHERE statuses.number = 2 GROUP BY employer.id

use the GROUP BY statment

   SELECT employer.id, employer.name
    FROM employers 
     LEFT JOIN positions ON positions.id = employers.id 
     LEFT JOIN statuses ON statuses.position = positions.some 
    WHERE statuses.number = 2 GROUP BY employer.id

回复收藏 0 原文

夏雨凉 2024-12-05 10:50:52

对雇主 ID 使用 GROUP BY，对于返回的每一行，您只会获得一条雇主记录：

SELECT employer.id, employer.name
FROM employers 
LEFT JOIN positions ON positions.id = employers.id 
LEFT JOIN statuses ON statuses.position = positions.some 
WHERE statuses.number = 2
GROUP BY employer.id

Using GROUP BY on employer id, you will only get one employer record for each row returned:

SELECT employer.id, employer.name
FROM employers 
LEFT JOIN positions ON positions.id = employers.id 
LEFT JOIN statuses ON statuses.position = positions.some 
WHERE statuses.number = 2
GROUP BY employer.id

回复收藏 0 原文

折戟 2024-12-05 10:50:52

首先，每当您在 WHERE 子句中引用 LEFT JOINed 列（例如 statuses.number =2）时，您都会否定 LEFT 并强制其表现得像 INNER。

其次，尝试这个版本：

SELECT e.id, e.name
    FROM employers e
    WHERE EXISTS(SELECT 1
                     FROM positions p
                         INNER JOIN statuses s
                             ON p.some = s.position
                     WHERE p.id = e.id
                         AND s.number = 2)

First, any time you reference a LEFT JOINed column in your WHERE clause, like statuses.number =2, you negate the LEFT and force it to behave like an INNER.

Second, try this version:

SELECT e.id, e.name
    FROM employers e
    WHERE EXISTS(SELECT 1
                     FROM positions p
                         INNER JOIN statuses s
                             ON p.some = s.position
                     WHERE p.id = e.id
                         AND s.number = 2)

回复收藏 0 原文

~没有更多了~