?
它山之石可以攻玉，這一篇是讀別人的博客后寫下的，不是原原本本的轉(zhuǎn)載，加入了自己的分析過程和演練。sql語句可以解決很多的復(fù)雜業(yè)務(wù)，避免過多的項目代碼，下面幾個語句很值得玩味。
1.
已經(jīng)知道原表
year salary
2000 1000
2001 2000
2002 3000
2003 4000
怎么查詢的到下面的結(jié)果，就是累積工資
year salary
2000 1000
2001 3000
2002 6000
2003 10000
思路：這個需要兩個表交叉查詢得到當(dāng)前年的所有過往年，然后再對過往年進行聚合。代碼如下：
?
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. 現(xiàn)在我們假設(shè)只有一個 table ，名為 pages ，有四個字段， id, url,title,body 。里面儲存了很多網(wǎng)頁，網(wǎng)頁的 url 地址， title 和網(wǎng)頁的內(nèi)容，然后你用一個 sql 查詢將 url 匹配的排在最前， title 匹配的其次， body 匹配最后，沒有任何字段匹配的，不返回。
?
思路：做過模糊搜索對這個應(yīng)該很熟悉的，可以使用 union all 依次向一個臨時表中添加記錄。這里使用 order by 和 charindex 來是實現(xiàn)，代碼如下：
?
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
只要出現(xiàn)一次就會排在前面，這種情況如果兩行都出現(xiàn)就會比較下一個字段，以此類推。
還有一種實現(xiàn)，類似于記分牌的思想，如下：
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.
表內(nèi)容：
2005-05-09
勝
2005-05-09
勝
2005-05-09
負
2005-05-09
負
2005-05-10
勝
2005-05-10
負
2005-05-10
負
如果要生成下列結(jié)果
,
該如何寫
sql
語句
?
??????????????????????
勝
負
2005-05-09? 2??? 2
2005-05-10??1??? 2
思路：首先要有 group by 時間，然后是使用 sum 統(tǒng)計勝負的個數(shù)。代碼如下：
?
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
?
還有一種方法是使用子查詢，先用兩個子查詢得到這些日期中的勝負常數(shù)，然后連接查詢，代碼如下：
?
?
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 語句實現(xiàn)：當(dāng) A 列大于 B 列時選擇 A 列否則選擇 B 列，當(dāng) B 列大于 C 列時選擇 B 列否則選擇 C 列
?
思路：這個字面意思很簡單了，就是二者選其一，使用 case 就可以實現(xiàn)，代碼如下：
?
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 語句得出結(jié)果
從 table1,table2 中取出如 table3 所列格式數(shù)據(jù)，注意提供的數(shù)據(jù)及結(jié)果不準(zhǔn)確，只是作為一個格式向大家請教。
table1
月份 ????????? 部門 業(yè)績
一月份 ????? 01 ????? 10
一月份 ????? 02 ????? 10
一月份 ????? 03 ????? 5
二月份 ????? 02 ????? 8
二月份 ????? 04 ????? 9
三月份 ????? 03 ????? 8
?
table2
部門 ????? 部門名稱
01 ????? 國內(nèi)業(yè)務(wù)一部
02 ????? 國內(nèi)業(yè)務(wù)二部
03 ????? 國內(nèi)業(yè)務(wù)三部
04 ????? 國際業(yè)務(wù)部
?
table3 （ result ）
部門 部門名稱 ? 一月份 ????? 二月份 ????? 三月份
? 01 ? 國內(nèi)業(yè)務(wù)一部 ??? 10 ??????? null ????? null
? 02 ?? 國內(nèi)業(yè)務(wù)二部 ?? 10 ???????? 8 ??????? null
? ? 03 ?? 國內(nèi)業(yè)務(wù)三部 ?? null ?????? 5 ??????? 8
? 04 ?? 國際業(yè)務(wù)部 ?? null ????? null ????? 9
思路：又是行列轉(zhuǎn)換，不過這個稍微復(fù)雜一點代碼如下：
?
create
table
#table4(
[
月份
]
varchar
(
10
),
[
部門
]
varchar
(
10
),
[
業(yè)績
]
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
'
,
'
國內(nèi)業(yè)務(wù)一部
'
),
(
'
02
'
,
'
國內(nèi)業(yè)務(wù)二部
'
),
(
'
03
'
,
'
國內(nèi)業(yè)務(wù)三部
'
),
(
'
04
'
,
'
國際業(yè)務(wù)部
'
)
select
[
部門
]
,
[
部門名稱
]
,
[
一月份
]
,
[
二月份
]
,
[
三月份
]
from
(
select
a.
[
月份
]
,a.
[
部門
]
as
[
部門
]
,b.
[
部門名稱
]
,a.
[
業(yè)績
]
from
#table4 a
join
#table5 b
on
a.
[
部門
]
=
b.
[
部門
]
) sod
pivot(
min
(sod.
[
業(yè)績
]
)
for
sod.
[
月份
]
in
(
[
一月份
]
,
[
二月份
]
,
[
三月份
]
)) pvt
order
by
[
部門
]
注意，這里每個月份每個部門只有一行數(shù)據(jù)，所以 pivot 運算的時候可以使用 min 函數(shù)，使用 max ， min 都可以。如果這里有多行數(shù)據(jù)，那么一般會讓計算合計，只能用 sum 了
還有一種方法是使用子查詢，這個代碼要多一點，如下：
?
?
select
a.
[
部門
]
,b.
[
部門名稱
]
,
SUM
(
case
when
a.月份
=
'
一月份
'
then
a.
[
業(yè)績
]
else
0
end
)
as
[
一月份
]
,
SUM
(
case
when
a.月份
=
'
二月份
'
then
a.
[
業(yè)績
]
else
0
end
)
as
[
二月份
]
,
SUM
(
case
when
a.月份
=
'
三月份
'
then
a.
[
業(yè)績
]
else
0
end
)
as
[
三月份
]
from
#table4 a
inner
join
#table5 b
on
a.
[
部門
]
=
b.
[
部門
]
group
by
a.
[
部門
]
,b.
[
部門名稱
]
6. 表結(jié)構(gòu)以及數(shù)據(jù)如下：
?
CREATE TABLE #table6
?
(ID int, 日期 varchar(11), 單據(jù) char(3))
?
INSERT INTO 表 (ID , 日期 , 單據(jù) ) VALUES ( 1 , '2004-08-02' , '001' );
?
INSERT INTO 表 (ID , 日期 , 單據(jù) ) VALUES ( 2 , '2004-09-02' , '001' );
?
INSERT INTO 表 (ID , 日期 , 單據(jù) ) VALUES ( 3 , '2004-10-02' , '002' );
?
INSERT INTO 表 (ID , 日期 , 單據(jù) ) VALUES ( 4 , '2004-09-02' , '002' );
?
要求 : 設(shè)計一個查詢，返回結(jié)果如下：
?
ID 日期 ?????? 單據(jù)
?
1 2004-08-02 001
?
4 2004-09-02 002
?
思路：這個是要找到日期比較小的那一條單據(jù)，這個有多種方法實現(xiàn)。第一種方法是相關(guān)子查詢，如下：
?
create
table
#table6
(id
int
, 日期varchar(
11
), 單據(jù)char(
3
))
insert
into
#table6 (id , 日期, 單據(jù))
values
(
1
,
'
2004-08-02
'
,
'
001
'
);
insert
into
#table6 (id , 日期, 單據(jù))
values
(
2
,
'
2004-09-02
'
,
'
001
'
);
insert
into
#table6 (id , 日期, 單據(jù))
values
(
3
,
'
2004-10-02
'
,
'
002
'
);
insert
into
#table6 (id , 日期, 單據(jù))
values
(
4
,
'
2004-09-02
'
,
'
002
'
);
select
*
from
#table6 a
where
a.
[
日期
]
=
(
select
MIN
(b.
[
日期
]
)
from
#table6 b
where
b.
[
單據(jù)
]
=
a.
[
單據(jù)
]
)
還可以使用 join 連接，如下：
select
a.
*
from
#table6 a
join
(
select
b.
[
單據(jù)
]
,
MIN
(b.
[
日期
]
)
as
[
日期
]
from
#table6 b
group
by
b.
[
單據(jù)
]
) c
on
a.
[
日期
]
=
c.
[
日期
]
and
a.
[
單據(jù)
]
=
c.
[
單據(jù)
]
注意最后 on 條件必須是 a.[ 日期 ] = c.[ 日期 ] and a.[ 單據(jù) ] = c.[ 單據(jù) ] ，因為 c 表只是找出來兩組符合條件的數(shù)據(jù)，如果只是 a.[ 日期 ] = c.[ 日期 ] 的話會找出多條不符合要求的數(shù)據(jù)。
還可以不 使用 join 連接，如下：
select
a.
*
from
#table6 a ,
(
select
b.
[
單據(jù)
]
,
MIN
(b.
[
日期
]
)
as
[
日期
]
from
#table6 b
group
by
b.
[
單據(jù)
]
) c
where
a.
[
日期
]
=
c.
[
日期
]
and
a.
[
單據(jù)
]
=
c.
[
單據(jù)
]
還可以使用謂詞 exist ，如下：
select
*
from
#table6 a
where
not
exists
(
select
1
from
#table6
where
[
單據(jù)
]
=
a.
[
單據(jù)
]
and
a.
[
日期
]
>
[
日期
]
)
注意not exists 查詢篩選得到時間最小的那條記錄，注意這里不能使用 exists ， exists 會得到多條?？梢岳斫鉃? a 中的日期不會大于子查詢中所有日期，就是那個最小的日期。還有去掉[單據(jù)]=a.[單據(jù)],也會得到更多的數(shù)據(jù)，這個和普通的情況剛好相反。因為加上這個條件整個子查詢會得到更多的數(shù)據(jù)，否則只保留a.[日期]>[日期]只會得到一條數(shù)據(jù)。
?
?
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#
?
思路：這個和上一篇中的最后一題很相似，也是連接有相同字段的字符，上回使用游標(biāo)實現(xiàn)的，這次用 for xml 來實現(xiàn)，代碼如下：
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 是好東西啊，是解決這類字符連接問題的利刃
?
?
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
