这是最好的时代，这是最坏的时代，这是智慧的时代，这是愚蠢的时代；这是信仰的时期，这是怀疑的时期；这是光明的季节，这是黑暗的季节；这是希望之春，这是失望之冬；人们面前有着各样事物，人们面前一无所有；人们正在直登天堂；人们正在直下地狱。 我也要与时俱进了，被itpub2.0牵着尾巴，拼命的奔跑，不停的灌水...

【原】关于一条多条件SQL的写法

上一篇 / 下一篇  2008-03-04 22:14:07 / 个人分类：数据库专区

查看( 67 ) / 评论( 14 )

有一张多对多的表，查询时要判断出是否满足所有的记录要求
K}^u j0例如ITPUB个人空间Fv4qOlC)Us
1 aITPUB个人空间oh:B Z s
1 bITPUB个人空间,f6c!g#X @)kG@
1 cITPUB个人空间Z5_F_4O S ?)g
2 aITPUB个人空间:j+S4xl @$U&y/I
2 bITPUB个人空间D.~6R {0P$Ssdd
3 a
ZMDL(TG C Ho0如果查询a则只返回3ITPUB个人空间+`8K+Wd5t:E?
如果查询a、b则只返回2ITPUB个人空间 R8aK0]3mV
如果查询a、b、c则只返回1

为了获取结果我们不太可能接受动态查询的方式，去拼凑出所有想要的查询条件，行转列当然也是一个办法，不过当行不太固定的时候，构造这个视图显然也比较困难。

为了解决这个问题，构造了一个SortSeq，该字段存储的始终是2的指数值
6y"yxoD%Y h0Create Table DimTableITPUB个人空间"T?`P5l5}
(
bI"SS9f"Vu0  DimID    varchar2(20),ITPUB个人空间.h z}a*w.~JZ
  SortSeq  number(20,0)ITPUB个人空间(b$Nq0g*{G9mk&I
);ITPUB个人空间(r {kcMVa/h
Create Table CrossTable
JJn/N A(@2o0(
-m1K]f#|D0  SumID   varchar2(20),
6a u M"B*iZd$E`/E0  DimID   Varchar2(20)
3C8W8I+O'b9lF0);
z,m\*D*PJ%LA0INSERT INTO DimTable VALUES('A',1);
M^GT/mc y+ugA0INSERT INTO DimTable VALUES('B',2);
hU`?LI+I0INSERT INTO DimTable VALUES('C',4);
'^'buP%X%u;?*Lv3^0INSERT INTO DimTable VALUES('D',8);
3JQ]Y/x0y0INSERT INTO DimTable VALUES('E',16);
Fs_[#\(e0COMMIT;
7E/BEI(['s0INSERT INTO CrossTable VALUES('1','A');ITPUB个人空间*]Ft~uyk/@ r\i
INSERT INTO CrossTable VALUES('1','B');
9};ui3?9f)n'F$Jb!Z0INSERT INTO CrossTable VALUES('1','C');
$oV5j xPiaR0W0INSERT INTO CrossTable VALUES('1','D');
V#E#N9y5Zw%P O0INSERT INTO CrossTable VALUES('2','A');
;C~v2x^I-Z@0INSERT INTO CrossTable VALUES('2','B');ITPUB个人空间'Y%H9|]Q N+NF K }
INSERT INTO CrossTable VALUES('2','C');ITPUB个人空间f i/}B g _n X~
INSERT INTO CrossTable VALUES('3','A');
"Q;}CS_T'UjA0INSERT INTO CrossTable VALUES('3','B');ITPUB个人空间Q0S kE3o;Y
INSERT INTO CrossTable VALUES('4','A');ITPUB个人空间Fbs$~KMQ B
COMMIT;
K%VT,g$s0t4o6E0例如我们要查询A和C，我们只需要找到A和C所对应的SortSeq即1和4，两者相加等于5

SELECT b.SumIDITPUB个人空间x],X1K @q+}
  FROM DimTable a,CrossTable BITPUB个人空间5cr;K VR8Q
 WHERE a.DimID=b.DimIDITPUB个人空间c:v5}}+~MFoG
 GROUP BY b.SumIDITPUB个人空间$x`#n,t8~%~5_KR:Q!w/A
 HAVING SUM(a.SortSeq)=5
,? f"a{[~0 
tX RMZr0j&x*?0其中的奥妙在于所有2的指数相加始终是一个唯一值，正是这个唯一值保证了查询的唯一性！
VuW;|+b8Q"ab-nU0最后感谢小杨同学提供的思路


论坛模式 推荐 收藏 分享给好友 推荐到圈子 管理

TAG:

zhangweicai74的个人空间 zhangweicai74 发布于2008-03-05 08:58:19
不错,值得参考
追、追、追一个
君三思的学习轨迹~~~~ junsansi 发布于2008-03-05 09:34:50
何用如此复杂~~~
nk.e&Xz M"PD7J;@*K
如果CrossTable 中sumid,dimid无重复的话，通过分析函数实现即可~~
zhangweicai74的个人空间 zhangweicai74 发布于2008-03-05 09:37:31
不过我测试了下,
J5k8Nd4e6j'nqITPUB个人空间SELECT b.SumIDspace.itpub.net8Q]-lei-\6o@&d
  FROM DimTable a,CrossTable B \@"t6u2f r H+Z({i9r
WHERE a.DimID=b.DimIDz:KOt,J'^
GROUP BY b.SumID
]vf`](K,f)tHAVING SUM(a.SortSeq)=5
9RFstl6[(Bspace.itpub.net
cN2WD6V _-aw没有值返回.7M,fRp*At"A

Z
?9N@1BEM
Q*YSELECT SUM(a.SortSeq)_4W\x9i f
  FROM DimTable a,CrossTable B
!lm*JY5?uC?space.itpub.netWHERE a.DimID=b.DimID
        e+A/P5yW5k+ziGROUP BY b.SumIDITPUB个人空间~7| aM9I
!qP)~D^2i
返回值是
8R9NFkCGN"y7
+k`8y@8N [ p15
君三思的学习轨迹~~~~ junsansi 发布于2008-03-05 09:37:39
查询时指定查询参数和参数个数即可，例如：space.itpub.net{w{gO |W8~.u

(Yx0bX*a,c[php]
TK;\%u5e BT^.K+D![SQL> select * from crosstable;)vkf {        QrBy`4s

?;k&t-E D&F%@X_oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.netSUMID                DIMID
C!v5A%uu]Y
t)loracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net-------------------- --------------------
Z6Tj8_(YaITPUB个人空间1                    ArG G8y4d&tg:yb
1                    B
1_*Ey9cm g1                    C
4}F0Q~:xVOITPUB个人空间1                    D
r%k/\/I8h}T1P koracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net2                    A
j(Uwn ~!CKspace.itpub.net2                    Bspace.itpub.net+\%T.u4u2z.k} b
2                    Ce:m6xx#a
3                    A
-Q2PO {6|!sB0V*{&y3                    B
4K        d}*W [$pspace.itpub.net4                    ANDr"P0S

/?$l}y7J _?KQ5QITPUB个人空间10 rows selected
XpJ
QorO
~

G*D)r
P;h*fSQL>
viuO ~6LSQL> select sumid, dimid_gt0A6ks3l)] ^
  2    from (SELECT a.*, count(sumid) over(partition by sumid order by sumid) ct
Y KP&H+Tg  3            FROM crosstable a) b
+b8KAh/y/R b_iITPUB个人空间  4   where dimid in ('A')ITPUB个人空间Q
mA%M%_D
m
  5     and ct = 1q"F+k+`r2e
  6  ;
(m0m w;[,c"J        o.A0K j ITPUB个人空间t|c        UG
SUMID                DIMIDoracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.netv:p{0kf:dvL i
-------------------- --------------------ITPUB个人空间4Yv?S        {vt
t
4                    APr9y Bu&B H,~

K-\/F1{3`E.qXz`SQL> 3FR"v-Wv
[F2t$S3X
SQL> select sumid, dimid
|RWm
xOl-Eoracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net  2    from (SELECT a.*, count(sumid) over(partition by sumid order by sumid) ct
0T7l9|)Sz~6C  3            FROM crosstable a) bspace.itpub.net)M!v @        \
ExGyN
  4   where dimid in ('A','B')space.itpub.net%i        D`wfA D
  5     and ct = 2space.itpub.net+r&mSl+A2V
  6  ;space.itpub.netPIx A,Z
space.itpub.net$ye
an5E-jJ
SUMID                DIMID&[\(I.w-B-f
To+GcD
-------------------- --------------------oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net        ~6Pro?#o8?
3                    B
gl
['Ip8dEy+Voracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net3                    A*rB#^7VeG
space.itpub.netk
Bx:Ty0Aw3T        a1F
SQL> space.itpub.netS ral8U*B
SQL> select sumid, dimid
w$T7vtYqm1c[oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net  2    from (SELECT a.*, count(sumid) over(partition by sumid order by sumid) ctoracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.netHy-]6Q
fc
  3            FROM crosstable a) b
I'K:p%BDk:k(O\ITPUB个人空间  4   where dimid in ('A','B','C','D')space.itpub.net%IJ5~:yj!F4Z
  5     and ct = 4
-G3v3Nh-[4]-~[oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net  6  ;
] A LWN%Fc space.itpub.netqC7R        @
S|T+m0B
\5R
SUMID                DIMIDP2bY3N LP-}
-------------------- --------------------ITPUB个人空间4^ Is1\q8l
WH9i
1                    Aoracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.netg7{ K[-QZ)EtA-i
1                    B
0^ K~)q m$R.F1                    Coracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net2m*Cz2y#q e2ES;B
1                    D:g3n.^'\&l1r8xa!h

NA({-ji#KoSQL>
(q-SCjZ'sti\7QITPUB个人空间--oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net
R*UEkD
[/php]
zhanglinjun007发布于2008-03-05 11:17:43

QUOTE:

原帖由 zhangweicai74 于 2008-3-5 09:37 发表 c#gY'[Yw
不过我测试了下,
1Lcl;o}Z)zSELECT b.SumID
E9w$Qj%C1av0oB9{c  FROM DimTable a,CrossTable Boracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.netfeEEaX
WHERE a.DimID=b.DimID
q5k]Zr_:G-saGROUP BY b.SumIDITPUB个人空间d        aSf(@n p+y
HAVING SUM(a.SortSeq)=5
:l        @ [o@
(d]af
p8Y#rL EITPUB个人空间没有值返回.ITPUB个人空间N+dY&mB%j

+B(Zc
m+uH8TSELECT SUM(a.SortSeq)space.itpub.net7|;U
VTk
vr;g1k
  FROM DimTable a,CrossTable B
e1f#@$aS.UQd!b8gWHERE a.DimID=b.DimID
3D)qfP,ZGROUP BY b.SumID h!eL2\-E
D7A[
space.itpub.netL{)z!p4}AA
Lf"iu
返回值是
x U}C]2a1q R$w7
4C
lF;LRH5J$A7poracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net15
oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net.uh@5t~B&Si
  5是指 SORTSEQ总和,看表里是否有数字对应A,C的.因为表里没有哪个数字对应A,C!%i7\
wa U7b
如果有:5,  A
Lh y,c%L0v           5  C
,{tg:Al/M @oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net则会返回5这个数了!
TOz%yQ5ol_  不知道我理解的对不对!
zhanglinjun007发布于2008-03-05 11:18:04
学习了,分析函数确实好用!
louis_xu的个人空间 louis_xu 发布于2008-03-05 12:22:19
LZ思路是不错啊，挺巧
`&P?3O){'p*O8yITPUB个人空间不过烦了点，还是三思的比较好，分析函数真是无所不能啊！
john_77发布于2008-03-05 14:35:51
学习了
nyfor发布于2008-03-05 15:15:17

QUOTE:

原帖由 junsansi 于 2008-3-5 09:37 发表 ITPUB个人空间7?b)?4gU
查询时指定查询参数和参数个数即可，例如：2B[*c+z]5e
.C7ZbZ@2@j(r`
[php]space.itpub.net3Z3}I        `"i&E
lM        R
SQL> select * from crosstable;
(@$A@.V
_2K1l1W
w2[L;elAUSUMID                DIMID
*q-MF`
Y\Y,i&H*?-------------------- --------------------
B|7m5hlAVspace.itpub.net1                    A
r3vC9M4d Moracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net1                    B
K8@9`B+]
I/aNoracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net1                    CITPUB个人空间-x$XKfr*x
1                    D
        D3h6m(T"Ik.Y2                    A
0r W1`PwT4E3pITPUB个人空间2                    B
j6g7c3q(X]space.itpub.net2                    C
2DgM'd@9u#CD(Coracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net3                    A
tl6xXU`E6Aiz3                    B
^%H
v\'I9I~rITPUB个人空间4                    A?xc-i+P$\q{:W1A
]T LQ2v'M
10 rows selected

d LE
x+|v/G
?;m M4oV@~
SQL> ,`T&Lf-e!OTZI]T5v
SQL> select sumid, dimidzt~Sd
  2    from (SELECT a.*, count(sumid) over(partition by sumid order by sumid) ct
0w\w0?/^{x        uITPUB个人空间  3            FROM crosstable a) b
5],xR9A$\ ^&M?ITPUB个人空间  4   where dimid in ('A'),_        P,\.lt bg@
  5     and ct = 1oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net$t2~5f4H&K"F
l{
  6  ;
|#i2x;U;t,i/G\XITPUB个人空间
})Yx[$og(Sspace.itpub.netSUMID                DIMID
\%S\Or~-y,CITPUB个人空间-------------------- --------------------
!z+bY
^Qm&d$h4                    A/?f@f5s#T-O
--
mSa kZB3D ?6H.l[/php]
yi?.G3l `D!Fj
:^ TN2@W        \4^
好像不对吧:
%_)Mp0jm1R在上面的数据基础上:
)I3y ^[+Y4`/R#KINSERT INTO CrossTable VALUES('3','D');space.itpub.net m)`A,Y,Rw
SQL> select sumid, dimid
0N m_ g,vy#i#q  2    from (SELECT a.*, count(sumid) over(partition by sumid order by sumid) ct
/f:ODXAC|J ho  3            FROM crosstable a) boracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net~
R?(s1l
  4   where dimid in ('A','B','D')oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net
QbErOP5`uv
  5     and ct = 3;
"wg*`hQ'd
"ay;emWnoracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.netSUMID                DIMID(UMZS vIP/Pwcg
t
-------------------- --------------------${'?Ez)w{
2                    AITPUB个人空间:q5N7`k!p0a*M*H0H
2                    B1G
xB7{S,_
pBCr8y
3                    Aoracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.netOgmc sd
3                    B
#[V$bB4KxITPUB个人空间3                    D
cm.R:w Z        Horacle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net e/{J5]MNK
SQL>
%`
e0? r%~)}oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net不应该出现下面这一组啊.
$a i`(_
P#v Mk5Cn2                    A
x
vu*N
C/bRNc#Wspace.itpub.net2                    B
nyfor发布于2008-03-05 15:16:59
类似问题以前曾有讨论过呢.
nyfor发布于2008-03-05 15:20:13
使用LZ提供的方法也很不错, 效率上比不用
-l)x8rguJSJ1YnITPUB个人空间Create Table DimTable
9l {9v,F4n7I(
LL%\Iw'U6b/^i4Tmoracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net  DimID    varchar2(20),ITPUB个人空间!]`N ZK#f%T
  SortSeq  number(20,0)M}8A&hX
);8H
W-T(XI9e3|h
要好, 不过使用一个 SQL 语句也是可以实现的, 只是效率上可能没有 LZ 的效率高.
bell6248的个人空间 bell6248 发布于2008-03-05 15:21:25
这个问题也是老问题了， 没有必要用分析函数！
君三思的学习轨迹~~~~ junsansi 发布于2008-03-05 15:36:23

QUOTE:

原帖由 nyfor 于 2008-3-5 15:15 发表
zG\({c7o8\8lITPUB个人空间oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net&H S;X4Je7Omo7N*No

ZO7O0r.]n4B8C2d,KIoracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net好像不对吧:
m3M$S^/k1R"\W在上面的数据基础上:ITPUB个人空间[4d;}X(c?8O
GcJ
INSERT INTO CrossTable VALUES('3','D');i3w+~
x%|_cT
N*j
SQL> select sumid, dimid
:?;TXFf-E5|,I  2    from (SELECT a.*, count(sumid) over(partition by sumid order by sumid) ct
u6t{U0q
g#Qbw  3            FROM crosstable a) b
,e`q7O@1Fd0jloracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net  4   where dimid in ('A','B','D')
7F1c ~1M2fITPUB个人空间  5     and ct = 3;
c7p*uTX3cITPUB个人空间 f"Bt"t;ZM;VA
SUMID                DIMIDspace.itpub.net"ly]_8b
-------------------- --------------------
2A&zV*hX v%U-\8}x+aspace.itpub.net2                    A
7Du{c
wxoracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net2                    B
q
n0o5fl3                    A
xDb o(k;N5R,i3                    BW&tD!ZqShQl
3                    DITPUB个人空间(n?%p9_\ ma(Nj[Nq
oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net H'r4k SdK
SQL>
D&I,Z*G{s7u)pITPUB个人空间不应该出现下面这一组啊.
#K/Y+\v,e5o2                    A
zaQG8G-s*UITPUB个人空间2                    B
7aH        C5|,D\u
K

4E"{W{[:lc
P3a|IV \:M
ao;X4`ITPUB个人空间确如你所说，解决起来也简单，外面再加一层就是：
6R
r(jr+}x+R*x[php]space.itpub.net'L6d Tgu(f0ti_._
SQL> select * from crosstable;
a3b#K*Q-~space.itpub.net JJ ?Qi5^.Qwg2kM
SUMID                DIMIDG9K `0Euq*Df
-------------------- --------------------ITPUB个人空间f!SZ GP%gw
E
1                    Aspace.itpub.net1CBYV+d }!pvp
Q%^
1                    B;N'^``5tC
1                    Cspace.itpub.net7si.b8v_ n
1                    D
4\DmtBi g.n9n.}#[2                    A
"@t4?v1E;G W8jygoracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net2                    B
T&W yD,D2~C2                    CITPUB个人空间)j        M3L+@{Nn2Y`
3                    A$w:f2z_3g3V
3                    B
E3f~$V-m&F-B
Z4                    Aspace.itpub.net8Zhj8vR
3                    D
d8q+io5SK)Xspace.itpub.net oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.netW
F MpSR)s T
11 rows selected
Rrv-B3VSa+O8_ l'J-l#sN
SQL> oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.netBZF G"a.U:u%Ww
SQL> select sumid, dimid'c9P]%o Bw&H0Q1Qa
  2    from (select b.*, count(sumid) over(partition by sumid order by sumid) nct&u GB8@4`"r {*xs
  3            from (SELECT a.*,space.itpub.net]c hS1WD.d
  4                         count(sumid) over(partition by sumid order by sumid) ctspace.itpub.net5j^\}        n.a
  5                    FROM crosstable a) bITPUB个人空间(?5d{G8^)TbT
  6           where dimid in ('A', 'B', 'D')
dA)I$x"y_ T$d  7             and ct = 3) c
(KO9oT1|1Wspace.itpub.net  8   where nct = 3oracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.netj;s        E4Q V;m mVL
  9  ;
E?~H5|&`ITPUB个人空间
TGZ&t3V9dN%WSUMID                DIMID
!~o!lt][(VW-------------------- --------------------
2fa Srx{:S3e0\3                    B
R&F(H        A:F~gR3                    D

h~.yT~"{lg9t3N7Ioracle,db2,sqlserver,sybase,mysql,erp,scm,sap,java,.net3                    A
$y'U)^;N[X MC9G
G3NT        L9||[Tspace.itpub.netSQL> space.itpub.net;Q.[3j/y;B[0a
--
_.W,YpDJF[/php]
不胜人生一场醉 bq_wang 发布于2008-03-05 17:34:33
呵呵，见笑了；分析函数一直没学会，改日真要学习一下了
y_;sh?l;G \
4YqN_x
{i8O3Wz谢谢三思
我来说两句

(可选)

日历

« 2008-07-24  
日 一 二 三 四 五 六
  12345
6789101112
13141516171819
20212223242526
2728293031  

数据统计

  • 访问量: 18737
  • 日志数: 64
  • 建立时间: 2007-12-07
  • 更新时间: 2008-07-24

RSS订阅

Open Toolbar