sql server分組查詢示例

sql server分組查詢示例[@more@]

create table Guest_info
(
GuestID int primary key,
GuestName varchar(50),
birthday datetime
)

create table Or_info
(
Order_id int identity(1,1) primary key,
GuestID int foreign key REFERENCES Guest_info(GuestID),
Isyes int -- 0:notOK 1:Ok 2:Cancel 3:Renege
)

select c.age,
TotalCount=count(*),
notOKCount=sum(case c.Isyes when 0 then 1 else 0 end) ,
cancelCount=sum(case c.Isyes when 2 then 1 else 0 end) ,
RenegeCount=sum(case c.Isyes when 3 then 1 else 0 end) ,
OKCount=sum(case c.Isyes when 1 then 1 else 0 end)
from
(select
age = case
when datediff(yy,cast(a.birthday as datetime),getdate()) >50
then '50以上'
when datediff(yy,cast(a.birthday as datetime),getdate()) between 41 and 50
then '41-50'
when datediff(yy,cast(a.birthday as datetime),getdate()) between 31 and 40
then '31-40'
when datediff(yy,cast(a.birthday as datetime),getdate()) between 20 and 30
then '20-30'
else
'20以下'
end,
b.*
from
Guest_info a inner join
Or_info b
on
a.GuestID = b.GuestID) c
group by c.age