order by 不能使用别名

今天在写一个sql 的时候可能是写的少的原因 花了好长的时间

现在吧代码贴出来 大家一起看看

select team.name tname,sum(c.none) as none ,sum(c.one) as one,sum(c.two) as two,sum(c.three) as three,sum(c.four) as four,sum(c.five) as five from ( 
select * from 
(select hometeamid tid,
sum(case when FirstHomeScores + SecondHomeScores  = 0 then 1 else 0 end)  none,
sum(case when FirstHomeScores + SecondHomeScores  = 1 then 1 else 0 end)  one,
sum(case when FirstHomeScores + SecondHomeScores  = 2 then 1 else 0 end)  two,
sum(case when FirstHomeScores + SecondHomeScores  = 3 then 1 else 0 end)  three,
sum(case when FirstHomeScores + SecondHomeScores  = 4 then 1 else 0 end)  four,
sum(case when FirstHomeScores + SecondHomeScores  >= 5 then 1 else 0 end)  five
from match where liansaiid=3   group by hometeamid )as a
union
select * from  
(select awayteamid as tid,
sum(case when  FirstAwayScores + SecondAwayScores = 0 then 1 else 0 end)  none,
sum(case when  FirstAwayScores + SecondAwayScores = 1 then 1 else 0 end)  one,
sum(case when  FirstAwayScores + SecondAwayScores = 2 then 1 else 0 end)  two,
sum(case when  FirstAwayScores + SecondAwayScores = 3 then 1 else 0 end)  three,
sum(case when  FirstAwayScores + SecondAwayScores = 4 then 1 else 0 end)  four,
sum(case when  FirstAwayScores + SecondAwayScores >= 5 then 1 else 0 end)  five
from match where liansaiid=3  group by awayteamid ) as b
)as c,team 
where c.tid=team.teamid
group by team.name

首先这里使用了case when 条件 then 真值 else 假值 语法 。

然后这里采用了一个union 连接两张结构一样的表，问题就处在这里。

原以为两张表经过union之后变成了一张表，可以对这样表进行操作了。可是死活就是编译不过去。后来实在没办法 就想到了 ，把两张表合并在一起以后进行一次查询。这样就获得了一张可以操作的表，并且

这张表可以使用 前面的别名。group 不要中是否能使用 还没试过。

转载于:https://www.cnblogs.com/wxzl/archive/2012/02/07/2341879.html