我有一个表 TABLE_A 现在大概有900多万行数据
orderID type
00001 A
00001 B
00001 C
00002 A
00002 B
00002 B
00003 A
00003 A
00003 A
00004 A
00004 B
00004 B
我要查询type全是A的订单号,我写的下边语句,慢的要死
SELECT orderID FROM TABLE_A
WHERE temp.status not in('B','C') GROUP BY orderID
晚上说自连的话会很快,我该怎么写,怎么优化~~~~~
orderID type
00001 A
00001 B
00001 C
00002 A
00002 B
00002 B
00003 A
00003 A
00003 A
00004 A
00004 B
00004 B
我要查询type全是A的订单号,我写的下边语句,慢的要死
SELECT orderID FROM TABLE_A
WHERE temp.status not in('B','C') GROUP BY orderID
晚上说自连的话会很快,我该怎么写,怎么优化~~~~~
WHERE temp.status='A' status要建立索引
SELECT DISTINCT (orderID) item_id FROM TABLE_A
WHERE orderID NOT IN (SELECT orderID FROM TABLE_A
WHERE type IN('B', 'C') GROUP BY order_id)
速度很慢
WHERE upper(temp.status)='A'