关于数据库理论中的divideby的问题

哪位大虾可以用sql实现一个简单的除法操作。比如:
s#{s1,s2,s3,s4,s5}
p#{p1,p2,p3,p4,p5,p6}
p1#{p1}
sp#{{s1,p1},{s1,p2},{s1,p3},{s1,p4},{s1,p5},{s1,p6},{s2,p1}}

s# divideby p1# per sp# = {s1,s2}
s# divideby p# per sp# = {s1}

---------------------------------------------------------------

SQL> select * from s#;

COL
----------
s1
s2
s3
s4
s5

已用时间: 00: 00: 00.30
SQL> select * from p#;

COL
----------
p1
p2
p3
p4
p5
p6

已选择6行。

已用时间: 00: 00: 00.41
SQL> select * from p1#;

COL
----------
p1

已用时间: 00: 00: 00.30
SQL> select * from sp#;

COL1 COL2
---------- ----------
s1 p1
s1 p2
s1 p3
s1 p4
s1 p5
s1 p6
s2 p1

已选择7行。

已用时间: 00: 00: 00.40
SQL> select col1 from (select col1,count() cnum from sp#,p# where sp#.col2=p#.col group by col1) t
2 ,s# where cnum=(select count(
) from p#) and s#.col=t.col1;

COL1
----------
s1

已用时间: 00: 00: 00.30
SQL> select col1 from (select col1,count() cnum from sp#,p1# where sp#.col2=p1#.col group by col1)
t
2 ,s# where cnum=(select count(
) from p1#) and s#.col=t.col1;

COL1
----------
s1
s2

已用时间: 00: 00: 00.20
SQL>

Published At
Categories with 数据库类
Tagged with
comments powered by Disqus