SQL 年龄段品牌分类分组统计代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 SQL 年龄段品牌分类分组统计
作者: / 发布于2011/3/15/ 546
<div><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">> 测试数据：[tb]</span><span style="color: rgb(0,128,128)">
</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)"> </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)">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)"> </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(255,0,0)">[</span><span style="color: rgb(255,0,0)">姓名</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)"> </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">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)">部门</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)"> </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">4</span><span style="color: rgb(0,0,0)">),</span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">学历</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)"> </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">4</span><span style="color: rgb(0,0,0)">),</span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">出生年月</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">datetime</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(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)">select</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(255,0,0)">'</span><span style="color: rgb(255,0,0)">后勤</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">高中</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">1986-1-1</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(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(255,0,0)">'</span><span style="color: rgb(255,0,0)">后勤</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">初中</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">1984-3-7</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(255,0,0)">'</span><span style="color: rgb(255,0,0)">C</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">管理</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">本科</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">1987-2-1</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(255,0,0)">'</span><span style="color: rgb(255,0,0)">D</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">操作</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">专科</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">1976-2-1</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(255,0,0)">'</span><span style="color: rgb(255,0,0)">E</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">操作</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">专科</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">1943-2-1</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,255)">declare</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@sql</span><span style="color: rgb(0,0,0)"> </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">8000</span><span style="color: rgb(0,0,0)">)
</span><span style="color: rgb(0,0,255)">set</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@sql</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(255,0,0)">'</span><span style="color: rgb(255,0,0)">select 部门,dbo.AgeLevel([出生年月]) as 年龄段</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(0,128,0)">@sql</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,128,0)">@sql</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(255,0,0)">'</span><span style="color: rgb(255,0,0)"> , sum(case 学历 when </span><span style="color: rgb(255,0,0)">'''</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(128,128,128)">+</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'''</span><span style="color: rgb(255,0,0)"> then 1 else 0 end) [</span><span style="color: rgb(255,0,0)">'</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(128,128,128)">+</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(255,0,0)">'</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)"> (</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">distinct</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)">as</span><span style="color: rgb(0,0,0)"> a
</span><span style="color: rgb(0,0,255)">set</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@sql</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,128,0)">@sql</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(255,0,0)">'</span><span style="color: rgb(255,0,0)"> from tb group by 部门,dbo.AgeLevel([出生年月])</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">exec</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(0,128,0)">@sql</span><span style="color: rgb(0,0,0)">) 

</span><span style="color: rgb(0,128,128)">/*</span><span style="color: rgb(0,128,128)"> 
部门 年龄段 本科 初中 高中 专科
---- ---------- ----------- ----------- ----------- -----------
管理 21－30 1 0 0 0
后勤 21－30 0 1 1 0
操作 31－40 0 0 0 1
操作 50以上 0 0 0 1

(4 行受影响)
</span><span style="color: rgb(0,128,128)">*/</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)">function</span><span style="color: rgb(0,0,0)"> AgeLevel 
</span><span style="color: rgb(0,0,255)">go</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,255)">create</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">function</span><span style="color: rgb(0,0,0)"> AgeLevel(</span><span style="color: rgb(0,128,0)">@birthday</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">datetime</span><span style="color: rgb(0,0,0)">) 
</span><span style="color: rgb(0,0,255)">returns</span><span style="color: rgb(0,0,0)"> </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">10</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)">begin</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">declare</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@AgeLevel</span><span style="color: rgb(0,0,0)"> </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">10</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(0,128,0)">@AgeLevel</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)">((</span><span style="color: rgb(255,0,255)">datediff</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">year</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(0,128,0)">@birthday</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">())</span><span style="color: rgb(128,128,128)">-</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(128,128,128)">/</span><span style="color: rgb(128,0,0); font-weight: bold">10</span><span style="color: rgb(0,0,0)">) </span><span style="color: rgb(0,0,255)">when</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(0,0,255)">then</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">21－30</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">3</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)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">31－40</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">4</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">then</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">41－50</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">else</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">50以上</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </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)">return</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@AgeLevel</span><span style="color: rgb(0,0,0)"> 
</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)">go</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)"> ,dbo.AgeLevel(</span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">出生年月</span><span style="color: rgb(255,0,0)">]</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)"> tb
</span><span style="color: rgb(0,128,128)">/*</span><span style="color: rgb(0,128,128)">
姓名 部门 学历 出生年月 年龄段
---- ---- ---- ----------------------- ----------
A 后勤 高中 1986-01-01 00:00:00.000 21－30
B 后勤 初中 1984-03-07 00:00:00.000 21－30
C 管理 本科 1987-02-01 00:00:00.000 21－30
D 操作 专科 1976-02-01 00:00:00.000 31－40
E 操作 专科 1943-02-01 00:00:00.000 50以上
</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)"> N</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">年龄段</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">=</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)">((</span><span style="color: rgb(255,0,255)">datediff</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">year</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">出生年月</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">())</span><span style="color: rgb(128,128,128)">-</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(128,128,128)">/</span><span style="color: rgb(128,0,0); font-weight: bold">10</span><span style="color: rgb(0,0,0)">) 
</span><span style="color: rgb(0,0,255)">when</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(0,0,255)">then</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">21－30</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">3</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)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">31－40</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">4</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">then</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">41－50</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">else</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">50以上</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,0,0)">), 
</span><span style="color: rgb(255,0,255)">count</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)">as</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">count</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)">group</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">by</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)">((</span><span style="color: rgb(255,0,255)">datediff</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">year</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">出生年月</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">())</span><span style="color: rgb(128,128,128)">-</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(128,128,128)">/</span><span style="color: rgb(128,0,0); font-weight: bold">10</span><span style="color: rgb(0,0,0)">) 
</span><span style="color: rgb(0,0,255)">when</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(0,0,255)">then</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">21－30</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">3</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)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">31－40</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">4</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">then</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">41－50</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">else</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">50以上</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,0,0)"> )
</span><span style="color: rgb(0,128,128)">/*</span><span style="color: rgb(0,128,128)">
年龄段 count
------ -----------
21－30 3
31－40 1
50以上 1

(3 行受影响)
</span><span style="color: rgb(0,128,128)">*/</span><span style="color: rgb(0,0,0)">





</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">以10岁为递增</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(255,0,255)">cast</span><span style="color: rgb(0,0,0)">(f1</span><span style="color: rgb(128,128,128)">*</span><span style="color: rgb(128,0,0); font-weight: bold">10</span><span style="color: rgb(128,128,128)">+</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(0,0,255)">as</span><span style="color: rgb(0,0,0)"> </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">3</span><span style="color: rgb(0,0,0)">))</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">-</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,255)">cast</span><span style="color: rgb(0,0,0)">(f1</span><span style="color: rgb(128,128,128)">*</span><span style="color: rgb(128,0,0); font-weight: bold">10</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(128,0,0); font-weight: bold">10</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)">varchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">3</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)"> 年龄段,f2 </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)"> 
(
</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">datediff</span><span style="color: rgb(0,0,0)">(d,</span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">出生年月</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">())</span><span style="color: rgb(128,128,128)">/</span><span style="color: rgb(128,0,0); font-weight: bold">365</span><span style="color: rgb(128,128,128)">/</span><span style="color: rgb(128,0,0); font-weight: bold">10</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)"> f1,
</span><span style="color: rgb(255,0,255)">count</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)">as</span><span style="color: rgb(0,0,0)"> f2 
</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)">group</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">by</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">datediff</span><span style="color: rgb(0,0,0)">(d,</span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">出生年月</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">())</span><span style="color: rgb(128,128,128)">/</span><span style="color: rgb(128,0,0); font-weight: bold">365</span><span style="color: rgb(128,128,128)">/</span><span style="color: rgb(128,0,0); font-weight: bold">10</span><span style="color: rgb(0,0,0)">) a 
</span><span style="color: rgb(0,0,255)">order</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">by</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">cast</span><span style="color: rgb(0,0,0)">(f1</span><span style="color: rgb(128,128,128)">*</span><span style="color: rgb(128,0,0); font-weight: bold">10</span><span style="color: rgb(128,128,128)">+</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(0,0,255)">as</span><span style="color: rgb(0,0,0)"> </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">3</span><span style="color: rgb(0,0,0)">))</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">-</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,255)">cast</span><span style="color: rgb(0,0,0)">(f1</span><span style="color: rgb(128,128,128)">*</span><span style="color: rgb(128,0,0); font-weight: bold">10</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(128,0,0); font-weight: bold">10</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)">varchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">3</span><span style="color: rgb(0,0,0)">)) 
</span><span style="color: rgb(0,128,128)">/*</span><span style="color: rgb(0,128,128)">
年龄段 人数
------- -----------
21-30 3
31-40 1
61-70 1

(3 行受影响)
</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)"> 
</span><span style="color: rgb(255,0,255)">SUM</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)"> </span><span style="color: rgb(0,0,255)">WHEN</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">datediff</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">year</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">出生年月</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">()) </span><span style="color: rgb(128,128,128)">BETWEEN</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">16</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)"> </span><span style="color: rgb(128,0,0); font-weight: bold">20</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)"> </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(0,0,255)">ELSE</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">0</span><span style="color: rgb(0,0,0)"> </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(255,0,0)">'</span><span style="color: rgb(255,0,0)">16-20</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">, 
</span><span style="color: rgb(255,0,255)">SUM</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)"> </span><span style="color: rgb(0,0,255)">WHEN</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">datediff</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">year</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">出生年月</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">()) </span><span style="color: rgb(128,128,128)">BETWEEN</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">21</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)"> </span><span style="color: rgb(128,0,0); font-weight: bold">30</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)"> </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(0,0,255)">ELSE</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">0</span><span style="color: rgb(0,0,0)"> </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(255,0,0)">'</span><span style="color: rgb(255,0,0)">21-30</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">, 
</span><span style="color: rgb(255,0,255)">SUM</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)"> </span><span style="color: rgb(0,0,255)">WHEN</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">datediff</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">year</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">出生年月</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">()) </span><span style="color: rgb(128,128,128)">BETWEEN</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">31</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)"> </span><span style="color: rgb(128,0,0); font-weight: bold">40</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)"> </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(0,0,255)">ELSE</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">0</span><span style="color: rgb(0,0,0)"> </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(255,0,0)">'</span><span style="color: rgb(255,0,0)">31-40</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">, 
</span><span style="color: rgb(255,0,255)">SUM</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)"> </span><span style="color: rgb(0,0,255)">WHEN</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">datediff</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">year</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">出生年月</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">()) </span><span style="color: rgb(128,128,128)">BETWEEN</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">41</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)"> </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)">THEN</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(0,0,255)">ELSE</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">0</span><span style="color: rgb(0,0,0)"> </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(255,0,0)">'</span><span style="color: rgb(255,0,0)">41-50</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,
</span><span style="color: rgb(255,0,255)">SUM</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)"> </span><span style="color: rgb(0,0,255)">WHEN</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">datediff</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">year</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">出生年月</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">()) </span><span style="color: rgb(128,128,128)">BETWEEN</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">51</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)"> </span><span style="color: rgb(128,0,0); font-weight: bold">60</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)"> </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(0,0,255)">ELSE</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">0</span><span style="color: rgb(0,0,0)"> </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(255,0,0)">'</span><span style="color: rgb(255,0,0)">51-60</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">, 
</span><span style="color: rgb(255,0,255)">SUM</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)"> </span><span style="color: rgb(0,0,255)">WHEN</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">datediff</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">year</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">出生年月</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">()) </span><span style="color: rgb(128,128,128)">BETWEEN</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">61</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)"> </span><span style="color: rgb(128,0,0); font-weight: bold">70</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)"> </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(0,0,255)">ELSE</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">0</span><span style="color: rgb(0,0,0)"> </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(255,0,0)">'</span><span style="color: rgb(255,0,0)">61-70</span><span style="color: rgb(255,0,0)">'</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,128,128)">/*</span><span style="color: rgb(0,128,128)">
16-20 21-30 31-40 41-50 51-60 61-70
----------- ----------- ----------- ----------- ----------- -----------
0 3 1 0 0 1

(1 行受影响)
</span><span style="color: rgb(0,128,128)">*/</span><span style="color: rgb(0,0,0)">
</span>










<div><span style="color: rgb(0,0,255)">create</span> <span style="color: rgb(0,0,255)">table</span><span style="color: rgb(0,0,0)"> brands(id </span><span style="color: rgb(0,0,255)">int</span><span style="color: rgb(0,0,0)">,brand </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">10</span><span style="color: rgb(0,0,0)">), address </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">10</span><span style="color: rgb(0,0,0)">)) 
</span><span style="color: rgb(0,0,255)">insert</span> <span style="color: rgb(0,0,255)">into</span><span style="color: rgb(0,0,0)"> brands </span><span style="color: rgb(0,0,255)">values</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)">联想</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">北京</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">) 
</span><span style="color: rgb(0,0,255)">insert</span> <span style="color: rgb(0,0,255)">into</span><span style="color: rgb(0,0,0)"> brands </span><span style="color: rgb(0,0,255)">values</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)">惠普</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">美国</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">) </span><span style="color: rgb(0,0,255)">
insert</span> <span style="color: rgb(0,0,255)">into</span><span style="color: rgb(0,0,0)"> brands </span><span style="color: rgb(0,0,255)">values</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">3</span><span style="color: rgb(0,0,0)"> ,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">神舟</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">深圳</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">) </span><span style="color: rgb(0,0,255)">
create</span> <span style="color: rgb(0,0,255)">table</span><span style="color: rgb(0,0,0)"> products(id </span><span style="color: rgb(0,0,255)">int</span><span style="color: rgb(0,0,0)">, brand </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">10</span><span style="color: rgb(0,0,0)">)) </span><span style="color: rgb(0,0,255)">
insert</span> <span style="color: rgb(0,0,255)">into</span><span style="color: rgb(0,0,0)"> products </span><span style="color: rgb(0,0,255)">values</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(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)">联想1</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">) 
</span><span style="color: rgb(0,0,255)">insert</span> <span style="color: rgb(0,0,255)">into</span><span style="color: rgb(0,0,0)"> products </span><span style="color: rgb(0,0,255)">values</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(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)">联想2</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">) 
</span><span style="color: rgb(0,0,255)">insert</span> <span style="color: rgb(0,0,255)">into</span><span style="color: rgb(0,0,0)"> products </span><span style="color: rgb(0,0,255)">values</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">3</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)">惠普1</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">) 
</span><span style="color: rgb(0,0,255)">insert</span> <span style="color: rgb(0,0,255)">into</span><span style="color: rgb(0,0,0)"> products </span><span style="color: rgb(0,0,255)">values</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">4</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)">惠普2</span><span style="color: rgb(255,0,0)">'</span></div>
<span style="color: rgb(0,0,0)">) </span><span style="color: rgb(0,0,255)">insert</span><span style="color: rgb(0,0,255)">into</span><span style="color: rgb(0,0,0)"> products </span><span style="color: rgb(0,0,255)">values</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">5</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)">联想3</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">) 
</span><span style="color: rgb(0,0,255)">insert</span><span style="color: rgb(0,0,255)">into</span><span style="color: rgb(0,0,0)"> products </span><span style="color: rgb(0,0,255)">values</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">6</span><span style="color: rgb(0,0,0)"> ,</span><span style="color: rgb(128,0,0); font-weight: bold">3</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">神舟1</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">)
</span><span style="color: rgb(0,0,255)">insert</span><span style="color: rgb(0,0,255)">into</span><span style="color: rgb(0,0,0)"> products </span><span style="color: rgb(0,0,255)">values</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">7</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)">联想4</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">) 
</span><span style="color: rgb(0,0,255)">go
</span><span style="color: rgb(0,0,255)">
</span>

<span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> ID</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">row_number()</span><span style="color: rgb(0,0,255)">over</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(0,0,255)">order</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">by</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">()),
b.产品数量,
a.</span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">brand</span><span style="color: rgb(255,0,0)">]</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)">address</span><span style="color: rgb(255,0,0)">]</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)"> brands a,
(</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">brand</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)">,
</span><span style="color: rgb(255,0,255)">count</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">brand</span><span style="color: rgb(255,0,0)">]</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)"> products
</span><span style="color: rgb(0,0,255)">group</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">by</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">brand</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)"> )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)">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)">b.</span><span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">brand</span><span style="color: rgb(255,0,0)">]</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">order</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">by</span><span style="color: rgb(0,0,0)"> b.产品数量 </span><span style="color: rgb(0,0,255)">desc</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)"> b.id,b1.cnt </span><span style="color: rgb(0,0,255)">as</span><span style="color: rgb(0,0,0)"> 产品数量,b.brand,b.address
</span><span style="color: rgb(0,0,255)">from</span><span style="color: rgb(0,0,0)"> brands b
</span><span style="color: rgb(128,128,128)">join</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)"> brand,</span><span style="color: rgb(255,0,255)">count</span><span style="color: rgb(0,0,0)">(brand) cnt
</span><span style="color: rgb(0,0,255)">from</span><span style="color: rgb(0,0,0)"> products
</span><span style="color: rgb(0,0,255)">group</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">by</span><span style="color: rgb(0,0,0)"> brand
) b1
</span><span style="color: rgb(0,0,255)">on</span><span style="color: rgb(0,0,0)"> b1.brand</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">b.id

id 产品数量 brand address
</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(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">4</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(128,0,0); font-weight: bold">2</span><span style="color: rgb(0,0,0)"> 惠普 美国
</span><span style="color: rgb(128,0,0); font-weight: bold">3</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(128,0,0); font-weight: bold">3</span><span style="color: rgb(0,0,0)"> 行受影响)
</span>


</div>
评论列表
本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。
试试其它关键字
同语言下
可能有用的
贡献的其它代码Label