---------------------------------------------------------------- -- Author :DBA_HuangZJ(發糞塗牆) -- Date :2014-09-17 16:43:35 -- Version: -- Microsoft SQL Server 2012 - 11.0.5058.0 (X64) -- May 14 2014 18:34:29 -- Copyright (c) Microsoft Corporation -- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据:[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([course] varchar(6),[name] varchar(4),[score] int) insert [huang] select 'class1','张三',81 union all select 'class1','李四',85 union all select 'class2','张三',92 union all select 'class2','李四',96 union all select 'class3','张三',80 --------------开始查询--------------------------declare @s nvarchar(4000) set @s='' Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else null end)' from [huang] group by [Course] exec('select [name]'+@s+' from [huang] group by [name]') ----------------结果---------------------------- /* name class1 class2 class3 ---- ----------- ----------- ----------- 李四 85 96 NULL 张三 81 92 80 */
换个写法 if object_id('tempdb..#huang') is not null drop table #huang go create table #huang([course] varchar(6),[name] varchar(4),[score] int) insert #huang select 'class1','张三',81 union all select 'class1','李四',85 union all select 'class2','张三',92 union all select 'class2','李四',96 union all select 'class3','张三',80declare @str varchar(max);with cte as (select 1 id,* from #huang) select @str=(select course + ',' from cte group by course FOR XML path('') ) from cte a group by id set @str = LEFT(@str,len(@str)-1)select @str=' select * from #huang pivot (max([score]) for [course] in('+@str+'))b'exec(@str)
谢谢两位,因为数据库我用的mariaDB(mysql),所有有些操作命令不一样。静态操作我已经实现了,动态操作我在mysql中构造过程 delimiter // create procedure sp_test() begin declare we varchar(8000); set we= 'select 姓名 '; select we = we+ ' , max(case 课程 when '" + 课程 +"' then 分数 else 0 end) ['+课程+']' from (select distinct 课程 from tb) asa; set we=we+ ' from tb group by 姓名'; execute we; endcall 'sp_test()';时提示错误“SQL错误(1292)发生在语句 #2: Truncated incorrect DOUBLE value: ' from tb group by 姓名' 中 */ “,网上查询一般说是字符集错误,但我这句”from tb group by 姓名“错在哪里啊?
如果是SQL查询,以上就差不多了,如果NET 要绑定控件,还要进一步处理绑定
额,板块没看到mysql,其实还是有很多sql 的命令是通用的,谢谢啦,不要生气
给你移动了,当年搞sybase,号称95%的语法相同,结果那5%的差异搞了我几天
SET @EE=''; SELECT @EE:=CONCAT(@EE,'SUM(IF(课程=\'',课程,'\'',',分数,0)) AS `',课程,'`,') FROM (SELECT DISTINCT 课程 FROM tb order by id) A; SET @QQ=CONCAT('SELECT ifnull(姓名,\'total\') as name,',@EE,'SUM(分数) AS TOTAL FROM tb GROUP BY 姓名 '); prepare stmt from @QQ; execute stmt;动态查询出来了,虽然我还是晕的,剩下的就是.net控件绑定了。
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-09-17 16:43:35
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([course] varchar(6),[name] varchar(4),[score] int)
insert [huang]
select 'class1','张三',81 union all
select 'class1','李四',85 union all
select 'class2','张三',92 union all
select 'class2','李四',96 union all
select 'class3','张三',80
--------------开始查询--------------------------declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else null end)'
from [huang] group by [Course]
exec('select [name]'+@s+' from [huang] group by [name]')
----------------结果----------------------------
/*
name class1 class2 class3
---- ----------- ----------- -----------
李四 85 96 NULL
张三 81 92 80
*/
if object_id('tempdb..#huang') is not null drop table #huang
go
create table #huang([course] varchar(6),[name] varchar(4),[score] int)
insert #huang
select 'class1','张三',81 union all
select 'class1','李四',85 union all
select 'class2','张三',92 union all
select 'class2','李四',96 union all
select 'class3','张三',80declare @str varchar(max);with cte as
(select 1 id,* from #huang)
select @str=(select course + ',' from cte group by course FOR XML path('') )
from cte a
group by id
set @str = LEFT(@str,len(@str)-1)select @str='
select * from #huang
pivot
(max([score]) for [course] in('+@str+'))b'exec(@str)
delimiter //
create procedure sp_test()
begin
declare we varchar(8000);
set we= 'select 姓名 ';
select we = we+ ' , max(case 课程 when '" + 课程 +"' then 分数 else 0 end) ['+课程+']'
from (select distinct 课程 from tb) asa;
set we=we+ ' from tb group by 姓名';
execute we;
endcall 'sp_test()';时提示错误“SQL错误(1292)发生在语句 #2: Truncated incorrect DOUBLE value: ' from tb group by 姓名' 中 */ “,网上查询一般说是字符集错误,但我这句”from tb group by 姓名“错在哪里啊?
额,板块没看到mysql,其实还是有很多sql 的命令是通用的,谢谢啦,不要生气
SET @EE='';
SELECT @EE:=CONCAT(@EE,'SUM(IF(课程=\'',课程,'\'',',分数,0)) AS `',课程,'`,') FROM (SELECT DISTINCT 课程 FROM tb order by id) A;
SET @QQ=CONCAT('SELECT ifnull(姓名,\'total\') as name,',@EE,'SUM(分数) AS TOTAL FROM tb GROUP BY 姓名 ');
prepare stmt from @QQ;
execute stmt;动态查询出来了,虽然我还是晕的,剩下的就是.net控件绑定了。