分析需求 就是要把 一张表中的数据 通过不同的计算方法得到不同的列 而且 这张表还要和其他的表进行关联
刚开始想这问题时 是怎么也想不出来 也不知道什么原因 竟然弄出来了
select a.hometeamid as team,(select count(*) from match z where z.Status=2 and z.liansaiid=3) as total,
(select count(*)
from match as d
where a.hometeamid=d.hometeamid and d.FirstHomeScores + d.SecondHomeScores = 0 or a.hometeamid=d.awayteamid and d.FirstAwayScores + d.SecondAwayScores = 0 ) qwj,
(select count(*)
from match as e
where a.hometeamid=e.hometeamid and e.FirstHomeScores = 0 or a.hometeamid=e.awayteamid and e.FirstAwayScores = 0 ) qwjs,
(select count(*)
from match as f
where a.hometeamid=f.hometeamid and f.SecondHomeScores = 0 or a.hometeamid=f.awayteamid and f.SecondAwayScores = 0 ) qwjx,
(select count(*)
from match as dd
where a.hometeamid=dd.hometeamid and dd.FirstAwayScores + dd.SecondAwayScores = 0 or a.hometeamid=dd.awayteamid and dd.FirstHomeScores + dd.SecondHomeScores = 0 ) qws,
(select count(*)
from match as ee
where a.hometeamid=ee.hometeamid and ee.FirstAwayScores = 0 or a.hometeamid=ee.awayteamid and ee.FirstHomeScores = 0 ) qwss,
(select count(*)
from match as ff
where a.hometeamid=ff.hometeamid and ff.SecondAwayScores = 0 or a.hometeamid=ff.awayteamid and ff.SecondHomeScores = 0 ) qwsx,
(select count(*) from match z where z.Status=2 and z.liansaiid=3)/2 half,
(select count(*)
from match as b
where a.hometeamid=b.hometeamid and b.FirstHomeScores + b.SecondHomeScores = 0 ) zwj,
(select count(*)
from match as g
where a.hometeamid=g.hometeamid and g.FirstHomeScores = 0 ) zwjs,
(select count(*)
from match as h
where a.hometeamid=h.hometeamid and h.SecondHomeScores = 0 ) zwjx,
(select count(*)
from match as bb
where a.hometeamid=bb.hometeamid and bb.FirstAwayScores + bb.SecondAwayScores = 0 ) zws,
(select count(*)
from match as gg
where a.hometeamid=gg.hometeamid and gg.FirstAwayScores = 0 ) zwss,
(select count(*)
from match as hh
where a.hometeamid=hh.hometeamid and hh.SecondAwayScores = 0 ) zwsx,
(select count(*) from match z where z.Status=2 and z.liansaiid=3)/2 half2,
(select count(*)
from match as c
where a.hometeamid=c.awayteamid and c.FirstAwayScores + c.SecondAwayScores = 0 ) kwj,
(select count(*)
from match as i
where a.hometeamid=i.awayteamid and i.FirstAwayScores = 0 )kwjs,
(select count(*)
from match as j
where a.hometeamid=j.awayteamid and j.SecondAwayScores = 0 ) kwjx,
(select count(*)
from match as cc
where a.hometeamid=cc.awayteamid and cc.FirstHomeScores + cc.SecondHomeScores = 0 ) kws,
(select count(*)
from match as ii
where a.hometeamid=ii.awayteamid and ii.FirstHomeScores = 0 )kwss,
(select count(*)
from match as jj
where a.hometeamid=jj.awayteamid and jj.SecondHomeScores = 0 ) kwsx
from match as a
where a.Status=2 and a.liansaiid=3
group by a.hometeamid
有些长的吓人把 我去简化下 明天继续 心里太难受了 张琳