java - mybatis中如何写mysql的union all 查询？-PHP中文网问答

文章专题 AI工具学习下载问答源码最近更新

PHP

会员中心讲师中心微信公众号

java - mybatis中如何写mysql的union all 查询？

PHP中文网 2017-04-18 10:32:02

[Java讨论组]

1188

用mybatis如何写下边的sql？

select order_union.* from 
(select order_id, uid, amount, pay_type, pay_time time,'2016' ,'支付' as order_type from fl_pay_order where uid=1001639998 and status=1
UNION ALL 
select order_id, uid ,amount , null as pay_type, pay_time time ,'2017','提现' as order_type from fl_withdraw_order where uid=1001639998 and status=3)
as order_union
order by order_union.time desc limit 0,10

PHP中文网

认证高级PHP讲师

全部回复(2)

PHPz2017-04-18 10:34:02 2楼

select order_id, uid, amount, pay_type, pay_time time,'2016' as y,'支付' as order_type from fl_pay_order where uid=1001639998 and status=1
UNION ALL
select order_id, uid ,amount , null as pay_type, pay_time time ,'2017' as y,'提现' as order_type from fl_withdraw_order where uid=1001639998 and status=3

赞 +0

添加回复

迷茫2017-04-18 10:34:02 1楼

<select id="yourId" parameterType="com.yourcomparny.yourmodel.YourType" resultMap="yourResultMap">
        SELECT ORDER_UNION.*
              FROM (SELECT ORDER_ID,
                           UID,
                           AMOUNT,
                           PAY_TYPE,
                           PAY_TIME TIME,
                           '2016',
                           '支付' AS ORDER_TYPE
                      FROM FL_PAY_ORDER
                     WHERE UID = 1001639998
                       AND STATUS = 1
                    UNION ALL
                    SELECT ORDER_ID,
                           UID,
                           AMOUNT,
                           NULL AS PAY_TYPE,
                           PAY_TIME TIME,
                           '2017',
                           '提现' AS ORDER_TYPE
                      FROM FL_WITHDRAW_ORDER
                     WHERE UID = 1001639998
                       AND STATUS = 3) AS ORDER_UNION
             ORDER BY ORDER_UNION.TIME DESC LIMIT 0, 10
    </select>

赞 +0

添加回复

专题推荐

更多>

热门话题

热门教程

更多>