这种数据结构，
如何才能用SQL语句一下得到，
刘诗诗 漂亮=8/9,一般=1/9,外倾=8/9,内倾=1/9,成熟=5/9,清纯=4/9
求SQL大神给个方案吧~坐等！
------解决思路----------------------
;WITH CTE AS(
SELECT CName
,Appearance1,CAST(COUNT(1)OVER(PARTITION BY CName,Appearance1)AS VARCHAR)Appearance1Cnt
,Personality,CAST(COUNT(1)OVER(PARTITION BY CName,Personality)AS VARCHAR)PersonalityCnt
,Adult,CAST(COUNT(1)OVER(PARTITION BY CName,Adult)AS VARCHAR)AdultCnt
,CAST(COUNT(1)OVER(PARTITION BY CName)AS VARCHAR)Cnt
FROM TB
)
,CTE2 AS(
SELECT CName,Appearance1,Appearance1Cnt,Cnt FROM CTE GROUP BY CName,Appearance1,Appearance1Cnt,Cnt
UNION ALL
SELECT CName,Personality,PersonalityCnt,Cnt FROM CTE GROUP BY CName,Personality,PersonalityCnt,Cnt
UNION ALL
SELECT CName,Adult,AdultCnt,Cnt FROM CTE GROUP BY CName,Adult,AdultCnt,Cnt
)
SELECT CName
,STUFF((SELECT ','+Appearance1+'='+Appearance1Cnt+'/'+Cnt
FROM CTE2 T2
WHERE T1.CName=T2.CName
FOR XML PATH(''))
,1,1,'')
FROM CTE2 T1
GROUP BY CName
------解决思路----------------------
--动态你可以试下以上语句
DECLARE @SQL VARCHAR(8000)--SQL2005+可以用VARCHAR(MAX)代替
SET @SQL=';WITH CTE AS(
SELECT CName
,Appearance1,CAST(COUNT(1)OVER(PARTITION BY CName,Appearance1)AS VARCHAR)Appearance1Cnt
,Personality,CAST(COUNT(1)OVER(PARTITION BY CName,Personality)AS VARCHAR)PersonalityCnt
,Adult,CAST(COUNT(1)OVER(PARTITION BY CName,Adult)AS VARCHAR)AdultCnt
,CAST(COUNT(1)OVER(PARTITION BY CName)AS VARCHAR)Cnt
FROM TB
)
,CTE2 AS(
SELECT CName,Appearance1,Appearance1Cnt,Cnt,1 T FROM CTE GROUP BY CName,Appearance1,Appearance1Cnt,Cnt
UNION ALL
SELECT CName,Personality,PersonalityCnt,Cnt,2 FROM CTE GROUP BY CName,Personality,PersonalityCnt,Cnt
UNION ALL
SELECT CName,Adult,AdultCnt,Cnt,3 FROM CTE GROUP BY CName,Adult,AdultCnt,Cnt
)SELECT CName'
SELECT @SQL=@SQL+',MAX(CASE WHEN T='+T+' AND Appearance1='''+Appearance1+''' THEN Appearance1Cnt+''/''+Cnt END)['+Appearance1+']'
FROM(
SELECT '1' T,Appearance1 FROM TB
UNION ALL
SELECT '2' T,Personality FROM TB
UNION ALL
SELECT '3' T,Adult FROM TB
)T1 GROUP BY T,Appearance1
SET @SQL=@SQL+'FROM CTE2 GROUP BY CName'
PRINT @SQL
EXEC(@SQL)
--静态
;WITH CTE AS(
SELECT CName
,Appearance1,CAST(COUNT(1)OVER(PARTITION BY CName,Appearance1)AS VARCHAR)Appearance1Cnt
,Personality,CAST(COUNT(1)OVER(PARTITION BY CName,Personality)AS VARCHAR)PersonalityCnt
,Adult,CAST(COUNT(1)OVER(PARTITION BY CName,Adult)AS VARCHAR)AdultCnt
,CAST(COUNT(1)OVER(PARTITION BY CName)AS VARCHAR)Cnt
FROM TB
)
,CTE2 AS(
SELECT CName,Appearance1,Appearance1Cnt,Cnt,1 T FROM CTE GROUP BY CName,Appearance1,Appearance1Cnt,Cnt
UNION ALL
SELECT CName,Personality,PersonalityCnt,Cnt,2 FROM CTE GROUP BY CName,Personality,PersonalityCnt,Cnt
UNION ALL
SELECT CName,Adult,AdultCnt,Cnt,3 FROM CTE GROUP BY CName,Adult,AdultCnt,Cnt
)
SELECT CName
,MAX(CASE WHEN T=1 AND Appearance1='漂亮' THEN Appearance1Cnt+'/'+Cnt END)[漂亮]
,MAX(CASE WHEN T=1 AND Appearance1='一般' THEN Appearance1Cnt+'/'+Cnt END)[一般]
,MAX(CASE WHEN T=2 AND Appearance1='内倾' THEN Appearance1Cnt+'/'+Cnt END)[内倾]
,MAX(CASE WHEN T=2 AND Appearance1='外倾' THEN Appearance1Cnt+'/'+Cnt END)[外倾]
,MAX(CASE WHEN T=3 AND Appearance1='成熟' THEN Appearance1Cnt+'/'+Cnt END)[成熟]
,MAX(CASE WHEN T=3 AND Appearance1='清纯' THEN Appearance1Cnt+'/'+Cnt END)[清纯]
FROM CTE2
GROUP BY CName
另外，Appearance1和Personality 会内容一样吗? 比如都有漂亮
------解决思路----------------------
select CName
,case(sum(case when Appearance1='漂亮' then 1 else 0 end) as varchar(10))+'/'+cast(count(1) as varchar(10)) as [漂亮]
,case(sum(case when Appearance1='一般' then 1 else 0 end) as varchar(10))+'/'+cast(count(1) as varchar(10)) as [一般]
,case(sum(case when Personality='外倾' then 1 else 0 end) as varchar(10))+'/'+cast(count(1) as varchar(10)) as [外倾]
,case(sum(case when Personality='内倾' then 1 else 0 end) as varchar(10))+'/'+cast(count(1) as varchar(10)) as [内倾]
,case(sum(case when Adult='成熟' then 1 else 0 end) as varchar(10))+'/'+cast(count(1) as varchar(10)) as [成熟]
,case(sum(case when Adult='清纯' then 1 else 0 end) as varchar(10))+'/'+cast(count(1) as varchar(10)) as [清纯]
from table_name
group by CName
------解决思路----------------------
不好意思 把 ,case 换成 ,cast 这个是转换数据类型的函数 手误写错了