在我的页面中,我使用以下查询来显示从当前时间到旧时间的选择。但它选择从旧时间选择到当前时间选择的行。
我想显示从当前时间到旧的选择
$result=mysql_query("(SELECT choice_id,poll_id,choicecreationtime from je_addchoice ORDER BY choicecreationtime DESC)
UNION
(SELECT choice_id,poll_id,datetime_voted from je_user_vote ORDER BY datetime_voted DESC)");
我不知道为什么会出现这个问题。如果我不使用UNION,就意味着它可以正常工作。例如以下查询显示当前时间到旧的顺序
$result = mysql_query("SELECT * FROM je_addchoice, je_addpoll where je_addpoll.privacy='0' AND je_addpoll.start_date <= '$check_date' AND je_addpoll.end_date >='$check_date' AND je_addpoll.poll_id=je_addchoice.poll_id order by je_addchoice.choicecreationtime desc");
任何人都可以帮我解决这个问题
通过子查询包装union
ed查询,并在外部查询上排序
SELECT *
FROM
(
SELECT choice_id, poll_id, choicecreationtime AS TIME
FROM je_addchoice
UNION
SELECT choice_id, poll_id, datetime_voted AS TIME
FROM je_user_vote
) s
ORDER BY TIME DESC
更新1
SELECT choice_id, poll_id, choicecreationtime AS TIME
FROM je_addchoice
UNION
SELECT choice_id, poll_id, datetime_voted AS TIME
FROM je_user_vote
ORDER BY Time DESC
如UNION
语法下所述:
要将
ORDER BY
或LIMIT
应用于单个SELECT
,请将子句放在包含SELECT
:的括号内(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
然而,对单个
SELECT
语句使用ORDER BY
并不意味着行在最终结果中出现的顺序,因为默认情况下UNION
会生成一组无序的行。因此,在这种情况下,ORDER BY
的使用通常与LIMIT
结合使用,因此它用于确定要为SELECT
检索的所选行的子集,即使它不一定影响最终UNION
结果中这些行的顺序。如果ORDER BY
在没有LIMIT
的情况下出现在SELECT
中,则会对其进行优化,因为它无论如何都不会产生任何影响。要使用
ORDER BY
或LIMIT
子句对整个UNION
结果进行排序或限制,请将各个SELECT
语句用括号括起来,并将ORDER BY
或LIMIT
放在最后一个语句之后。以下示例同时使用这两个子句:(SELECT a FROM t1 WHERE a=10 AND B=1) UNION (SELECT a FROM t2 WHERE a=11 AND B=2) ORDER BY a LIMIT 10;
没有括号的语句相当于刚才显示的一个带括号的语句。
试试这个:
SELECT choice_id, poll_id, dt
FROM (SELECT choice_id, poll_id, choicecreationtime dt FROM je_addchoice
UNION
SELECT choice_id, poll_id, datetime_voted dt FROM je_user_vote ) AS A
ORDER BY dt DESC