mysql UNION重复(mysql UNION duplicates)
我正在使用这个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 DESCjob_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 job3I'm using this mysql query and to sort it, I have added bool column is_main (is it from main table). The result is working but I'm getting duplicates, because of that is_main column.. Is there any fix for this? Query:
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 DESCThe job_data table:
id | dateTime | jobName | is_main 1 | 00.00.00 | job1 | 0 2 | 00.00.00 | job2 | 0 3 | 00.00.00 | job3 | 0The data table:
id | dateTime | jobName | is_main 1 | 00.00.00 | job1 | 1 2 | 00.00.00 | --- | 1The output goes something like this :
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最满意答案
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 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 idmysql UNION重复(mysql UNION duplicates)我正在使用这个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 DESCjob_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 job3I'm using this mysql query and to sort it, I have added bool column is_main (is it from main table). The result is working but I'm getting duplicates, because of that is_main column.. Is there any fix for this? Query:
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 DESCThe job_data table:
id | dateTime | jobName | is_main 1 | 00.00.00 | job1 | 0 2 | 00.00.00 | job2 | 0 3 | 00.00.00 | job3 | 0The data table:
id | dateTime | jobName | is_main 1 | 00.00.00 | job1 | 1 2 | 00.00.00 | --- | 1The output goes something like this :
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
发布评论