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

liqiyou 2005-09-27 08:58:13

20以下 1 0　　　　　 0 1　　　　0
20－30 5 　0　　　　　 4 1 　0
...... ...... ...... .....

...全文
103 点赞 收藏 6

6 条回复

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 '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
)

select
c.年龄段,

from
(select

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

2.7w+

MS-SQL Server 应用实例

2005-09-27 08:58