有个订单的统计,如何写SQL

liqiyou 2005-09-27 08:58:13
比如:我的用户表中只有出生年月字段,现在统计各个年龄段的订单的预定次数,成功次数,如下表:
年龄段 预定次数 取消次数 违约次数 成功次数 失败次数
20以下 1 0      0 1    0
20-30 5  0      4 1  0
...... ...... ...... .....
各位帮帮忙喽。
用户表为Guest_info:GuestID,......
订单表为Order,字段为:GuestID,OrderID,DDatetime(下订单时间),isyes(这个是订单成功状态,如:0:预定失败1:预定成功2:取消 3违约),......
...全文
103 点赞 收藏 6
写回复
6 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
LGame 2005-09-27
帮你顶一下吧!!
回复
zouqiang122 2005-09-27
支持一下
回复
xueguang 2005-09-27
select '20以下' 年龄段 ,select sum(1) from Order where GuestID in(select GuestID from Guest_info where datediff(yy,出生年月+'.01',getdate())<=20) 预定次数,select sum(case isyes when 2 then 1 else 0 end) from Order where GuestID in(select GuestID from Guest_info where datediff(yy,出生年月+'.01',getdate())<=20) 取消次数,select sum(case isyes when 3 then 1 else 0 end) from Order where GuestID in(select GuestID from Guest_info where datediff(yy,出生年月+'.01',getdate())<=20) 违约次数,select sum(case isyes when 1 then 1 else 0 end) from Order where GuestID in(select GuestID from Guest_info where datediff(yy,出生年月+'.01',getdate())<=20) 成功次数,select sum(case isyes when 0 then 1 else 0 end) from Order where GuestID in(select GuestID from Guest_info where datediff(yy,出生年月+'.01',getdate())<=20) 失败次数
union
select '20-30' 年龄段 ,select sum(1) from Order where GuestID in(select GuestID from Guest_info where datediff(yy,出生年月+'.01',getdate()) between 21 and 30) 预定次数,select sum(case isyes when 2 then 1 else 0 end) from Order where GuestID in(select GuestID from Guest_info where datediff(yy,出生年月+'.01',getdate()) between 21 and 30) 取消次数,select sum(case isyes when 3 then 1 else 0 end) from Order where GuestID in(select GuestID from Guest_info where datediff(yy,出生年月+'.01',getdate()) between 21 and 30) 违约次数,select sum(case isyes when 1 then 1 else 0 end) from Order where GuestID in(select GuestID from Guest_info where datediff(yy,出生年月+'.01',getdate()) between 21 and 30) 成功次数,select sum(case isyes when 0 then 1 else 0 end) from Order where GuestID in(select GuestID from Guest_info where datediff(yy,出生年月+'.01',getdate()) between 21 and 30) 失败次数
union
select '30以上' 年龄段 ,select sum(1) from Order where GuestID in(select GuestID from Guest_info where datediff(yy,出生年月+'.01',getdate())>30) 预定次数,select sum(case isyes when 2 then 1 else 0 end) from Order where GuestID in(select GuestID from Guest_info where datediff(yy,出生年月+'.01',getdate())>30) 取消次数,select sum(case isyes when 3 then 1 else 0 end) from Order where GuestID in(select GuestID from Guest_info where datediff(yy,出生年月+'.01',getdate())>30) 违约次数,select sum(case isyes when 1 then 1 else 0 end) from Order where GuestID in(select GuestID from Guest_info where datediff(yy,出生年月+'.01',getdate())>30) 成功次数,select sum(case isyes when 0 then 1 else 0 end) from Order where GuestID in(select GuestID from Guest_info where datediff(yy,出生年月+'.01',getdate())>30) 失败次数
回复
mschen 2005-09-27
--试试这个.

select 年龄段,
预定次数=max(case 定单状态 when '2' then 次数 else 0 end)+
max(case 定单状态 when '3' then 次数 else 0 end)+
max(case 定单状态 when '1' then 次数 else 0 end)+
max(case 定单状态 when '0' then 次数 else 0 end),
取消次数=max(case 定单状态 when '2' then 次数 else 0 end),
违约次数=max(case 定单状态 when '3' then 次数 else 0 end),
成功次数=max(case 定单状态 when '1' then 次数 else 0 end),
失败次数=max(case 定单状态 when '0' then 次数 else 0 end)
from
(
select (case when datediff(yy,G.BornDate,getdate())<20 then '20以下'
when datediff(yy,G.BornDate,getdate()) between 20 and 30 then '20-30'
end
) as 年龄段,
isyes as 定单状态,
count(*) as 次数
from Guest_info G join [Order] O
on G.GuestID=O.GuestID
group by (case when datediff(yy,G.BornDate,getdate())<20 then '20以下'
when datediff(yy,G.BornDate,getdate()) between 20 and 30 then '20-30'
end
),isyes
)t
回复
vivianfdlpw 2005-09-27
select ( case
when year(getdate())-B.出生年<20 then '20以下'
when year(getdate())-B.出生年>=20
and year(getdate())-B.出生年<30 then '20-30'
when year(getdate())-B.出生年>=30
and year(getdate())-B.出生年<40 then '30-40'
when year(getdate())-B.出生年>=40
and year(getdate())-B.出生年<50 then '40-50'
when year(getdate())-B.出生年>=50
and year(getdate())-B.出生年<60 then '50-60'
when year(getdate())-B.出生年>=60
and year(getdate())-B.出生年<70 then '60-70'
else '70以上'
end
) as '年龄段'
,sum(case when A.isyes in(0,1) then 1 else 0 end) as '预定次数'
,sum(case when A.isyes=2 then 1 else 0 end) as '取消次数'
,sum(case when A.isyes=3 then 1 else 0 end) as '违约次数'
,sum(case when A.isyes=1 then 1 else 0 end) as '成功次数'
,sum(case when A.isyes=0 then 1 else 0 end) as '失败次数'
from [Order] A
join Guest_info B on A.GuestID=B.GuestID
group by ( case
when year(getdate())-B.B.出生年<20 then '20以下'
when year(getdate())-B.出生年>=20 and year(getdate())-B.出生年<30
then '20-30'
when year(getdate())-B.出生年>=30 and year(getdate())-B.出生年<40
then '30-40'
when year(getdate())-B.出生年>=40 and year(getdate())-B.出生年<50
then '40-50'
when year(getdate())-B.出生年>=50 and year(getdate())-B.出生年<60
then '50-60'
when year(getdate())-B.出生年>=60 and year(getdate())-B.出生年<70
then '60-70'
else '70以上'
end
)
回复
子陌红尘 2005-09-27
select
c.年龄段,
预定次数 = count(c.*),
取消次数 = sum(case c.isyes when 2 then 1 else 0 end),
违约次数 = sum(case c.isyes when 3 then 1 else 0 end),
成功次数 = sum(case c.isyes when 1 then 1 else 0 end),
失败次数 = sum(case c.isyes when 0 then 1 else 0 end)
from
(select
年龄段 = case
when datediff(yy,cast(出生年月+'01' as datetime),gettime) >50
then '50以上'
when datediff(yy,cast(出生年月+'01' as datetime),gettime) between 41 and 50
then '41-50'
when datediff(yy,cast(出生年月+'01' as datetime),gettime) between 31 and 40
then '31-40'
when datediff(yy,cast(出生年月+'01' as datetime),gettime) between 20 and 30
then '20-30'
else
'20以下'
end,
b.isyes
from
Guestinfo a,
Order b
where
a.GuestID = b.GuestID) c
group by
c.年龄段
回复
相关推荐
发帖
应用实例
创建于2007-09-28

2.7w+

社区成员

MS-SQL Server 应用实例
申请成为版主
帖子事件
创建了帖子
2005-09-27 08:58
社区公告
暂无公告