WITH CTE AS( select RuleName,TypeName,TopScore,LowestScore from TB_Base_Rule_Type_Score t1 left join TB_Base_RuleType t2 on t1.RuleTypeID=t2.ID left join TB_Base_Rule t3 on t1.RuleID=t3.ID ) SELECT RuleName ,CASE TypeName WHEN '优' THEN TypeName END TypeName ,CASE TypeName WHEN '优' THEN TopScore END TopScore ,CASE TypeName WHEN '优' THEN LowestScore END LowestScore ,CASE TypeName WHEN '良' THEN TypeName END TypeName ,CASE TypeName WHEN '良' THEN TopScore END TopScore ,CASE TypeName WHEN '良' THEN LowestScore END LowestScore ,CASE TypeName WHEN '合格' THEN TypeName END TypeName ,CASE TypeName WHEN '合格' THEN TopScore END TopScore ,CASE TypeName WHEN '合格' THEN LowestScore END LowestScore ,CASE TypeName WHEN '差' THEN TypeName END TypeName ,CASE TypeName WHEN '差' THEN TopScore END TopScore ,CASE TypeName WHEN '差' THEN LowestScore END LowestScore FROM CTE GROUP BY RuleName
现在提示:选择列表中的列 'CTE.TypeName' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。 如果我把TypeName,TopScore,LowestScore放在group中,组合是有了,能满足我的需求,但内容还是2行。
现在提示:选择列表中的列 'CTE.TypeName' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。 如果我把TypeName,TopScore,LowestScore放在group中,组合是有了,能满足我的需求,但内容还是2行。 SELECT RuleName ,MAX(CASE TypeName WHEN '优' THEN TypeName END )TypeName ,MAX(CASE TypeName WHEN '优' THEN TopScore END ) TopScore ,MAX(CASE TypeName WHEN '优' THEN LowestScore END ) LowestScore ,MAX(CASE TypeName WHEN '良' THEN TypeName END ) TypeName ,MAX(CASE TypeName WHEN '良' THEN TopScore END ) TopScore ,MAX(CASE TypeName WHEN '良' THEN LowestScore END ) LowestScore ,MAX(CASE TypeName WHEN '合格' THEN TypeName END ) TypeName ,MAX(CASE TypeName WHEN '合格' THEN TopScore END ) TopScore ,MAX(CASE TypeName WHEN '合格' THEN LowestScore END ) LowestScore ,MAX(CASE TypeName WHEN '差' THEN TypeName END ) TypeName ,MAX(CASE TypeName WHEN '差' THEN TopScore END ) TopScore ,MAX(CASE TypeName WHEN '差' THEN LowestScore END ) LowestScore FROM CTE GROUP BY RuleName 之前没弄好,试下这个
现在提示:选择列表中的列 'CTE.TypeName' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。 如果我把TypeName,TopScore,LowestScore放在group中,组合是有了,能满足我的需求,但内容还是2行。 SELECT RuleName ,MAX(CASE TypeName WHEN '优' THEN TypeName END )TypeName ,MAX(CASE TypeName WHEN '优' THEN TopScore END ) TopScore ,MAX(CASE TypeName WHEN '优' THEN LowestScore END ) LowestScore ,MAX(CASE TypeName WHEN '良' THEN TypeName END ) TypeName ,MAX(CASE TypeName WHEN '良' THEN TopScore END ) TopScore ,MAX(CASE TypeName WHEN '良' THEN LowestScore END ) LowestScore ,MAX(CASE TypeName WHEN '合格' THEN TypeName END ) TypeName ,MAX(CASE TypeName WHEN '合格' THEN TopScore END ) TopScore ,MAX(CASE TypeName WHEN '合格' THEN LowestScore END ) LowestScore ,MAX(CASE TypeName WHEN '差' THEN TypeName END ) TypeName ,MAX(CASE TypeName WHEN '差' THEN TopScore END ) TopScore ,MAX(CASE TypeName WHEN '差' THEN LowestScore END ) LowestScore FROM CTE GROUP BY RuleName 之前没弄好,试下这个太感谢您了。问题已解决。谢谢。学了一招
select RuleName,TypeName,TopScore,LowestScore from TB_Base_Rule_Type_Score t1
left join TB_Base_RuleType t2
on t1.RuleTypeID=t2.ID
left join TB_Base_Rule t3
on t1.RuleID=t3.ID
)
SELECT
RuleName
,CASE TypeName WHEN '优' THEN TypeName END TypeName
,CASE TypeName WHEN '优' THEN TopScore END TopScore
,CASE TypeName WHEN '优' THEN LowestScore END LowestScore
,CASE TypeName WHEN '良' THEN TypeName END TypeName
,CASE TypeName WHEN '良' THEN TopScore END TopScore
,CASE TypeName WHEN '良' THEN LowestScore END LowestScore
,CASE TypeName WHEN '合格' THEN TypeName END TypeName
,CASE TypeName WHEN '合格' THEN TopScore END TopScore
,CASE TypeName WHEN '合格' THEN LowestScore END LowestScore
,CASE TypeName WHEN '差' THEN TypeName END TypeName
,CASE TypeName WHEN '差' THEN TopScore END TopScore
,CASE TypeName WHEN '差' THEN LowestScore END LowestScore
FROM
CTE
GROUP BY
RuleName
谢谢您的回答,我这还有个问题,我的优良差是在TB_Base_RuleType 这张表里维护的,意味着他可能不固定。case when有办法动态吗?
EXEC(@SQL)
如果我把TypeName,TopScore,LowestScore放在group中,组合是有了,能满足我的需求,但内容还是2行。
如果我把TypeName,TopScore,LowestScore放在group中,组合是有了,能满足我的需求,但内容还是2行。
SELECT
RuleName
,MAX(CASE TypeName WHEN '优' THEN TypeName END )TypeName
,MAX(CASE TypeName WHEN '优' THEN TopScore END ) TopScore
,MAX(CASE TypeName WHEN '优' THEN LowestScore END ) LowestScore
,MAX(CASE TypeName WHEN '良' THEN TypeName END ) TypeName
,MAX(CASE TypeName WHEN '良' THEN TopScore END ) TopScore
,MAX(CASE TypeName WHEN '良' THEN LowestScore END ) LowestScore
,MAX(CASE TypeName WHEN '合格' THEN TypeName END ) TypeName
,MAX(CASE TypeName WHEN '合格' THEN TopScore END ) TopScore
,MAX(CASE TypeName WHEN '合格' THEN LowestScore END ) LowestScore
,MAX(CASE TypeName WHEN '差' THEN TypeName END ) TypeName
,MAX(CASE TypeName WHEN '差' THEN TopScore END ) TopScore
,MAX(CASE TypeName WHEN '差' THEN LowestScore END ) LowestScore
FROM
CTE
GROUP BY
RuleName
之前没弄好,试下这个
如果我把TypeName,TopScore,LowestScore放在group中,组合是有了,能满足我的需求,但内容还是2行。
SELECT
RuleName
,MAX(CASE TypeName WHEN '优' THEN TypeName END )TypeName
,MAX(CASE TypeName WHEN '优' THEN TopScore END ) TopScore
,MAX(CASE TypeName WHEN '优' THEN LowestScore END ) LowestScore
,MAX(CASE TypeName WHEN '良' THEN TypeName END ) TypeName
,MAX(CASE TypeName WHEN '良' THEN TopScore END ) TopScore
,MAX(CASE TypeName WHEN '良' THEN LowestScore END ) LowestScore
,MAX(CASE TypeName WHEN '合格' THEN TypeName END ) TypeName
,MAX(CASE TypeName WHEN '合格' THEN TopScore END ) TopScore
,MAX(CASE TypeName WHEN '合格' THEN LowestScore END ) LowestScore
,MAX(CASE TypeName WHEN '差' THEN TypeName END ) TypeName
,MAX(CASE TypeName WHEN '差' THEN TopScore END ) TopScore
,MAX(CASE TypeName WHEN '差' THEN LowestScore END ) LowestScore
FROM
CTE
GROUP BY
RuleName
之前没弄好,试下这个太感谢您了。问题已解决。谢谢。学了一招