两个表上的sql排序相同的列名

Question

我有两张桌子：

1. projects_tasks
2. projects_tasks_deadlines

现在，我有一个简单的查询，在它们的最后期限对每个任务进行排序。一开始，任务只能有一个截止日期。

然后，我被要求能够为一项任务设定多个截止日期。这就是为什么我有第二个表projects_tasks_deadlines。

我设法允许用户选择应该激活的截止日期(任务细节)。但是在包含所有任务的列表中，如果至少可以的话，我不知道如何在两个表之间排序。

这是我目前使用的查询：

SELECT 
    projects_tasks.id AS task_id,
    projects_tasks.name AS task_name,
    projects_tasks.deadline AS task_deadline,
    projects_tasks.created_by AS task_creator,
    labels.name AS task_label,
    users.name AS task_responsible_name,
    users.surname AS task_responsible_surname,
    users2.name AS task_assigner_name,
    users2.surname AS task_assigner_surname
FROM `projects_tasks`
    INNER JOIN `labels` ON labels.id = projects_tasks.label_id
    INNER JOIN `users` ON users.id = projects_tasks.responsible_id
    INNER JOIN `users` AS `users2` ON users2.id = projects_tasks.created_by
WHERE projects_tasks.project_id = :project_id
AND labels.id != '8'
ORDER BY -projects_tasks.deadline DESC

如何实现我的目标，检查第二个表中是否有活动的截止日期，以及相应的`task_id。如果找到了火柴，就把最后期限算进去，然后分类。

举个例子：

表projects_tasks：

id | name | deadline
--------------------
1  | test | 2016-01-19
2  | test2| 2016-02-15
3  | test | 2016-01-25

表projects_tasks_deadlines

id | task_id | deadline  | active
--------------------------------
1  | 1       | 2016-01-20| 1 // yes
2  | 1       | 2016-01-24| 0 // no
3  | 2       | 2016-02-25| 0

结果应该是

1. 2016-01-20 -> projects_tasks_deadlines
2. 2016-01-25 -> projects_tasks
3. 2016-02-15 -> projects_tasks

谢谢你的帮助，希望这足够清楚..。

**更新**

SELECT 
    projects_tasks.id AS task_id,
    projects_tasks.name AS task_name,
    projects_tasks.created_by AS task_creator,
    projects_tasks_deadlines.id AS deadline_id,
    labels.name AS task_label,
    users.name AS task_responsible_name,
    users.surname AS task_responsible_surname,
    users2.name AS task_assigner_name,
    users2.surname AS task_assigner_surname,
    CASE WHEN projects_tasks_deadlines.deadline_active = '1' THEN projects_tasks_deadlines.deadline AS task_deadline ELSE
    projects_tasks.deadline AS task_deadline END
FROM `projects_tasks`
    INNER JOIN `labels` ON labels.id = projects_tasks.label_id
    INNER JOIN `users` ON users.id = projects_tasks.responsible_id
    INNER JOIN `users` AS `users2` ON users2.id = projects_tasks.created_by
    LEFT JOIN projects_tasks_deadlines ON projects_tasks_deadlines.task_id = projects_tasks.id
WHERE projects_tasks.project_id = '1'
AND labels.id != '8'
ORDER BY CASE WHEN projects_tasks_deadlines.deadline_active = 1 THEN -projects_tasks_deadlines.deadline ELSE -projects_tasks.deadline END DESC

我应该如何在CASE中做SELECT

Answer 1

你可以按条件订购。如果active =1，则使用projects_tasks_deadline表中的截止日期，否则使用projects_tasks表。所以就像这样：

SELECT 
    projects_tasks.id AS task_id,
    projects_tasks.name AS task_name,
    projects_tasks.deadline AS task_deadline,
    projects_tasks.created_by AS task_creator,
    labels.name AS task_label,
    users.name AS task_responsible_name,
    users.surname AS task_responsible_surname,
    users2.name AS task_assigner_name,
    users2.surname AS task_assigner_surname
FROM `projects_tasks` 
    INNER JOIN `labels` ON labels.id = projects_tasks.label_id
    INNER JOIN `users` ON users.id = projects_tasks.responsible_id
    INNER JOIN `users` AS `users2` ON users2.id = projects_tasks.created_by
LEFT JOIN projects_tasks_deadline ptd ON id = task_id
WHERE projects_tasks.project_id = :project_id
AND labels.id != '8'
ORDER BY CASE WHEN ptd.active = 1 THEN ptd.deadline ELSE projects_tasks.deadline END DESC

希望这有意义吗？

Answer 2

我终于找到了解决办法：

SELECT 
    projects_tasks.id AS task_id,
    projects_tasks.name AS task_name,
    projects_tasks.created_by AS task_creator,
    labels.name AS task_label,
    users.name AS task_responsible_name,
    users.surname AS task_responsible_surname,
    users2.name AS task_assigner_name,
    users2.surname AS task_assigner_surname,
    (CASE WHEN projects_tasks_deadlines.deadline_active = '1' THEN projects_tasks_deadlines.deadline ELSE projects_tasks.deadline END) AS task_deadline
FROM projects_tasks
    INNER JOIN `labels` ON labels.id = projects_tasks.label_id
    INNER JOIN `users` ON users.id = projects_tasks.responsible_id
    INNER JOIN `users` AS `users2` ON users2.id = projects_tasks.created_by
    LEFT JOIN projects_tasks_deadlines ON projects_tasks_deadlines.task_id = projects_tasks.id
WHERE projects_tasks.project_id = '1'
AND labels.id != '8'
GROUP BY projects_tasks.id
ORDER BY CASE WHEN projects_tasks_deadlines.deadline_active = '1' THEN -projects_tasks_deadlines.deadline ELSE -projects_tasks.deadline END DESC

我错过了另一个CASE在SELECT

(CASE WHEN projects_tasks_deadlines.deadline_active = '1' THEN projects_tasks_deadlines.deadline ELSE projects_tasks.deadline END) AS task_deadline

这过滤出了正确的截止日期，他们也在正确的最后期限上得到了分类。