实例:
数据库中有2张表,2张表中都有我需要的记录,
要将2张表中的记录一起取出来进行排序,这需要用UNION把2张表合并在一起。
表①:t_event
id | user_id | title | comment | time | display |
1 | 478 | hi | how are u? | 2007-06-16 | 1 |
表②:t_topics
id | user_id | title | comment | time | display |
1 | 478 | hi | my name is zj | 2007-06-18 | 0 |
2 | 478 | hi | nice to meet u | 2007-06-20 | 1 |
SQL语句:
SELECT * FROM (
SELECT
'event' as flag,
t_event.id as id,
t_event.user_id as user_id,
t_event.title as title,
t_event.comment as comment,
t_event.time as time,
t_event.display as display
FROM
t_event
UNION
SELECT
'topics' as flag,
t_topics.id as id,
t_topics.user_id as user_id,
t_topics.title as title,
t_topics.comment as comment,
t_topics.time as time,
t_topics.display as display
FROM
t_topics
) as union
WHERE
user_id = 478 and
display = true
ORDER BY
time DESC
OFFSET ?
LIMIT ?
结果:
flag | id | user_id | title | comment | time | display |
topics | 2 | 478 | hi | nice to meet u | 2007-06-20 | 1 |
event | 1 | 478 | hi | how are u? | 2007-06-16 | 1 |
这样就把2张表里满足user_id=478并且display=1的记录取出来放在一起,按时间降序进行排序。
需要注意的是,2张表对应的字段类型必须相同,如果不同,可以强制转换。比如用Bool()把int转成bool型,用Date()把vchar转成timestamp型。
疑问:
UNION和UNION ALL有什么区别还是不知道,如果有高手路过,还望指点一二。