根据不同的条件查询不同的表代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】根据不同的条件查询不同的表
作者:曾祥展 / 发布于2011/6/30/ 754
<div>
<div id="cnblogs_post_body"><span style="color: rgb(0,128,128)">/*</span><span style="color: rgb(0,128,128)">
a表 a.user_id a.name 
b表 b.user_id b.name 
c表 c.user_id c.user_type 

当c表的 c.user_type = "a" 时 它显示 a表的 a.name 
当c表的 c.user_type = "b" 时 它显示 b表的 b.name 
a,b,c表中的 a.user_id = c.user_id,b.user_id = c.user_id 

</span><span style="color: rgb(0,128,128)">*/</span><span style="color: rgb(0,0,0)">

</span><span style="color: rgb(0,0,255)">if</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">object_id</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">ta</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">)</span><span style="color: rgb(0,0,255)">is</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">not</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">null</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">drop</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">table</span><span style="color: rgb(0,0,0)"> ta
</span><span style="color: rgb(0,0,255)">go</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">create</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">TABLE</span><span style="color: rgb(0,0,0)"> ta(</span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">user_ID</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">int</span><span style="color: rgb(0,0,0)">,name </span><span style="color: rgb(0,0,255)">varchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">50</span><span style="color: rgb(0,0,0)">))
</span><span style="color: rgb(0,0,255)">INSERT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">INTO</span><span style="color: rgb(0,0,0)"> ta </span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">a1</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">union</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">all</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(128,0,0); font-weight: bold">2</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">a2</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">

</span><span style="color: rgb(0,0,255)">if</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">object_id</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">tb</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">)</span><span style="color: rgb(0,0,255)">is</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">not</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">null</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">drop</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">table</span><span style="color: rgb(0,0,0)"> tb
</span><span style="color: rgb(0,0,255)">go</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">create</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">TABLE</span><span style="color: rgb(0,0,0)"> tb(</span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">user_ID</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">int</span><span style="color: rgb(0,0,0)">,name </span><span style="color: rgb(0,0,255)">varchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">50</span><span style="color: rgb(0,0,0)">))
</span><span style="color: rgb(0,0,255)">INSERT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">INTO</span><span style="color: rgb(0,0,0)"> tb </span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">b1</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">union</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">all</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(128,0,0); font-weight: bold">2</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">b2</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">

</span><span style="color: rgb(0,0,255)">if</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">object_id</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">tc</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">)</span><span style="color: rgb(0,0,255)">is</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">not</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">null</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">drop</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">table</span><span style="color: rgb(0,0,0)"> tc
</span><span style="color: rgb(0,0,255)">go</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">create</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">TABLE</span><span style="color: rgb(0,0,0)"> tc(</span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">user_ID</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">int</span><span style="color: rgb(0,0,0)">,user_type </span><span style="color: rgb(0,0,255)">varchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">50</span><span style="color: rgb(0,0,0)">))
</span><span style="color: rgb(0,0,255)">INSERT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">INTO</span><span style="color: rgb(0,0,0)"> tc </span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">a</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">union</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">all</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(128,0,0); font-weight: bold">2</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">b</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">

</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">*</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">from</span><span style="color: rgb(0,0,0)"> ta
</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">*</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">from</span><span style="color: rgb(0,0,0)"> tb
</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">*</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">from</span><span style="color: rgb(0,0,0)"> tc

</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">方法一</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> c.</span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">user_id</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)">,name</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">case</span><span style="color: rgb(0,0,0)"> user_type </span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">a</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">then</span><span style="color: rgb(0,0,0)"> a.name </span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">b</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">then</span><span style="color: rgb(0,0,0)"> b.name </span><span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">from</span><span style="color: rgb(0,0,0)"> tc c,ta a,tb b
</span><span style="color: rgb(0,0,255)">where</span><span style="color: rgb(0,0,0)"> a.</span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">user_id</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">c.</span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">user_id</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">and</span><span style="color: rgb(0,0,0)"> b.</span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">user_id</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">c.</span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">user_id</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)"> 

</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">方法二</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> tc.</span><span style="color: rgb(255,0,255)">user_ID</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,255)">CASE</span><span style="color: rgb(0,0,0)"> user_type </span><span style="color: rgb(0,0,255)">WHEN</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">a</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">THEN</span><span style="color: rgb(0,0,0)"> ta.name </span><span style="color: rgb(0,0,255)">WHEN</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">b</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">THEN</span><span style="color: rgb(0,0,0)"> tb.name </span><span style="color: rgb(0,0,255)">END</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">AS</span><span style="color: rgb(0,0,0)"> 输出
</span><span style="color: rgb(0,0,255)">FROM</span><span style="color: rgb(0,0,0)"> tc </span><span style="color: rgb(0,0,255)">INNER</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">JOIN</span><span style="color: rgb(0,0,0)">
ta </span><span style="color: rgb(0,0,255)">ON</span><span style="color: rgb(0,0,0)"> tc.</span><span style="color: rgb(255,0,255)">user_ID</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> ta.</span><span style="color: rgb(255,0,255)">user_ID</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">INNER</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">JOIN</span><span style="color: rgb(0,0,0)">
tb </span><span style="color: rgb(0,0,255)">ON</span><span style="color: rgb(0,0,0)"> tc.</span><span style="color: rgb(255,0,255)">user_ID</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> tb.</span><span style="color: rgb(255,0,255)">user_ID</span><span style="color: rgb(0,0,0)">


</span><span style="color: rgb(0,128,128)">/*</span><span style="color: rgb(0,128,128)">

user_ID name
----------- --------------------------------------------------
1 a1
2 a2


user_ID name
----------- --------------------------------------------------
1 b1
2 b2


user_ID user_type
----------- --------------------------------------------------
1 a
2 b


user_id 输出
----------- --------------------------------------------------
1 a1
2 b2



</span><span style="color: rgb(0,128,128)">*/</span></div>
</div>
试试其它关键字
同语言下
可能有用的
曾祥展贡献的其它代码(13)