Sql Server分组查询示例

wuhuif

于 2006-12-12 04:06:00 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/wuhuif/article/details/1439187

本文介绍了一种使用SQL进行数据统计的方法，通过创建两个表：客户信息表和订单状态表，并对客户的年龄进行分组，统计不同年龄段客户的订单状态（未确认、取消、违约和已确认）的数量。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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