我有以下sql表达式:
SELECT SS_ITEM_SK AS POP_ITEM_SK
FROM (SELECT SS_ITEM_SK
FROM (SELECT SS_ITEM_SK,(ITEM_SOLD-ITEM_RETURNED) AS TOT_SOLD_QTY FROM (SELECT SS_ITEM_SK,COUNT(SS_ITEM_SK) AS ITEM_SOLD,COUNT(SR_ITEM_SK) AS ITEM_RETURNED FROM STORE_SALES1 right outer join STORE_RETURNS1 on SS_TICKET_NUMBER = SR_TICKET_NUMBER AND SS_ITEM_SK = SR_ITEM_SK GROUP BY SS_ITEM_SK)))
INTERSECT
SELECT CS_ITEM_SK AS POP_ITEM_SK FROM (SELECT CS_ITEM_SK
FROM (SELECT CS_ITEM_SK,(ITEM_SOLD-ITEM_RETURNED) AS TOT_SOLD_QTY FROM (SELECT CS_ITEM_SK,COUNT(CS_ITEM_SK) AS ITEM_SOLD,COUNT(CR_ITEM_SK) AS ITEM_RETURNED FROM CATALOG_SALES1 right outer join CATALOG_RETURNS1 on CS_ORDER_NUMBER = CR_ORDER_NUMBER and CS_ITEM_SK = CR_ITEM_SK GROUP BY CS_ITEM_SK)))
INTERSECT
SELECT WS_ITEM_SK AS POP_ITEM_SK FROM (SELECT WS_ITEM_SK
FROM (SELECT WS_ITEM_SK,(ITEM_SOLD-ITEM_RETURNED) AS TOT_SOLD_QTY FROM (SELECT WS_ITEM_SK,COUNT(WS_ITEM_SK) AS ITEM_SOLD,COUNT(WR_ITEM_SK) AS ITEM_RETURNED FROM WEB_SALES1 right outer join WEB_RETURNS1 on WS_ORDER_NUMBER = WR_ORDER_NUMBER AND WS_ITEM_SK = WR_ITEM_SK GROUP BY WS_ITEM_SK)))
ApachePhoenix不支持关键字 INTERSECT
. 有没有人能帮我在不使用 INTERSECT
?
1条答案
按热度按时间gstyhher1#
我认为有多种方法可以做到这一点:
join方法
exists方法
in方法
参考文献:https://blog.jooq.org/2015/10/06/you-probably-dont-use-sql-intersect-or-except-often-enough/ 以及http://phoenix.apache.org/subqueries.html
虽然我会使用exists/in over the join,因为如果这些查询返回大量数据,那么您可能必须使用以下命令优化查询:https://phoenix.apache.org/joins.html