字段一 字段二
1 21
1 22
1 23
1 24
1 25
2 26
2 27
……………………假设查询出来的数据是这样的,我现在想让查询的结果是
字段一 字段二 字段二 字段三 字段4 字段5
1 21 22 23 24 25
2 26 27因为我用的是mysql数据库,所以请大家用比较简单的方法写出来,要不MYSQL不一定支持,我刚查了下好像PIVOT可以,但是不知道MYSQL支持不
麻烦大家用简单的方法写下来,不要用游标了
1 21
1 22
1 23
1 24
1 25
2 26
2 27
……………………假设查询出来的数据是这样的,我现在想让查询的结果是
字段一 字段二 字段二 字段三 字段4 字段5
1 21 22 23 24 25
2 26 27因为我用的是mysql数据库,所以请大家用比较简单的方法写出来,要不MYSQL不一定支持,我刚查了下好像PIVOT可以,但是不知道MYSQL支持不
麻烦大家用简单的方法写下来,不要用游标了
case when ...group by 能实现行转列.
declare @sql varchar(8000)
set @sql = 'select 字段一 '
select @sql = @sql + ' , max(case ltrim(字段一) when ''' + ltrim(字段一) + ''' then 字段二 else 0 end) [' + ltrim(字段一) +']'
from (select distinct 字段一 from tb) as a
set @sql = @sql + ' from tb group by 字段一'
exec(@sql)
---sql里的,不知道MYSQL支不支持if object_id('tb') is not null drop table tb
go
create table tb(字段一 int, 字段二 int)
insert tb select
1, 21 union all select
1, 22 union all select
1, 23 union all select
1, 24 union all select
1, 25 union all select
2, 26 union all select
2, 27
select *,id=(select count(1) from tb where 字段一=t.字段一 and 字段二<=t.字段二)
into #t from tb tdeclare @s varchar(4000)select @s=isnull(@s+',','')+'max(case when id='+ltrim(id)+' then ltrim(字段二) else '''' end) ['+'字段'+ltrim(id)+']'
from (select distinct id from #t)tset @s='select 字段一,'+@s+' from #t group by 字段一'
exec(@s)
字段一 字段1 字段2 字段3 字段4 字段5
----------- ------------ ------------ ------------ ------------ ------------
1 21 22 23 24 25
2 26 27 (2 行受影响)drop table #t
(
col1 int,
col2 int
);
insert into tb values(1,21);
insert into tb values(1,22);
insert into tb values(1,23);
insert into tb values(1,24);
insert into tb values(1,25);
insert into tb values(2,26);
insert into tb values(2,27);select
max(if(px=1,col2,0)) as col1,
max(if(px=2,col2,0)) as col2,
max(if(px=3,col2,0)) as col3,
max(if(px=4,col2,0)) as col4,
max(if(px=5,col2,0)) as col5
from(
select *,(select count(1)+1 from tb where col1=t.col1 and col2<t.col2) as px from tb t
) t
group by col1/**
col1 col2 col3 col4 col5
------ ------ ------ ------ ------
21 22 23 24 25
26 27 0 0 0
**/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-21 13:35:30
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([字段一] int,[字段二] int)
insert [tb]
select 1,21 union all
select 1,22 union all
select 1,23 union all
select 1,24 union all
select 1,25 union all
select 2,26 union all
select 2,27
--------------开始查询--------------------------
select *,id=(select count(1) from tb where 字段一=t.字段一 and 字段二<=t.字段二)
into #t from tb tdeclare @sql varchar(8000)
set @sql = 'select 字段一 '
select @sql = @sql + ' , max(case ltrim(id) when ''' + ltrim(id) + ''' then 字段二 else 0 end) ['+'字段'+ltrim(id)+']'
from (select distinct id from #t) as a
set @sql = @sql + ' from #t group by 字段一'
exec(@sql) drop table #t
----------------结果----------------------------
/* 字段一 字段1 字段2 字段3 字段4 字段5
----------- ----------- ----------- ----------- ----------- -----------
1 21 22 23 24 25
2 26 27 0 0 0(2 行受影响)
*/
一个列名而已 在MYSQL一样不知道行不行
字段别名,就是增加了一个排序字段,报什么错了?
我在mysql下测试通过
[SQL] select
max(if(px=1,col2,0)) as col1,
max(if(px=2,col2,0)) as col2,
max(if(px=3,col2,0)) as col3,
max(if(px=4,col2,0)) as col4,
max(if(px=5,col2,0)) as col5
from(
select *,(select count(1)+1 from tb where col1=t.col1 and col2<t.col2) as px from tb t
) t
group by col1;
[Err] 1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select *,(select count(1)+1 from tb where col1=t.col1 and col2<
declare @sql varchar(1000)
set @sql = 'select 字段一 '
select @sql = @sql + ' , max(case ltrim(字段一) when ''' + ltrim(字段一) + ''' then 字段二 else 0 end) [' + ltrim(字段一) +']' from (select distinct 字段一 from tb) as a
set @sql = @sql + '
from tb group by 字段一'
exec(@sql)