php - mysql UNION 重复项

Question

我正在使用这个 mysql 查询并对其进行排序，我添加了 bool 列 is_main (是否来自主表)。结果有效，但由于 is_main 列而导致我收到重复项。有解决此问题的方法吗？查询:

SELECT 
    b.dateTime,
    a.id,
    a.jobName,
    a.is_main
FROM job_data a, data b
WHERE a.userEmail = b.userEmail AND a.userEmail = :email
UNION
SELECT 
    b.dateTime,
    b.id,
    b.jobName,
    b.is_main
FROM job_data a, data b
WHERE a.userEmail = b.userEmail AND a.userEmail = :email

ORDER BY is_main DESC

job_data 表:

id | dateTime | jobName | is_main
1  | 00.00.00 | job1    | 0
2  | 00.00.00 | job2    | 0
3  | 00.00.00 | job3    | 0

数据表:

id | dateTime | jobName | is_main
1  | 00.00.00 | job1    | 1
2  | 00.00.00 | ---     | 1

输出如下:

job1  //this is not okay, because of duplicate job1
job1
job2 
job3 
_____
---  //this is perfect, because I want empty cell to show on top
job1
job2
job3

Answer 1

SELECT dateTime, id, jobName, max(is_main) from
( SELECT 
    b.dateTime,
    a.id,
    a.jobName,
    a.is_main
FROM job_data a, data b
WHERE a.userEmail = b.userEmail AND a.userEmail = :email
UNION
SELECT 
    b.dateTime,
    b.id,
    b.jobName,
    b.is_main
FROM job_data a, data b
WHERE a.userEmail = b.userEmail AND a.userEmail = :email

)
GROUP BY id