亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

SQL點滴26—常見T-SQL面試解析

系統(tǒng) 1623 0
原文: SQL點滴26—常見T-SQL面試解析

?

它山之石可以攻玉,這一篇是讀別人的博客后寫下的,不是原原本本的轉(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 是好東西啊,是解決這類字符連接問題的利刃

?






?

?

SQL點滴26—常見T-SQL面試解析


更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯(lián)系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦?。?!

發(fā)表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 久插视频| 97在线播放 | 欧美亚洲欧美日韩中文二区 | 天天干在线观看 | 黄色不良视频 | 亚洲国产精品综合欧美 | 欧美大片a一级毛片视频 | 国产麻豆视频在线看网站 | 精品亚洲一区二区在线播放 | 成人国产精品久久久免费 | 无遮挡又黄又爽又色的视频免费 | 亚洲欧美激情综合第一区 | 色婷婷综合久久久中文字幕 | 亚洲视频 中文字幕 | 久久社区视频 | 黄色片免费在线观看视频 | 夜夜资源 | 99久久做夜夜爱天天做精品 | 久久精品免视看国产成人2021 | 色综合视频一区二区观看 | 国产成人久久久精品一区二区三区 | 天天操操操操操 | 美女黄频视频大全免费高清 | 综合精品一区 | 色综合久久久久久久久五月性色 | 国产99在线播放 | 久久riav国产精品 | 6一10周岁毛片在线 717影院理论午夜伦八戒 | 久久久在线视频 | 色资源站 | 欧美xxxxbbbb在线播放 | 中文字幕在线永久 | 成人免费视频一区二区 | 韩国办公室激情 | 日本在线毛片视频免费看 | 亚洲综合国产精品 | 国产成人免费观看 | 操操干| 毛片永久新网址首页 | 超级乱淫视频aⅴ播放视频 超级乱淫视频播放日韩 | 亚洲精品人成在线观看 |