已解决请各位大佬给想一下这样的SQL语句该怎么写

安稳更新于2023-09-20

问题描述

有两个表一个是table_order订单表还有一个table_gift赠送礼物表，这两个表里都有字段叫pay_amount和user_id，需求就是我想根据这两个表pay_amount相加的和从大到小排序查询出user_id的结果集合，就是类似于消费最高的用户排名，这样的SQL该怎么写啊

为此你搜索到了哪些方案及不适用的原因

结合CHATGPT 和其他搜索用SQL子查询就能实现

sql
SELECT user_id, SUM(pay_amount) AS total_amount
FROM (
    SELECT user_id, pay_amount FROM table_order
    UNION ALL
    SELECT user_id, pay_amount FROM table_gift
) AS combined_table
GROUP BY user_id
ORDER BY total_amount DESC;

 555  2 0

2个回答

Dalong 2023-09-19

order by pay_amount desc

暂无评论

小W 2023-09-20

首选业务关系：

如果两个表user_id不是对应关系，或者说table_gift表中的user_id可能没有在table_order表中出现。
查询两张表中每个user_id的总金额

select user_id, sum(pay_amount) as a from TABLE group by user_id;
避免两个表数据重复，使用union all联合查询

select user_id, sum(pay_amount) as a from TABLE1 group by user_id union all select user_id, sum(pay_amount) as a from TABLE2 group by user_id;
从联合查询的结果查询并排序

select user_id, sum(a) as sum_price from (上面的联合查询语句) group by user_id order by sum_price desc;