它山之石可以攻玉，这一篇是读别人的博客后写下的，不是原原本本的转载，加入了自己的分析过程和演练。sql语句可以解决很多的复杂业务，避免过多的项目代码，下面几个语句很值得玩味。
1.
已经知道原表
year salary
2000 1000
2001 2000
2002 3000
2003 4000
怎么查询的到下面的结果，就是累积工资
year salary
2000 1000
2001 3000
2002 6000
2003 10000
思路：这个需要两个表交叉查询得到当前年的所有过往年，然后再对过往年进行聚合。代码如下：
create
table
#salary(years
int
,salary
int
)
insert
into
#salary
values
(
2000
,
1000
),
(
2001
,
2000
),
(
2002
,
3000
),
(
2003
,
4000
)
select
b.years,
SUM
(a.salary)
from
#salary a,#salary b
where
a.years
<=
b.years
group
by
b.years
order
by
b.years
还有一种方法是使用子查询，第一列是年，第二列是所有小于等于第一列这年的工资总和，也比较直接，代码如下：
select
s1.years
as
years,
(
select
sum
(s2.salary)
from
#salary s2
where
s2.years
<=
s1.years)
as
salary
from
#salary s1
2. 现在我们假设只有一个 table ，名为 pages ，有四个字段， id, url,title,body 。里面储存了很多网页，网页的 url 地址， title 和网页的内容，然后你用一个 sql 查询将 url 匹配的排在最前， title 匹配的其次， body 匹配最后，没有任何字段匹配的，不返回。
思路：做过模糊搜索对这个应该很熟悉的，可以使用 union all 依次向一个临时表中添加记录。这里使用 order by 和 charindex 来是实现，代码如下：
create
table
#page(id
int
, url
varchar
(
100
),title
varchar
(
100
), body
varchar
(
100
))
insert
into
#page
values
(
1
,
null
,
'
abcde
'
,
'
abcde
'
),
(
2
,
null
,
'
abcde
'
,
null
),
(
3
,
'
abcde
'
,
'
e
'
,
null
)
select
*
from
#page
where
url
like
'
%e%
'
or
title
like
'
%e%
'
or
body
like
'
%e%
'
order
by
case
when
(
charindex
(
'
e
'
, url)
>
0
)
then
1
else
0
end
desc
,
case
when
(
charindex
(
'
e
'
, title)
>
0
)
then
1
else
0
end
desc
,
case
when
(
charindex
(
'
e
'
, body)
>
0
)
then
1
else
0
end
desc
只要出现一次就会排在前面，这种情况如果两行都出现就会比较下一个字段，以此类推。
还有一种实现，类似于记分牌的思想，如下：
select
a.
[
id
]
,
sum
(a.mark)
as
summark
from
(
select
#page.
*
,
10
as
mark
from
#page
where
#page.
[
url
]
like
'
%b%
'
union
select
#page.
*
,
5
as
mark
from
#page
where
#page.
[
title
]
like
'
%b%
'
union
select
#page.
*
,
1
as
mark
from
#page
where
#page.
[
body
]
like
'
%b%
'
)
as
a
group
by
id
order
by
summark
desc
3.
表内容：
2005-05-09
胜
2005-05-09
胜
2005-05-09
负
2005-05-09
负
2005-05-10
胜
2005-05-10
负
2005-05-10
负
如果要生成下列结果
,
该如何写
sql
语句
?
胜
负
2005-05-09 2 2
2005-05-10 1 2
思路：首先要有 group by 时间，然后是使用 sum 统计胜负的个数。代码如下：
create
table
#scores(dates
varchar
(
10
),score
varchar
(
2
))
insert
into
#scores
values
(
'
2005-05-09
'
,
'
胜
'
),
(
'
2005-05-09
'
,
'
胜
'
),
(
'
2005-05-09
'
,
'
负
'
),
(
'
2005-05-09
'
,
'
负
'
),
(
'
2005-05-10
'
,
'
胜
'
),
(
'
2005-05-10
'
,
'
负
'
),
(
'
2005-05-10
'
,
'
负
'
)
select
a.dates
as
[
比赛时间
]
,
SUM
(
case
a.score
when
'
胜
'
then
1
else
0
end
)
as
[
胜
]
,
SUM
(
case
a.score
when
'
负
'
then
1
else
0
end
)
as
[
负
]
from
#scores a
group
by
a.dates
还有一种方法是使用子查询，先用两个子查询得到这些日期中的胜负常数，然后连接查询，代码如下：
select
t1.dates
as
[
比赛时间
]
,
t1.score
as
[
胜
]
,
t2.score
as
[
负
]
from
(
select
a.dates
as
dates,
COUNT
(
1
)
as
score
from
#scores a
where
a.score
=
'
胜
'
group
by
a.dates) t1
inner
join
(
select
a.dates
as
dates,
COUNT
(
1
)
as
score
from
#scores a
where
a.score
=
'
负
'
group
by
a.dates) t2
on
t1.dates
=
t2.dates
4. 表中有 A B C 三列 , 用 SQL 语句实现：当 A 列大于 B 列时选择 A 列否则选择 B 列，当 B 列大于 C 列时选择 B 列否则选择 C 列
思路：这个字面意思很简单了，就是二者选其一，使用 case 就可以实现，代码如下：
create
table
#table3(A
int
, B
int
,C
int
)
insert
into
#table3
values
(
2
,
1
,
3
),
(
4
,
2
,
5
)
select
case
when
A
>
B
then
A
else
B
end
as
AB,
case
when
B
>
C
then
B
else
C
end
as
BC
from
#table3
5. 请用一个 sql 语句得出结果
从 table1,table2 中取出如 table3 所列格式数据，注意提供的数据及结果不准确，只是作为一个格式向大家请教。
table1
月份 部门 业绩
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8
table2
部门 部门名称
01 国内业务一部
02 国内业务二部
03 国内业务三部
04 国际业务部
table3 （ result ）
部门 部门名称 一月份 二月份 三月份
01 国内业务一部 10 null null
02 国内业务二部 10 8 null
03 国内业务三部 null 5 8
04 国际业务部 null null 9
思路：又是行列转换，不过这个稍微复杂一点代码如下：
create
table
#table4(
[
月份
]
varchar
(
10
),
[
部门
]
varchar
(
10
),
[
业绩
]
int
)
insert
into
#table4
values
(
'
一月份
'
,
'
01
'
,
'
10
'
),
(
'
一月份
'
,
'
02
'
,
'
10
'
),
(
'
一月份
'
,
'
03
'
,
'
5
'
),
(
'
二月份
'
,
'
02
'
,
'
8
'
),
(
'
二月份
'
,
'
04
'
,
'
9
'
),
(
'
三月份
'
,
'
03
'
,
'
8
'
)
create
table
#table5(
[
部门
]
varchar
(
10
),
[
部门名称
]
varchar
(
50
))
insert
into
#table5
values
(
'
01
'
,
'
国内业务一部
'
),
(
'
02
'
,
'
国内业务二部
'
),
(
'
03
'
,
'
国内业务三部
'
),
(
'
04
'
,
'
国际业务部
'
)
select
[
部门
]
,
[
部门名称
]
,
[
一月份
]
,
[
二月份
]
,
[
三月份
]
from
(
select
a.
[
月份
]
,a.
[
部门
]
as
[
部门
]
,b.
[
部门名称
]
,a.
[
业绩
]
from
#table4 a
join
#table5 b
on
a.
[
部门
]
=
b.
[
部门
]
) sod
pivot(
min
(sod.
[
业绩
]
)
for
sod.
[
月份
]
in
(
[
一月份
]
,
[
二月份
]
,
[
三月份
]
)) pvt
order
by
[
部门
]
注意，这里每个月份每个部门只有一行数据，所以 pivot 运算的时候可以使用 min 函数，使用 max ， min 都可以。如果这里有多行数据，那么一般会让计算合计，只能用 sum 了
还有一种方法是使用子查询，这个代码要多一点，如下：
select
a.
[
部门
]
,b.
[
部门名称
]
,
SUM
(
case
when
a.月份
=
'
一月份
'
then
a.
[
业绩
]
else
0
end
)
as
[
一月份
]
,
SUM
(
case
when
a.月份
=
'
二月份
'
then
a.
[
业绩
]
else
0
end
)
as
[
二月份
]
,
SUM
(
case
when
a.月份
=
'
三月份
'
then
a.
[
业绩
]
else
0
end
)
as
[
三月份
]
from
#table4 a
inner
join
#table5 b
on
a.
[
部门
]
=
b.
[
部门
]
group
by
a.
[
部门
]
,b.
[
部门名称
]
6. 表结构以及数据如下：
CREATE TABLE #table6
(ID int, 日期 varchar(11), 单据 char(3))
INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 1 , '2004-08-02' , '001' );
INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 2 , '2004-09-02' , '001' );
INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 3 , '2004-10-02' , '002' );
INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 4 , '2004-09-02' , '002' );
要求 : 设计一个查询，返回结果如下：
ID 日期 单据
1 2004-08-02 001
4 2004-09-02 002
思路：这个是要找到日期比较小的那一条单据，这个有多种方法实现。第一种方法是相关子查询，如下：
create
table
#table6
(id
int
, 日期varchar(
11
), 单据char(
3
))
insert
into
#table6 (id , 日期, 单据)
values
(
1
,
'
2004-08-02
'
,
'
001
'
);
insert
into
#table6 (id , 日期, 单据)
values
(
2
,
'
2004-09-02
'
,
'
001
'
);
insert
into
#table6 (id , 日期, 单据)
values
(
3
,
'
2004-10-02
'
,
'
002
'
);
insert
into
#table6 (id , 日期, 单据)
values
(
4
,
'
2004-09-02
'
,
'
002
'
);
select
*
from
#table6 a
where
a.
[
日期
]
=
(
select
MIN
(b.
[
日期
]
)
from
#table6 b
where
b.
[
单据
]
=
a.
[
单据
]
)
还可以使用 join 连接，如下：
select
a.
*
from
#table6 a
join
(
select
b.
[
单据
]
,
MIN
(b.
[
日期
]
)
as
[
日期
]
from
#table6 b
group
by
b.
[
单据
]
) c
on
a.
[
日期
]
=
c.
[
日期
]
and
a.
[
单据
]
=
c.
[
单据
]
注意最后 on 条件必须是 a.[ 日期 ] = c.[ 日期 ] and a.[ 单据 ] = c.[ 单据 ] ，因为 c 表只是找出来两组符合条件的数据，如果只是 a.[ 日期 ] = c.[ 日期 ] 的话会找出多条不符合要求的数据。
还可以不 使用 join 连接，如下：
select
a.
*
from
#table6 a ,
(
select
b.
[
单据
]
,
MIN
(b.
[
日期
]
)
as
[
日期
]
from
#table6 b
group
by
b.
[
单据
]
) c
where
a.
[
日期
]
=
c.
[
日期
]
and
a.
[
单据
]
=
c.
[
单据
]
还可以使用谓词 exist ，如下：
select
*
from
#table6 a
where
not
exists
(
select
1
from
#table6
where
[
单据
]
=
a.
[
单据
]
and
a.
[
日期
]
>
[
日期
]
)
注意not exists 查询筛选得到时间最小的那条记录，注意这里不能使用 exists ， exists 会得到多条。可以理解为 a 中的日期不会大于子查询中所有日期，就是那个最小的日期。还有去掉[单据]=a.[单据],也会得到更多的数据，这个和普通的情况刚好相反。因为加上这个条件整个子查询会得到更多的数据，否则只保留a.[日期]>[日期]只会得到一条数据。
7. 已知下面的表
id strvalue type
1 how 1
2 are 1
3 you 1
4 fine 2
5 thank 2
6 you 2
要求用 sql 把它们搜索出来成为这样的
#how are you#fine thank you#
思路：这个和上一篇中的最后一题很相似，也是连接有相同字段的字符，上回使用游标实现的，这次用 for xml 来实现，代码如下：
create
table
#table7(id
int
,strvalue
varchar
(
20
),typ
int
)
insert
into
#table7
values
(
1
,
'
how
'
,
1
),
(
2
,
'
are
'
,
1
),
(
3
,
'
you
'
,
1
),
(
4
,
'
fine
'
,
2
),
(
5
,
'
thank
'
,
2
),
(
6
,
'
you
'
,
2
)
select
*
from
#table7
select
(
select
'
#
'
+
replace
(
replace
((
select
strvalue
from
#table7 t
where
typ
=
1
for
xml auto),
'
<t strvalue="
'
,
'
'
),
'
"/>
'
,
'
'
)
+
'
#
'
)
+
(
select
replace
(
replace
((
select
strvalue
from
#table7 t
where
typ
=
2
for
xml auto),
'
<t strvalue="
'
,
'
'
),
'
"/>
'
,
'
'
)
+
'
#
'
)
或者这样
select
'
#
'
+
ltrim
((
select
'
'
+
a.strvalue
from
#table7 a
where
a.typ
=
1
for
xml path(
''
)))
+
'
#
'
+
ltrim
((
select
'
'
+
a.strvalue
from
#table7 a
where
a.typ
=
2
for
xml path(
''
)))
+
'
#
'
或者这样，用变量来处理
declare
@value
varchar
(
1000
)
=
'
#
'
select
@value
=
''
+
@value
+
a.strvalue
+
'
'
from
#table7 a
where
a.typ
=
1
select
@value
=
@value
+
'
#
'
select
@value
=
@value
+
a.strvalue
+
'
'
from
#table7 a
where
a.typ
=
2
select
@value
=
@value
+
'
#
'
print
@value
for xml 是好东西啊，是解决这类字符连接问题的利刃
