SQL 查询关键字用法祥解

1 case关键字

-----case start-------------------------------------------------------------------------------------

关键字 case 

case 语法

--------1-----------------------------------------------------------------------------------------------

case <表达式A>

when <Α> then <值A>

when <表达式B> then <值B>

when <表达式C> then <值C>

else <值D>

end

----------2---------------------------------------------------------------------------------------------

case when <条件表达式1> then <值A>

when <条件表达式2> then <值B>

when <条件表达式3> then <值C>

else <值D>

end

create table tabCase (UID int ,Areaid varchar(10),price money)

insert into tabCase select 1, 'GD', 8

union select 2, 'GD', 10

union select 3, 'GX', 12

union select 4, 'GX', 14

union select 5, 'SD', 16

union select 6, 'SD', 18

union select 7, 'GX', 20

union select 8, 'SD', 30

union select 9, 'GX', 40

union select 10, 'GD', 50

select UID , Areaid , price from tabCase

select UID, case areaid when 'GD' then '广东'

when 'GX' then '广西'

when 'SD' then '山东'

end as AreaidName

,price

from tabCase

----------------------------------------------------------------------------------------------------

select UID, case areaid when 'GD' then '广东'

when 'GX' then '广西'

when 'SD' then '山东'

end as Areaid

,case when price <=10 then '超低价商品'

when price <=20 then '低价商品'

when price <=50 then '高价商品'

end as price

from tab

------------------------------------------------------------------------------------------

select Areaid

,GDCount = sum(case when areaid='GD' then 1 else 0 end )

,GXCount = sum(case when areaid='GX' then 1 else 0 end )

,SDCount = sum(case when areaid='SD' then 1 else 0 end )

from tab group by areaid

drop table tab

-----case End-------------------------------------------------------------------------------------

2 convert 关键字

----convert start----------------------------------------------------

--1 日期转换并格式化

select convert(varchar,getdate(),120) , convert(varchar,getdate(),108)

--2 其它数据类型转换

select convert(int '12'),convert(varchar,125)

----convert end----------------------------------------------------

100 豎表轉橫表

drop table tb

create table tb(UserName varchar(10) , Subject varchar(10) , Score int)

insert into tb values('張三' , '語文' , 74)

insert into tb values('張三' , '數學' , 83)

insert into tb values('張三' , '物理' , 93)

insert into tb values('李四' , '語文' , 74)

insert into tb values('李四' , '數學' , 84)

insert into tb values('李四' , '物理' , 94)

select UserName,sum(case when Subject= '物理' then Score else 0 end) [物理],sum(case when Subject= '語文' then Score else 0 end) [語文],sum(case when Subject= '數學' then Score else 0 end) [數學] from tb group by UserName

declare @sql varchar(1000)

set @sql='select UserName'

select @sql=@sql+',sum(case when Subject= ''' +Subject+ ''' then Score else 0 end) ['+Subject+']' from (select distinct Subject from tb)a

set @sql = @sql + ' from tb group by UserName'

print @sql

exec(@sql)

3 比較二個日期區（DateS1,DateE1） , (DateS2,DateE2 )間是否有交集 ,有交集返回1，否則返回0

Code

4 SQL 分頁儲存過程

Code

5 根據資料表生成新增SQL句語

Code

6 獲得表指定列的最大值加一

Code

posted on 2009-06-28 10:49 brake 阅读(2271) 评论(0) 编辑收藏举报