sql.htm

Sql Server的例子

                                        <span lang="EN-US">Total_quantity</span></td>
                                      </tr>
                                      <tr>
                                        <td width="31%" valign="top" style="width: 31.58%; border-left: 1.0pt solid windowtext; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">10248</span></td>
                                        <td width="35%" valign="top" style="width: 35.1%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">11</span></td>
                                        <td valign="top" style="width: 33%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">12</span></td>
                                      </tr>
                                      <tr>
                                        <td width="31%" valign="top" style="width: 31.58%; border-left: 1.0pt solid windowtext; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">10248</span></td>
                                        <td width="35%" valign="top" style="width: 35.1%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">42</span></td>
                                        <td valign="top" style="width: 33%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">10</span></td>
                                      </tr>
                                      <tr>
                                        <td width="31%" valign="top" style="width: 31.58%; border-left: 1.0pt solid windowtext; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">10248</span></td>
                                        <td width="35%" valign="top" style="width: 35.1%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">72</span></td>
                                        <td valign="top" style="width: 33%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">5</span></td>
                                      </tr>
                                      <tr>
                                        <td width="31%" valign="top" style="width: 31.58%; border-left: 1.0pt solid windowtext; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">10249</span></td>
                                        <td width="35%" valign="top" style="width: 35.1%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">14</span></td>
                                        <td valign="top" style="width: 33%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">9</span></td>
                                      </tr>
                                      <tr>
                                        <td width="31%" valign="top" style="width: 31.58%; border-left: 1.0pt solid windowtext; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">10249</span></td>
                                        <td width="35%" valign="top" style="width: 35.1%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">51</span></td>
                                        <td valign="top" style="width: 33%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">40</span></td>
                                      </tr>
                                      <tr>
                                        <td width="31%" valign="top" style="width: 31.58%; border-left: 1.0pt solid windowtext; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">(5 
                                        row(s) affected)</span></td>
                                        <td width="35%" valign="top" style="width: 35.1%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0">
                                        <span lang="EN-US" style="font-family: 宋体">
                                        &nbsp;</span></td>
                                        <td valign="top" style="width: 33%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0">
                                        <span lang="EN-US" style="font-family: 宋体">
                                        &nbsp;</span></td>
                                      </tr>
                                    </table>
                                    <p>Rollup在全部第一行生成总汇总，再每个productid的第一行生成单个productid的汇总。<br>
                                    SQL Server处理数据的顺序是按照GROUP 
                                    BY子句指定的列表从右至左，然后对每一组使用聚合函数。 SQL 
                                    Server向显示累积合计的结果集添加行，例如运算总和。这些聚合结果在结果集中用NULL标识。<br>
                                    <b><br>
                                    SELECT</b>&nbsp; productid ,orderid, sum(quantity)
                                    <b>as</b> total_quantity,count(quantity) as 
                                    total_quantity <b>FROM</b> [order details]
                                    <b>group</b> <b>by</b> productid,orderid <b>
                                    with rollup order by</b> productid,orderid<br>
                                    　<table border="1" cellspacing="0" cellpadding="0" style="width: 51%; border-collapse: collapse; border: medium none">
                                      <tr>
                                        <td valign="top" style="width: 30%; border: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a" style="margin-left:10.5pt">
                                        <span lang="EN-US">Ordered</span></td>
                                        <td valign="top" style="width: 35%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: 1.0pt solid windowtext; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a" style="margin-left:10.5pt">
                                        <span lang="EN-US">Productid</span></td>
                                        <td valign="top" style="width: 35%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: 1.0pt solid windowtext; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a" style="margin-left:10.5pt">
                                        <span lang="EN-US">Total_quantity</span></td>
                                      </tr>
                                      <tr>
                                        <td valign="top" style="width: 30%; border-left: 1.0pt solid windowtext; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">NULL</span></td>
                                        <td valign="top" style="width: 35%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">NULL</span></td>
                                        <td valign="top" style="width: 35%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">76</span></td>
                                      </tr>
                                      <tr>
                                        <td valign="top" style="width: 30%; border-left: 1.0pt solid windowtext; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">10248</span></td>
                                        <td valign="top" style="width: 35%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">NULL</span></td>
                                        <td valign="top" style="width: 35%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">27</span></td>
                                      </tr>
                                      <tr>
                                        <td valign="top" style="width: 30%; border-left: 1.0pt solid windowtext; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">10248</span></td>
                                        <td valign="top" style="width: 35%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">11</span></td>
                                        <td valign="top" style="width: 35%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">12</span></td>
                                      </tr>
                                      <tr>
                                        <td valign="top" style="width: 30%; border-left: 1.0pt solid windowtext; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">10248</span></td>
                                        <td valign="top" style="width: 35%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">42</span></td>
                                        <td valign="top" style="width: 35%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">10</span></td>
                                      </tr>
                                      <tr>
                                        <td valign="top" style="width: 30%; border-left: 1.0pt solid windowtext; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">10248</span></td>
                                        <td valign="top" style="width: 35%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">72</span></td>
                                        <td valign="top" style="width: 35%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">5</span></td>
                                      </tr>
                                      <tr>
                                        <td valign="top" style="width: 30%; border-left: 1.0pt solid windowtext; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">10249</span></td>
                                        <td valign="top" style="width: 35%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">NULL</span></td>
                                        <td valign="top" style="width: 35%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">49</span></td>
                                      </tr>
                                      <tr>
                                        <td valign="top" style="width: 30%; border-left: 1.0pt solid windowtext; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">10249</span></td>
                                        <td valign="top" style="width: 35%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">14</span></td>
                                        <td valign="top" style="width: 35%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">9</span></td>
                                      </tr>
                                      <tr>
                                        <td valign="top" style="width: 30%; border-left: 1.0pt solid windowtext; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">10249</span></td>
                                        <td valign="top" style="width: 35%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">51</span></td>
                                        <td valign="top" style="width: 35%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">40</span></td>
                                      </tr>
                                      <tr>
                                        <td valign="top" style="width: 30%; border-left: 1.0pt solid windowtext; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0"><span lang="EN-US">(8 
                                        row(s) affected)</span></td>
                                        <td valign="top" style="width: 35%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0">
                                        <span lang="EN-US" style="font-family: 宋体">
                                        &nbsp;</span></td>
                                        <td valign="top" style="width: 35%; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding: 0cm">
                                        <p class="a0">
                                        <span lang="EN-US" style="font-family: 宋体">
                                        &nbsp;</span></td>
                                      </tr>
                                    </table>
                                    <p>
                                    <br>
                                    Cube除了含有rollup所有的东西，还要对每个组合进行一次汇总<br>
                                    <b>SELECT</b>&nbsp; productid ,orderid,sum(quantity)
                                    <b>as</b> total_quantity,count(quantity) as 
                                    total_quantity <b>FROM</b> [order details]<b> 
                                    group by</b> productid,orderid <b>with cube 
                                    order by </b>productid,orderid</td>
                                  </tr>
                                  <tr>
                                    <td width="100%" height="0" style="font-family: 宋体; font-size: 9pt; color: #003366; line-height: 150%">
                                    <u><b>10.存在语句</b></u><br>
                                    <b>SELECT</b> lastname, employeeid <b>FROM</b> 
                                    employees AS e <b>WHERE</b> <b>EXISTS</b> (
                                    <b>SELECT</b> * <b>FROM</b> orders <b>AS</b> 
                                    o <b>WHERE</b> e.employeeid = o.employeeid
                                    <b>AND</b> o.orderdate = &#39;9/5/1997&#39; )</td>
                                  </tr>
                                  <tr>
                                    <td width="100%" height="0" style="font-family: 宋体; font-size: 9pt; color: #003366; line-height: 150%">
                                    <u><b>11.Group和Having</b></u><br>
                                    <b>SELECT</b> productid, SUM(quantity) <b>AS</b> 
                                    total_quantity <b>FROM</b> orderhist <b>GROUP 
                                    BY</b> productid <b>HAVING</b> SUM(quantity) 
                                    &gt;=30</td>
                                  </tr>
                                  <tr>
                                    <td width="100%" height="0" style="font