首页 > 在表1中按某个colume值排序取前五,表二中按相同colume值排序取前五,在两表取的数据中再取前五,这个操作怎么写?

在表1中按某个colume值排序取前五,表二中按相同colume值排序取前五,在两表取的数据中再取前五,这个操作怎么写?

例如表一table_1,三个字段id, name, time
表二table_2,三个字段id,name,time
各自查询表中time最大值,再取两者中较大的一个,并标注来自表


SELECT id,NAME,TIME,table_name FROM (
    (SELECT id,NAME,TIME,'tab1' AS table_name FROM tab1 ORDER BY TIME DESC LIMIT 0,5)
    UNION
    (SELECT id,NAME,TIME,'tab2' AS table_name FROM tab2 ORDER BY TIME DESC LIMIT 0,5)
) AS tmp ORDER BY TIME DESC LIMIT 0,1

自问自答:
SELECT
temp.id, temp.time, temp.ttable
FROM
((SELECT
id, time, '1' ttable
FROM
table_1
ORDER BY time DESC
LIMIT 5) UNION ALL (SELECT
id, time, '2' ttable
FROM
table_2
ORDER BY time DESC
LIMIT 5)) AS temp
ORDER BY time DESC
LIMIT 5

【热门文章】
【热门文章】