表结构如下
+ --------------- + ---------- + ------------------- +- ----------- + --------- + -------- + ------------------- ------ + ---------------- + ---------------- + --------- -------------- + ---------------------- + ------------ --------- + ------------------- + -------------- +
| REDEMPTION_ID | CCID | MEMBERSHIP_NUMBER | POINTS_TYPE |伙伴
|计划| REDEMPTION_ORDER_STATUS | MEMBER_SEGMENT | PARTNER_POINTS |
MEMBERSHIP_FIRST_NAME | MEMBERSHIP_LAST_NAME | REDEMPTION_DATE |
OUTBOUND_FILENAME | PRODUCT_TYPE |
+ --------------- + ---------- + --------
----------- + ------------- + --------- + -------- + ----- -------------------- + ---------------- + ------------ ---- + ----------------------- + --------------------- -+ --------------------- + ------------------- + ------ -------- +
| 1003740 | 21212103 | 1231237 |基础|澳洲航空
|签证|订购遗产| 5000.000000 |
e |姓名| 2017-10-23 10:26:51 |
NABQF05P.012 |消费者
| 1003741 | 21212103 | 1231238 |奖金|澳洲航空|签证|订购遗产| 2500.000000 | e |名称
| 2017-10-23 10:26:51 | NABQF05P.012 |消费者
我想根据Membership_Number和POINTS_TYPE列对以上行进行分组，结果行应为一行。
我正在使用以下查询：
select * from ((
select * from NAB_REDEMPTION_DETAILS
where PARTNER='QANTAS' and REDEMPTION_ORDER_STATUS IN ('PLACED','RESEND') and POINTS_TYPE = 'BASE' group by MEMBERSHIP_NUMBER) a
left OUTER JOIN (
select * from NAB_REDEMPTION_DETAILS
where PARTNER='QANTAS' and REDEMPTION_ORDER_STATUS IN ('PLACED','RESEND') and POINTS_TYPE = 'BONUS' group by MEMBERSHIP_NUMBER) b on a.MEMBERSHIP_NUMBER=b.MEMBERSHIP_NUMBER) union (
select * from (
select * from NAB_REDEMPTION_DETAILS
where PARTNER='QANTAS' and REDEMPTION_ORDER_STATUS IN ('PLACED','RESEND') and POINTS_TYPE = 'BASE' group by MEMBERSHIP_NUMBER) c right OUTER JOIN (
select * from NAB_REDEMPTION_DETAILS
where PARTNER='QANTAS' and REDEMPTION_ORDER_STATUS IN ('PLACED','RESEND') and POINTS_TYPE = 'BONUS' group by MEMBERSHIP_NUMBER) d on c.MEMBERSHIP_NUMBER=d.MEMBERSHIP_NUMBER)
执行查询时，出现上述异常
最佳答案
我想到了。由于我在同一张表中使用联接，并且Redemption_id是主键，因此导致了异常。我使用group_concat而不是join，它解决了我的问题。
请在链接http://sqlfiddle.com/#!9/dc59a1/5中找到解决方案