Blog Content

    티스토리 뷰

    41.MS_SQL 2008 - Select 기타 키워드

    반응형

    --[1]테이블생성

     

    -- 삭제하기위해다음과같이명령어사용

    -- Drop Table Customers

    Create Table dbo.Customers

    (

           Num Int Identity(1,1) Primary Key,

           Name VarChar(25) Not Null,

           Age Int Null,

           Address VarChar(100)

    )

    Go

     

    --[2]샘플데이터입력

    Insert Customers Values('홍길동','21','서울')

    Insert Customers Values('백두산','31','서울')

    Insert Customers Values('한라산','41','부산')

    Insert Customers Values('지리산','21','부산')

    Insert Customers Values('설악산','31','대전')

     

     

    --select문주요기능

    --전체고객리스트

    Select *From Customers

     

     

    --[2-1]중복제거(Distinct) : 고객들이사는지역을중복없이출력

    Select Distinct Address From Customers

     

     

    --[2-2] Group By : 집계함수그룹화: 같은지역고객의나이평균

    Select Address, AVG(Age) From Customers

    Group By Address

     

     

    --[3] 조건이있는집계함수그룹화:

    -- 같은지역고객중나이가40이상인고객의나이의평균

    Select Address, AVG(Age) From Customers

    Where Age >= 40

    Group By Address

     

     

    --Group By All : 위쿼리문중조건에맞지않아도지역리스트출력

    Select Address, AVG(Age) From Customers

    Where Age > 40

    Group By All Address

     

     

    --[4] Having :  집계함수에대한조건처리

    -- 같은지역고객의나이의평균이30이상인데이터만출력

    Select Address, AVG(Age) As 나이평균 From Customers

    Group By Address

    Having AVG(Age) >= 30

     

     

    --[5] RollUp : 소계: 지역별나이를출력후나의소계(중간합계)

    Select Address, AVG(Age) From Customers

    Group By Address With RollUp

     

     

    --[6] Cube : 소계: 지역별나이를출력후나의소계

    Select Address, AVG(Age) From Customers

    Group By Address With Cube

     

     

    --[7] Grouping() 함수: RollUp/Cube 사용시그룹화된항목인지표시

    Select Address, AVG(Age), GROUPING(Address) As 그룹화여부

    From Customers

    Group By Address With Cube

     

     

    --[8] Compute : 출력결과에대한집계

    Select Address, Age From Customers

    Compute SUM(Age), AVG(Age)

     

     

    --[9] Compute by : 출력결과에대한집계에대한정렬

    Select Address, Age From Customers Order By Address

    Compute Sum(Age), Avg(Age) By Address

     

     

    --[10] Case : 문장대체

    Select Name, Age, Address From Customers

     

    Select Name, Age,

           Address =

           Case Address

                 When '서울' Then 'Seoul'

                 When '부산' Then 'Busan'

                 Else '다른지역'

           End

    From Customers




    반응형

    Comments