minus SQL 優化請教

newkid

Naldonado 发表于 2017-5-29 16:03
你是指这样？

可以把having精简：
having count(DECODE(systype,'a',null,1))=0

Naldonado

newkid 发表于 2017-5-29 21:25
可以把having精简：
having count(DECODE(systype,'a',null,1))=0

腻害，确实如此！精简了不少，反正where 里面已经有了dept_id，然后又少了b,c,d......

kigo6

Naldonado 发表于 2017-5-29 16:03
你是指这样？

剛剛多做了個試驗 如果同一systype 但dept不一樣的話，結果卻不一樣了
  
原SQL:
select item_id from table a where systype ='a' and dept_id <> '1234'
minus
select item_id from table a where systype ='a' and dept_id = '1234'
minus
select item_id from table a where systype ='b' and dept_id <> '1234'
minus
select item_id from table a where systype ='c' and dept_id <> '1234'

改寫後：
select item_id from table a
    where  a.systype in('a','b','c')
   group by item.id
    having count(case when systype='a' and dept_id='1234' then 1 else null end )=0
     having count(case when systype='b' and dept_id<>'1234' then 1 else null end )=0
     having count(case when systype='c' and dept_id<>'1234' then 1 else null end )=0
   

newkid

你这13楼的需求和7楼又不一样了，然后套用别人对7楼的解答，这完全是风马牛不相及。
建议：
“想清楚”自己要干什么。
“说清楚”自己要干什么。
给出建表、INSERT语句构造出一个测试用例。
给出预期结果。如果自己已有查询，贴出来并说明为什么不是你想要的结果。
其实，就是参见置顶帖“提问的智慧”。懂得提问的人，问题很快就能得到解答。

kigo6

newkid 发表于 2017-5-30 04:08
你这13楼的需求和7楼又不一样了，然后套用别人对7楼的解答，这完全是风马牛不相及。
建议：
“想清楚”自 ...

了解，其實9樓與11樓已經是我現階段的需求了

13樓的問題單純是我一時發想的問題，想說可以一起討論...

謝謝newkid提醒，未來針對問題的發問會更謹慎
=========================================

sql:

create table iteminfo (item_id varchar2(10),systype varchar2(10),dept_id varchar2(10));

insert into iteminfo values ('it_1','a','1234');
insert into iteminfo values ('it_2','a','2234');
insert into iteminfo values ('it_3','a','3456');
insert into iteminfo values ('it_4','a','1325');
insert into iteminfo values ('it_5','b','1234');
insert into iteminfo values ('it_6','b','2234');
insert into iteminfo values ('it_7','b','3456');
insert into iteminfo values ('it_8','b','1325');
insert into iteminfo values ('it_9','c','1234');
insert into iteminfo values ('it_10','c','2234');
insert into iteminfo values ('it_11','c','3456');
insert into iteminfo values ('it_12','c','1325');

commit;


SQL> select item_id from iteminfo where systype='a' and dept_id<>'1234'
  2  minus
  3  select item_id from iteminfo where systype='a' and dept_id='1234'
  4  minus
  5  select item_id from iteminfo where systype='b' and dept_id<>'1234'
  6  minus
  7  select item_id from iteminfo where systype='c' and dept_id<>'1234'
  8    ;

ITEM_ID
----------
it_2
it_3
it_4

SQL> select item_id from iteminfo
  2   where systype in ('a','b','c')
  3    group by item_id
  4    having count(case when systype='a' and dept_id='1234' then 1 else null end )=0
  5    and count(case when systype='b' and dept_id<>'1234' then 1 else null end )=0
  6    and count(case when systype='c' and dept_id<>'1234' then 1 else null end )=0
  7  ;

ITEM_ID
----------
it_2
it_3
it_4
it_5
it_9

SQL> spool off

kigo6

kigo6 发表于 2017-5-30 05:11
了解，其實9樓與11樓已經是我現階段的需求了

13樓的問題單純是我一時發想的問題，想說可以一起討論 .. ...

看我這蠢的...
到最後研究發現 原來是where過程中 忘記先將其他systype給濾了

result 1:
select item_id from item_info
where (systype ='a') or (systype='b' and dpet<> '1234') or (systype='c' and dept<>'1234')
group by item_id
having count(case when systype='a' and dept='1234' then 1 else null end)=0
count(case when systype='b' and dept<>'1234' then 1 else null end)=0
count(case when systype='c' and dept<>'1234' then 1 else null end)=0

result 2:
select item_id from item_info
where (systype ='a') or (systype='b' and dpet<> '1234') or (systype='c' and dept<>'1234')
group by item_id
having count(decode(systype,'a',null,1))=0 and  count(decode(dept_id,'1234',1,null)) =0