- 帖子
- 12095
- 积分
- 12095
- 经验
- 12095 点
- 威望
- 51 点
- 金钱
- 40138 NG
- 魅力
- 190464
- 性别
- 男
- 来自
- 世界政府-TS
- 注册时间
- 2003-4-15
我们约会吧!
|
10#
发表于 2005-6-7 09:04
| 只看该作者
SQL> WITH t1 AS
2 (SELECT a.x,
3 b.y,
4 a.x * b.y p,
5 a.x + b.y s,
6 COUNT (*) OVER (PARTITION BY a.x * b.y) pcnt,
7 COUNT (*) OVER (PARTITION BY a.x + b.y) scnt
8 FROM (SELECT ROWNUM + 1 x
9 FROM user_objects
10 WHERE ROWNUM < 99) a,
11 (SELECT ROWNUM + 1 y
12 FROM user_objects
13 WHERE ROWNUM < 99) b
14 WHERE a.x < b.y)
15 SELECT x,
16 y
17 FROM (SELECT x,
18 y,
19 x + y s,
20 COUNT (*) OVER (PARTITION BY x + y) scnt
21 FROM (SELECT x,
22 y,
23 x * y p,
24 COUNT (*) OVER (PARTITION BY x * y) pcnt
25 FROM (SELECT x,
26 y,
27 x * y p,
28 x + y s,
29 COUNT (*) OVER (PARTITION BY x * y) pcnt,
30 COUNT (*) OVER (PARTITION BY x + y) scnt
31 FROM t1 o
32 WHERE scnt > 1
33 AND NOT EXISTS (SELECT NULL
34 FROM t1 i
35 WHERE i.pcnt = 1
36 AND i.s = o.s))
37 WHERE pcnt = 1))
38 WHERE scnt = 1;
X Y
---------- ----------
4 13
SQL> |
|