全部

有个订单的统计，如何写ＳＱＬ

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.年龄段

回复赞