我有一个表 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 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)
网上说自连的话会很快,我该怎么写,怎么优化~~~~~
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 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)
网上说自连的话会很快,我该怎么写,怎么优化~~~~~
这些都是导致查询慢的原因啊按你的要求,可以这样写
SELECT DISTINCT (orderID) item_id
FROM TABLE_A
WHERE type='A' OR type='C'
得到的结果和你现在要的结果是一样的,你可以试一下
where not exist(
select orderid from tableA b
where a.orderid=b.orderid and b.type='B'
union
select orderid from tableA b
where a.orderid=b.orderid and b.type='c')这样试试
where type='A' and not exists
(select 1 from table_a b where a.orderid=b.orderid and type<>'A')
我想的type全是A的订单号~~~~
where not exists(
select orderid from table_A b
where a.orderid=b.orderid and b.type<>'A')
我建了一个索引index(orderid,type)不起作用怎么回事
WHERE type='A'
请参考:
Oracle Sql 语句性能调优
FROM TABLE_A
WHERE type='A'