当前位置：Gxlcms > 数据库问题 > SQL 数据库 子查询及示例

SQL 数据库 子查询及示例

时间：2021-07-01 10:21:17 帮助过：4人阅读

      将一个查询语句做为一个结果集供其他SQL语句使用，就像使用普通的表一样，被当作结果集的查询语句被称为子查询。

子查询有两种类型：

一种是只返回一个单值的子查询，这时它可以用在一个单值可以使用的地方，这时子查询可以看作是一个拥有返回值的函数；

另外一种是返回一列值的子查询，这时子查询可以看作是一个在内存中临时存在的数据表。

 

示例：

--创建一个数据库，建立一个部门表格和部门人员表格

  1. <span style="color: #0000ff;">Create</span> <span style="color: #0000ff;">database</span> gongs <span style="color: #008000;">--创建一个gongs的数据库</span>
  2. <span style="color: #0000ff;">go</span>
  3. <span style="color: #0000ff;">use</span> gongs <span style="color: #008000;">--使用这个数据库</span>
  4. <span style="color: #0000ff;">go</span>
  5. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span> bm <span style="color: #008000;">--添加一个bm的表格</span>
  6. <span style="color: #000000;">(
  7.   bbian </span><span style="color: #0000ff;">int</span> <span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span> <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span>, <span style="color: #008000;">--添加部门编号bbian 整数类型,用primary key约束来创建bbian为主键 具有唯一性,not null具有非空性</span>
  8.   bname <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span>),<span style="color: #008000;">--添加部门名字</span>
  9.   bceo <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span>),<span style="color: #008000;">--添加部门领导名字</span>
  10.   bdh <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span>),<span style="color: #008000;">--添加部门电话</span>
  11. <span style="color: #000000;">) 
  12. </span><span style="color: #0000ff;">go</span>
  13. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span> ry <span style="color: #008000;">--添加一个部门人员ry的表格</span>
  14. <span style="color: #000000;">(
  15.   bian </span><span style="color: #0000ff;">int</span> <span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span> <span style="color: #ff00ff;">identity</span>(<span style="color: #800000; font-weight: bold;">1001</span>,<span style="color: #800000; font-weight: bold;">1</span>) <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span>,<span style="color: #008000;">--添加人员编号,primary key identity(1001,1)从1开始，每次增长1，添加values时不用添加此列</span>
  16.   name <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span>),<span style="color: #008000;">--添加部门人员名字</span>
  17.   xingbie <span style="color: #0000ff;">char</span>(<span style="color: #800000; font-weight: bold;">10</span>),<span style="color: #008000;">--添加xingbie</span>
  18.   age <span style="color: #0000ff;">int</span>,<span style="color: #008000;">--添加年龄</span>
  19.   sfz <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span>),<span style="color: #008000;">--添加身份证号</span>
  20.   dh <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span>),<span style="color: #008000;">--添加电话号码</span>
  21.   bcode <span style="color: #0000ff;">int</span>,<span style="color: #008000;">--部门编号</span>
  22. <span style="color: #000000;">)
  23. </span><span style="color: #0000ff;">go</span>
  24. <span style="color: #008000;">--向部门表bm中插入数据</span>
  25. <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> bm <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">101</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">人事部</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">袁天罡</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">1234567</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  26. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> bm <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">102</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">业务部</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">李元吉</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2345678</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  27. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> bm <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">103</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">企划部</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">公孙胜</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">3456789</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  28. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> bm <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">104</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">客服部</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">冯程程</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">7654321</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  29. </span><span style="color: #008080;">--</span><span style="color: #008080;">插入人员列表信息</span>
  30. <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> ry <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">袁天罡</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">男</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">28</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">3434343434</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">121212</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">101</span><span style="color: #000000;">)
  31. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> ry <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">袁大</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">男</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">25</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">4343434343</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">212121</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">101</span><span style="color: #000000;">)
  32. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> ry <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">袁二</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">女</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">27</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">3434343434</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">112112</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">101</span><span style="color: #000000;">)
  33. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> ry <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">李元吉</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">男</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">25</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">5656565656</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">343434</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">102</span><span style="color: #000000;">)
  34. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> ry <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">李元a</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">女</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">23</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">6565656565</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">343434</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">102</span><span style="color: #000000;">)
  35. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> ry <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">李元b</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">女</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">24</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">5565565565</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">334334</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">102</span><span style="color: #000000;">)
  36. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> ry <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">公孙胜</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">男</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">28</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">1515151515</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">191919</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">103</span><span style="color: #000000;">)
  37. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> ry <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">公孙a</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">女</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">23</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">5151515151</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">919191</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">103</span><span style="color: #000000;">)
  38. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> ry <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">公孙b</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">女</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">24</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">1414141414</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">181818</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">103</span><span style="color: #000000;">)
  39. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> ry <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">公孙吉</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">男</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">25</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">4141414141</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">818181</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">103</span><span style="color: #000000;">)
  40. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> ry <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">公孙c</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">男</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">23</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2525252525</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">171717</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">103</span><span style="color: #000000;">)
  41. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> ry <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">冯程程</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">女</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">25</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">3838383838</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">202020</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">104</span><span style="color: #000000;">)
  42. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> ry <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">冯锡范</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">男</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">32</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">8383838383</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">020202</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">104</span><span style="color: #000000;">)
  43. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> ry <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">冯斯托洛夫斯基</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">男</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">20</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2929292929</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">886886</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">104</span><span style="color: #000000;">)
  44. </span><span style="color: #0000ff;">go</span>
  45. <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> bm 
  46. </span><span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> ry
  47. </span><span style="color: #008000;">--插入一条错误数据  显示一下外键是什么作用</span>
  48. <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> ry <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">甲鱼</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">女</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">26</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">234765423456222</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">9874533</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">108</span><span style="color: #000000;">)
  49. </span><span style="color: #008000;">--显示一下主键的作用</span>
  50. <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> bm <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">101</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">人事部</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">钱八</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2345652</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  51. </span><span style="color: #008000;">---查询人数最多的部门里年龄最大的人的信息</span>
  52. <span style="color: #0000ff;">select</span> bcode <span style="color: #0000ff;">from</span> ry <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> bcode <span style="color: #0000ff;">having</span> <span style="color: #ff00ff;">COUNT</span>(<span style="color: #808080;">*</span>)<span style="color: #808080;">></span><span style="color: #800000; font-weight: bold;">4</span><span style="color: #008000;">--查找人数最多的部门</span>
  53. <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">MAX</span>(age) <span style="color: #0000ff;">from</span> ry <span style="color: #0000ff;">where</span> bcode<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">103</span><span style="color: #008000;">--查找里面最大年龄</span>
  54. <span style="color: #0000ff;">select</span> bian <span style="color: #0000ff;">from</span> ry <span style="color: #0000ff;">where</span> bcode<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">103</span> <span style="color: #808080;">and</span> age <span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">28</span><span style="color: #008000;">--查找这个人的编号
  55. --子查询的方法:</span>
  56. <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> ry <span style="color: #0000ff;">where</span> bian<span style="color: #808080;">=</span><span style="color: #000000;">
  57. (</span><span style="color: #0000ff;">select</span> bian <span style="color: #0000ff;">from</span> ry <span style="color: #0000ff;">where</span> bcode<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">103</span> <span style="color: #808080;">and</span> age <span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">28</span><span style="color: #000000;">)
  58. </span><span style="color: #008000;">--按照年龄从小到大排序,取789号人员的所有信息</span>
  59. <span style="color: #0000ff;">select</span> <span style="color: #0000ff;">top</span> <span style="color: #800000; font-weight: bold;">3</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> ry <span style="color: #0000ff;">where</span> bian <span style="color: #808080;">not</span> <span style="color: #808080;">in</span><span style="color: #000000;">
  60. (</span><span style="color: #0000ff;">select</span> <span style="color: #0000ff;">top</span> <span style="color: #800000; font-weight: bold;">6</span> bian <span style="color: #0000ff;">from</span> ry <span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span> age) <span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span><span style="color: #000000;"> age
  61. </span><span style="color: #008000;">--查找所有男同志里面年龄最大的人的信息</span>
  62. <span style="color: #0000ff;">select</span><span style="color: #808080;">*</span><span style="color: #0000ff;">from</span> ry <span style="color: #0000ff;">where</span> bian<span style="color: #808080;">=</span><span style="color: #000000;">
  63. (</span><span style="color: #0000ff;">select</span> <span style="color: #0000ff;">top</span> <span style="color: #800000; font-weight: bold;">1</span> bian <span style="color: #0000ff;">from</span> ry  <span style="color: #0000ff;">where</span> age<span style="color: #808080;">=</span><span style="color: #000000;">
  64. (</span><span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">MAX</span>(age) <span style="color: #0000ff;">from</span> ry  <span style="color: #0000ff;">where</span> xingbie<span style="color: #808080;">=</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">男</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">))
  65. </span><span style="color: #008000;">--分页查询</span>
  66. <span style="color: #0000ff;">select</span> <span style="color: #0000ff;">top</span> <span style="color: #800000; font-weight: bold;">5</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> ry
  67. </span><span style="color: #008080;">--</span><span style="color: #008080;">6~10</span>
  68. <span style="color: #0000ff;">select</span> <span style="color: #0000ff;">top</span> <span style="color: #800000; font-weight: bold;">5</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> ry <span style="color: #0000ff;">where</span> bian <span style="color: #808080;">not</span> <span style="color: #808080;">in</span><span style="color: #000000;">
  69. (</span><span style="color: #0000ff;">select</span> <span style="color: #0000ff;">top</span> <span style="color: #800000; font-weight: bold;">5</span> bian <span style="color: #0000ff;">from</span><span style="color: #000000;"> ry)
  70. </span><span style="color: #008080;">--</span><span style="color: #008080;">11-14</span>
  71. <span style="color: #0000ff;">select</span> <span style="color: #0000ff;">top</span> <span style="color: #800000; font-weight: bold;">5</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> ry <span style="color: #0000ff;">where</span> bian <span style="color: #808080;">not</span> <span style="color: #808080;">in</span><span style="color: #000000;">
  72. (</span><span style="color: #0000ff;">select</span> <span style="color: #0000ff;">top</span> <span style="color: #800000; font-weight: bold;">10</span> bian <span style="color: #0000ff;">from</span><span style="color: #000000;"> ry)
  73. </span><span style="color: #008000;">--能分多少页</span>
  74. <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">ceiling</span>(( <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">COUNT</span>(<span style="color: #808080;">*</span>)<span style="color: #0000ff;">from</span> ry)<span style="color: #808080;">/</span><span style="color: #800000; font-weight: bold;">5.0</span><span style="color: #000000;">)
  75. </span><span style="color: #008000;">--将人员表显示出来   并且将部门编号变为部门名称显示</span>
  76. <span style="color: #0000ff;">select</span> name , xingbie ,(<span style="color: #0000ff;">select</span> bname <span style="color: #0000ff;">from</span> bm <span style="color: #0000ff;">where</span> bm.bbian <span style="color: #808080;">=</span> ry.bcode) <span style="color: #0000ff;">from</span> ry

 

SQL 数据库 子查询及示例

标签：

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！