上面这个表想实现交叉表查询
SELECT yyqbh,
SUM(fk) AS [合计 ],
SUM ( CASE fkmc WHEN '''+现金+''' THEN yyqbh END ) AS [现金 ] ,
SUM ( CASE fkmc WHEN '''+刷卡+''' THEN yyqbh ELSE NULL END ) AS [刷卡 ] ,
SUM ( CASE fkmc WHEN '''+团购+''' THEN yyqbh ELSE NULL END ) AS [团购 ]
FROM h GROUP BY yyqbh
得出查询结果
为什么现金、刷卡、团购的数值都是空的？？？（合计是正确有数据的）
很是疑惑
希望各位高人指教
十分感谢！！
------解决思路----------------------
SELECT yyqbh,
SUM(fk) AS [合计],
SUM ( CASE fkmc WHEN '现金' THEN fk ELSE NULL END ) AS [现金],
SUM ( CASE fkmc WHEN '刷卡' THEN fk ELSE NULL END ) AS [刷卡],
SUM ( CASE fkmc WHEN '团购' THEN fk ELSE NULL END ) AS [团购]
FROM h
GROUP BY yyqbh
------解决思路----------------------
WITH h(yyqbh,fkrq,fkmc,fk) AS (
SELECT 6,'2014-12-01','现金',110.0000 UNION ALL
SELECT 6,'2014-12-01','现金',114.0000 UNION ALL
SELECT 6,'2014-12-01','现金',198.0000 UNION ALL
SELECT 6,'2014-12-01','刷卡',309.0000 UNION ALL
SELECT 6,'2014-12-01','现金', 83.0000
)
SELECT yyqbh,
SUM(fk) AS [合计],
SUM ( CASE fkmc WHEN '现金' THEN fk ELSE NULL END ) AS [现金],
SUM ( CASE fkmc WHEN '刷卡' THEN fk ELSE NULL END ) AS [刷卡],
SUM ( CASE fkmc WHEN '团购' THEN fk ELSE NULL END ) AS [团购]
FROM h
GROUP BY yyqbh
yyqbh 合计 现金 刷卡 团购
----------- ---------- ---------- ---------- ----------
6 814.0000 505.0000 309.0000 NULL
没问题啊。或者你的fkmc字段值中有不可见字符？
------解决思路----------------------
fkmc字段是nvarchar(20),那么 SQL 语句中就要用 N'现金' 啊。