?
它山之石可以攻玉,這一篇是讀別人的博客后寫下的,不是原原本本的轉(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元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
