dbo.Jx_RCPG(季度) iID cName
dbo.Jx_RCPGScore(分数) cScore iID UzbID(对应Jx_UserZB的iid) JDID(对应Jx_RCPG的iid)
dbo.Jx_Unit(单位) uID uUnitName
dbo.Jx_UserZB(计分类型) iid UserID(对应Jx_Unit的uid)
根据单位查询出每个季度的分数
单位名称 一季度 二季度 三季度 四季度 总计 排名
dbo.Jx_RCPGScore(分数) cScore iID UzbID(对应Jx_UserZB的iid) JDID(对应Jx_RCPG的iid)
dbo.Jx_Unit(单位) uID uUnitName
dbo.Jx_UserZB(计分类型) iid UserID(对应Jx_Unit的uid)
根据单位查询出每个季度的分数
单位名称 一季度 二季度 三季度 四季度 总计 排名
排名 = dense_rank() OVER (ORDER BY (a.one + a.two + a.three + a.four) DESC)
FROM (
SELECT unit, (
CASE cname WHEN '一季度' THEN score ELSE 0 END) AS one,
(CASE cname WHEN '二季度' THEN score ELSE 0 END) AS two,
(CASE cname WHEN '三季度' THEN score ELSE 0 END) AS three,
(CASE cname WHEN '四季度' THEN score ELSE 0 END) AS four
FROM (
select a.uUnitName as unit,d.cName,d.score from Jx_Unit a left outer join(
select ISNULL(SUM(cScore),0) as score,c.cName,b.UserID as userid
from Jx_RCPGScore a inner join Jx_UserZB b on a.UzbID=b.iid right outer join Jx_RCPG c
ON a.JDID = c.iID
where c.iID!=(select iID from Jx_RCPG where cName='年度')
group by c.cName,b.UserID) d on d.userid=a.uID
) a
GROUP BY unit, a.cName, a.score
) a
我这样写能查出来 但是每个季度的分数不会在一行显示,是一行有一个季度的数据,请问该怎么改
排名 = dense_rank() OVER (ORDER BY (a.one + a.two + a.three + a.four) DESC)
FROM (
SELECT unit, max(
CASE cname WHEN '一季度' THEN score ELSE 0 END) AS one,
max(CASE cname WHEN '二季度' THEN score ELSE 0 END) AS two,
max(CASE cname WHEN '三季度' THEN score ELSE 0 END) AS three,
max(CASE cname WHEN '四季度' THEN score ELSE 0 END) AS four
FROM (
select a.uUnitName as unit,d.cName,d.score from Jx_Unit a left outer join(
select ISNULL(SUM(cScore),0) as score,c.cName,b.UserID as userid
from Jx_RCPGScore a inner join Jx_UserZB b on a.UzbID=b.iid right outer join Jx_RCPG c
ON a.JDID = c.iID
where c.iID!=(select iID from Jx_RCPG where cName='年度')
group by c.cName,b.UserID) d on d.userid=a.uID
) a
GROUP BY unit
) a
from(
select a.uUnitName,
sum(case when c.cName='一季度' then b.cSore else 0 end)as [一季度],
sum(case when c.cName='二季度' then b.cSore else 0 end)as [二季度],
sum(case when c.cName='三季度' then b.cSore else 0 end)as [三季度],
sum(case when c.cName='四季度' then b.cSore else 0 end)as [四季度],
sum(cSore) as 总计
from Jx_Unit a inner join jx_rcpgscore b on a.uid=b.uzbid
inner join jx_rcpg c on b.jdid=c.iid
group by a.uUnitName
)t