代码语言
.
CSharp
.
JS
Java
Asp.Net
C
MSSQL
PHP
Css
PLSQL
Python
Shell
EBS
ASP
Perl
ObjC
VB.Net
VBS
MYSQL
GO
Delphi
AS
DB2
Domino
Rails
ActionScript
Scala
代码分类
文件
系统
字符串
数据库
网络相关
图形/GUI
多媒体
算法
游戏
Jquery
Extjs
Android
HTML5
菜单
网页交互
WinForm
控件
企业应用
安全与加密
脚本/批处理
开放平台
其它
【
MSSQL
】
SQL 年龄段 品牌分类 分组统计
作者:
/ 发布于
2011/3/15
/
575
<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>
试试其它关键字
同语言下
.
SQL查询 多列合并成一行用逗号隔开
.
查看存储过程修改时间,最近执行时间
.
设置手动批量删除数据库相关进程
.
获取某个表中特定字段的所有字符串形式
.
SQL 如何去除重复的字符串
.
怎么去掉一个字段中的重复数据
.
String 去除空格 回车 换行 水平制表符
.
SQL查询和替换含有回车,空格,TAB
.
SQL SERVER 查询每日新增用户数量、次留数量
.
判断两个字符串是否存在相同的内容
可能有用的
.
SQL查询 多列合并成一行用逗号隔开
.
查看存储过程修改时间,最近执行时间
.
设置手动批量删除数据库相关进程
.
获取某个表中特定字段的所有字符串形式
.
SQL 如何去除重复的字符串
.
怎么去掉一个字段中的重复数据
.
String 去除空格 回车 换行 水平制表符
.
SQL查询和替换含有回车,空格,TAB
.
SQL SERVER 查询每日新增用户数量、次留数量
.
判断两个字符串是否存在相同的内容
贡献的其它代码
Label
Copyright © 2004 - 2024 dezai.cn. All Rights Reserved
站长博客
粤ICP备13059550号-3