联合查询
最近遇到一个需求需要查询出订单记录等信息,有两类查询参数,但是结果又要按照时间统一排序
用的是union来解决的
联合查询结果是将多个select语句的查询结果合并到一块
可以使用union和union all关键字进行操作
all:表示无论重复都输出
distinct: 去重(整个重复)(默认的)
联合查询只要求字段一样, 跟数据类型和顺序无关,数量对的上都能返回结果到一列去。
排序
- 两个sql分别排序需要加上括号,然后对union结果进行select:order by不能直接出现在union的子句中,但是可以出现在子句的子句中
- 联合结果排序,只在最后加上order by
(SELECT
...
b.CREATE_TIME AS TRADE_TIME
FROM a INNER JOIN b ON a.ORDER_NO = b.ORDER_NO
WHERE a.COMPANY_ID = #{companyId}
AND r.VALID_FLAG = 'ENABLE'
AND f.STATUS = 'SUCCESS')
UNION ALL
(SELECT *
FROM (a INNER JOIN b ON a.ORDER_NO = b.ORDER_NO)
INNER JOIN c ON b.ID = c.FUND_ID
WHERE r.COMPANY_ID = #{companyId}
AND c.FUND_STATUS = 'REFOUND_ALL'
AND c.STATUS = 'REFOUND_ALL'
AND a.VALID_FLAG = 'ENABLE')
ORDER BY TRADE_TIME DESC