MS sql server 2005 数据查询语句如何写？

表名  table_1id name kumu fenshu 1 zs yuwen 232 3 ls shuxue 32 4 zs shuxue 12 5 ls lishi 343 6 ww yuwen 1245 8 zs lishi 121

写出sql语句，查询结果如下： name  yuwen shuxue lishi   zl       232     12      121  ls     0        32   343   ww    1245     0        0请问改如何书 ms sql 2005数据库.

select id,name,kumu,fenshu from table_1

SELECt NAME , a.[yuwen],a.[shuxue].a.[lishi]

from table-1

pivot ( sum(fenshu) for kumu in ([yuwen],[shuxue],[lishi])) as a

典型的交叉表应用（即行列转换）

SELECt [NAME], SUM(CASE KUMU WHEN 'YUWEN' THEN FENSHU ELSE 0 END) AS YUWEN, SUM(CASE KUMU WHEN 'SHUXUE' THEN FENSHU ELSE 0 END) AS SHUXUE, SUM(CASE KUMU WHEN 'LISHI' THEN FENSHU ELSE 0 END) AS LISHI FROM table_1 GROUP BY [NAME]

执行结果

直接运用数据库语句select id,name,kumu,fenshu from table_1

写的有点复杂，但能实现你要的效果，而且效率很底，建议你用存储过程比较好。

select (select fenshu from table_1 where (name=a.name) and (kemu='yuwen')) yuwen, (select fenshu from table_1 where (name=a.name) and (kemu='shuxue')) shuxue, (select fenshu from table_1 where (name=a.name) and (kemu='lishi')) lishi from table_1 a where (a.name in (select name from table_1)) group by a.name

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息