在mysql查询中选择唯一的行

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?

事情是一些我需要雇主的独特记录，但我得到重复，因为statuses.number重复像222 6 777等我只需要抓住他们一次。我不想用DISTINCT还有其他办法吗？

3 个解决方案

#1

use the GROUP BY statment

使用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

#2

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

在雇主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

#3

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.

首先，每当你在WHERE子句中引用LEFT JOINed列时，比如statuses.number = 2，你就取消了LEFT并迫使它表现得像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)

#1