--declare @totalCount int if object_id('tempdb..#')is not null drop table # select @totalCount=sum(total) from #SELECT CASE VoteValueStr WHEN '较好' THEN '好' WHEN '很好' THEN '好' WHEN '较差' THEN '差' WHEN '很差' THEN '差' ELSE VoteValueStr END AS VoteValueStr , CASE WHEN VoteValueStr='较好' or VoteValueStr='很好' THEN 1 WHEN VoteValueStr='较差' or VoteValueStr='很差' THEN 3 ELSE 2 END AS DisplayOrder -- COUNT(1) AS total COUNT(1)Over() AS total Into # FROM [Vote_ListLog1] where ClassID<>7 -- GROUP BY CASE VoteValueStr -- WHEN '较好' THEN '好' -- WHEN '很好' THEN '好' -- WHEN '较差' THEN '差' -- WHEN '很差' THEN '差' -- ELSE votevaluestr -- END -- ) T
Select VoteValueStr ,total ,Ltrim(Cast(Count(1)*100.0/total As Numeric(4,2)))+'%' As perTotal From # Group by VoteValueStr,total -- SELECT * FROM (SELECT CASE VoteValueStr -- WHEN '较好' THEN '好' -- WHEN '很好' THEN '好' -- WHEN '较差' THEN '差' -- WHEN '很差' THEN '差' -- ELSE VoteValueStr -- END AS VoteValueStr , -- COUNT(1) AS total, -- SUBSTRING (convert(varchar (20),(COUNT(1)*1.0/@totalCount*100)),1,5)+'%' as perTotal -- FROM [Vote_ListLog1] where ClassID<>7 -- GROUP BY CASE VoteValueStr -- WHEN '较好' THEN '好' -- WHEN '很好' THEN '好' -- WHEN '较差' THEN '差' -- WHEN '很差' THEN '差' -- ELSE votevaluestr -- END -- ) T --ORDER BY CASE VoteValueStr -- WHEN '好' THEN 1 -- WHEN '一般' THEN 2 -- ELSE
去除注視: IF OBJECT_ID('tempdb..#')IS NOT NULL DROP TABLE # SELECT CASE WHEN VoteValueStr=N'较好' OR VoteValueStr=N'很好' THEN '好' WHEN VoteValueStr=N'较差' OR VoteValueStr=N'很差' THEN '差' ELSE VoteValueStr END AS VoteValueStr ,CASE WHEN VoteValueStr=N'较好' or VoteValueStr=N'很好' THEN 1 WHEN VoteValueStr=N'较差' or VoteValueStr=N'很差' THEN 3 ELSE 2 END AS DisplayOrder ,COUNT(1)Over() AS total INTO # FROM [Vote_ListLog1] WHERE ClassID<>7
SELECT VoteValueStr ,total ,LTRIM(CAST(COUNT(1)*100.0/total AS NUMERIC(4,2)))+'%' AS perTotal FROM # GROUP BY VoteValueStr,total,DisplayOrder ORDER BY DisplayOrder
有100%的可能性的話,就使用 NUMERIC(5,2)
是不是刷選條件少了個:where ClassID<>7
IF OBJECT_ID('tempdb..#')IS NOT NULL DROP TABLE # SELECT CASE WHEN VoteValueStr=N'较好' OR VoteValueStr=N'很好' THEN '好' WHEN VoteValueStr=N'较差' OR VoteValueStr=N'很差' THEN '差' ELSE VoteValueStr END AS VoteValueStr ,CASE WHEN VoteValueStr=N'较好' or VoteValueStr=N'很好' THEN 1 WHEN VoteValueStr=N'较差' or VoteValueStr=N'很差' THEN 3 ELSE 2 END AS DisplayOrder ,COUNT(1)Over() AS total INTO # FROM [Vote_ListLog1] WHERE ClassID<>7
SELECT VoteValueStr ,COUNT(1) AS 數量 ,LTRIM(CAST(COUNT(1)*100.0/total AS NUMERIC(5,2)))+'%' AS perTotal FROM # GROUP BY VoteValueStr,total,DisplayOrder ORDER BY DisplayOrder
if object_id('tempdb..#')is not null
drop table #
select @totalCount=sum(total) from #SELECT CASE VoteValueStr
WHEN '较好' THEN '好'
WHEN '很好' THEN '好'
WHEN '较差' THEN '差'
WHEN '很差' THEN '差'
ELSE VoteValueStr
END AS VoteValueStr ,
CASE
WHEN VoteValueStr='较好' or VoteValueStr='很好' THEN 1
WHEN VoteValueStr='较差' or VoteValueStr='很差' THEN 3
ELSE 2
END AS DisplayOrder
-- COUNT(1) AS total
COUNT(1)Over() AS total
Into #
FROM [Vote_ListLog1] where ClassID<>7
-- GROUP BY CASE VoteValueStr
-- WHEN '较好' THEN '好'
-- WHEN '很好' THEN '好'
-- WHEN '较差' THEN '差'
-- WHEN '很差' THEN '差'
-- ELSE votevaluestr
-- END
-- ) T
Select
VoteValueStr
,total
,Ltrim(Cast(Count(1)*100.0/total As Numeric(4,2)))+'%' As perTotal
From #
Group by VoteValueStr,total
-- SELECT * FROM (SELECT CASE VoteValueStr
-- WHEN '较好' THEN '好'
-- WHEN '很好' THEN '好'
-- WHEN '较差' THEN '差'
-- WHEN '很差' THEN '差'
-- ELSE VoteValueStr
-- END AS VoteValueStr ,
-- COUNT(1) AS total,
-- SUBSTRING (convert(varchar (20),(COUNT(1)*1.0/@totalCount*100)),1,5)+'%' as perTotal
-- FROM [Vote_ListLog1] where ClassID<>7
-- GROUP BY CASE VoteValueStr
-- WHEN '较好' THEN '好'
-- WHEN '很好' THEN '好'
-- WHEN '较差' THEN '差'
-- WHEN '很差' THEN '差'
-- ELSE votevaluestr
-- END
-- ) T
--ORDER BY CASE VoteValueStr
-- WHEN '好' THEN 1
-- WHEN '一般' THEN 2
-- ELSE
IF OBJECT_ID('tempdb..#')IS NOT NULL
DROP TABLE #
SELECT
CASE
WHEN VoteValueStr=N'较好' OR VoteValueStr=N'很好'
THEN '好'
WHEN VoteValueStr=N'较差' OR VoteValueStr=N'很差'
THEN '差'
ELSE VoteValueStr
END AS VoteValueStr
,CASE
WHEN VoteValueStr=N'较好' or VoteValueStr=N'很好'
THEN 1
WHEN VoteValueStr=N'较差' or VoteValueStr=N'很差'
THEN 3
ELSE 2
END AS DisplayOrder
,COUNT(1)Over() AS total
INTO #
FROM [Vote_ListLog1]
WHERE ClassID<>7
SELECT
VoteValueStr
,total
,LTRIM(CAST(COUNT(1)*100.0/total AS NUMERIC(4,2)))+'%' AS perTotal
FROM #
GROUP BY VoteValueStr,total,DisplayOrder
ORDER BY DisplayOrder
DROP TABLE #
SELECT
CASE
WHEN VoteValueStr=N'较好' OR VoteValueStr=N'很好'
THEN '好'
WHEN VoteValueStr=N'较差' OR VoteValueStr=N'很差'
THEN '差'
ELSE VoteValueStr
END AS VoteValueStr
,CASE
WHEN VoteValueStr=N'较好' or VoteValueStr=N'很好'
THEN 1
WHEN VoteValueStr=N'较差' or VoteValueStr=N'很差'
THEN 3
ELSE 2
END AS DisplayOrder
,COUNT(1)Over() AS total
INTO #
FROM [Vote_ListLog1]
WHERE ClassID<>7
SELECT
VoteValueStr
,COUNT(1) AS 數量
,LTRIM(CAST(COUNT(1)*100.0/total AS NUMERIC(5,2)))+'%' AS perTotal
FROM #
GROUP BY VoteValueStr,total,DisplayOrder
ORDER BY DisplayOrder