30.MS_SQL 2008 - 서브쿼리문 [3]

--주소가'부산'인사람의나이보다크거나같은데이터출력

Select *From Members

where Age >= (Select Age From Members Where Address = '부산')

Go --에러: 여러개의값을반환하지못함

--위구문을개선: Or(Any, Some) 연산, And(All) 연산

--Any 구문사용

Select *From Members

Where

       Age >= Any(Select Age From Members Where Address = '부산')

Go --30, 100 출력

 

--All 구문사용

Select *From Members

Where

       Age >= All(Select Age From Members Where Address='부산')

Go --100

 

--Scalar 값이아닌다중레코드값은In 구문사용

Select *From Members

Where Age In (Select Age From Members Where Address = '부산')

Go

 

--[6] 테이블삭제

Drop Table dbo.Member

Go

 

--[!]나이가평균이하인사람출력

Select *From Members Where Age <= (Select Avg(Age) From Members)

Go

---

30SubTSQL(2).sql