一个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...
代码如下：
//创建数据表
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
您可能感兴趣的文章:
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。