Sql Server分组查询示例

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