ITPub博客

首页 > 数据库 > SQL Server > sql server分组查询示例

sql server分组查询示例

原创 SQL Server 作者:kitesky 时间:2006-04-05 14:47:45 0 删除 编辑
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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/66009/viewspace-825236/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-04-22

  • 博文量
    273
  • 访问量
    2172357