mysql - 获取 NonUniqueDiscoveredSqlAliasException

Question

表结构如下

+----------------+----------+-------------------- +-------------+---------+--------------------+---------------- ------+----------------+----------------+----- ------------------+--------------------+--------- ------------+--------------------+--------------+

| REDEMPTION_ID | CCID | MEMBERSHIP_NUMBER | POINTS_TYPE | PARTNER | SCHEME | REDEMPTION_ORDER_STATUS | MEMBER_SEGMENT | PARTNER_POINTS | MEMBERSHIP_FIRST_NAME | MEMBERSHIP_LAST_NAME | REDEMPTION_DATE | OUTBOUND_FILENAME | PRODUCT_TYPE |

+----------------+----------+-------------------+-------------+---------+--------+----- --------------------+----------------+------------------------ ----+--------------------+--------------------- -+--------------------------------+--------------------------------+------ --------+

| 1003740 | 21212103 | 1231237 | BASE | QANTAS | Visa | ORDERED | LEGACY | 5000.000000 | e | Name | 2017-10-23 10:26:51 | NABQF05P.012 | CONSUMER |

| 1003741 | 21212103 |1231238 | BONUS | QANTAS | Visa | ORDERED | LEGACY | 2500.000000 | e | Name
| 2017-10-23 10:26:51 | NABQF05P.012 | CONSUMER |

我想根据 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)

执行查询时，我收到上述异常

Answer 1

我明白了。由于我在同一个表中使用联接并且 Redemption_id 是主键，因此导致了异常。我使用 group_concat 而不是 join，它解决了我的问题。

请在链接http://sqlfiddle.com/#!9/dc59a1/5中查找解决方案