mysql - 单个 mysql 表中的多个 select 查询

Question

我仍在通过编写简单的程序来学习 PHP 和 MySql。在我最新的项目中，我面临一个问题，你可能会觉得很简单，但我被困住了。

我有一个这样的表:

id |  text       |  create_date         | active | featured | featured_end
---+-------------+----------------------+--------+----------+--------------------
1  |Some text 1  |  2016-02-10 17:32:51 | 1      | 0        | 0000-00-00 00:00:00
2  |Some text 2  |  2016-02-09 17:32:51 | 1      | 1        | 2016-03-05 17:32:51
3  |Some text 3  |  2016-02-08 17:32:51 | 1      | 1        | 2016-03-05 17:32:51
4  |Some text 4  |  2016-02-07 17:32:51 | 0      | 0        | 0000-00-00 00:00:00
5  |Some text 5  |  2016-02-06 17:32:51 | 1      | 0        | 0000-00-00 00:00:00
6  |Some text 6  |  2016-02-05 17:32:51 | 1      | 0        | 0000-00-00 00:00:00
7  |Some text 7  |  2016-02-04 17:32:51 | 1      | 1        | 2016-03-05 17:32:51
8  |Some text 8  |  2016-02-03 17:32:51 | 1      | 0        | 0000-00-00 00:00:00
9  |Some text 9  |  2016-02-02 17:32:51 | 0      | 0        | 0000-00-00 00:00:00
10 |Some text 10 |  2016-02-01 17:32:51 | 1      | 1        | 2016-03-05 17:32:51

我需要使用一些过滤器从该表中获取所有结果:

1) 结果必须仅包含 active = 1

2)featured = 1 必须位于结果集的开头

3) 并且如果featured = 1 那么featured_end 必须大于now()

我尝试过这个(不起作用):

SELECT * FROM `table` WHERE `active` = 1 
UNION SELECT * FROM `table` WHERE `featured` = 1
HAVING `featured_end` > now()
ORDER BY `featured` 
DESC, `create_date` DESC 

这个也不起作用:

SELECT * FROM (
(SELECT * FROM `table` WHERE `active` = 1)
UNION
(SELECT * FROM `table` WHERE `featured` = 1 AND `featured_end` > NOW())
) table
ORDER BY `featured` DESC 

显然，我错过了一些东西，或者我完全走在错误的道路上。你能引导我走正确的道路吗？

Answer 1

我还没有测试过这个，但它应该可以完成工作。

SELECT *
FROM `table`
WHERE `active` = 1
AND (
    (`featured` = 1 AND `featured_end` > now())
    OR `featured` = 0
)
ORDER BY `featured` DESC