在MySQL中使用Union All和Order By [英] Using Union All and Order By in MySQL

问题描述

我有2张桌子:

create table advertised_products(id int,title varchar(99),timestamp timestamp);
insert advertised_products select 1,'t1',curdate();

create table wanted_products(id int,title varchar(99),timestamp timestamp);
insert wanted_products select 1,'t1',now();

我正在使用此查询来获取记录:

I'm using this query to get the records:

(
SELECT 
    ap.*,
    'advertised'  as type 
FROM advertised_products as ap
)
union all
(
SELECT 
    wp.*,
    'wanted' as type 
FROM wanted_products as wp
)
ORDER BY timestamp desc limit 3

但是它给出了错误:

order子句中的时间戳"列不明确

Column 'timestamp' in order clause is ambiguous

我该如何排序?

推荐答案

将其包装在子查询中.

SELECT s.*
FROM
    (
        SELECT  ap.*, 'advertised'  as type 
        FROM advertised_products as ap
          union all
        SELECT  wp.*, 'wanted' as type 
        FROM wanted_products as wp
    ) s
ORDER BY s.timestamp desc 
limit 3

这篇关于在MySQL中使用Union All和Order By的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！