版本:sql2000 SP4
OS:win2003问题:我有一个如图所示的表
需求:我想得到下面的表格:并且不想用临时表,最好是视图,可以有中间视图。cpici cvalue1,cvalue2,cvalue3,cvalue4
-----------------
T501|31|33|5 |
T502|22|3 | |
T503|23|44|50|53
数据:CREATE table tb1 --数据表
(
cpici varchar(10) not null,
cname varchar(10) not null,
cvalue int null
)
--插入测试数据
INSERT INTO tb1 values('T501','x1',31)
INSERT INTO tb1 values('T501','x1',33)
INSERT INTO tb1 values('T501','x1',5)INSERT INTO tb1 values('T502','x1',3)
INSERT INTO tb1 values('T502','x1',22)
INSERT INTO tb1 values('T502','x1',3)INSERT INTO tb1 values('T503','x1',53)
INSERT INTO tb1 values('T503','x1',44)
INSERT INTO tb1 values('T503','x1',50)
INSERT INTO tb1 values('T503','x1',23)SELECT * FROM tb1
OS:win2003问题:我有一个如图所示的表
需求:我想得到下面的表格:并且不想用临时表,最好是视图,可以有中间视图。cpici cvalue1,cvalue2,cvalue3,cvalue4
-----------------
T501|31|33|5 |
T502|22|3 | |
T503|23|44|50|53
数据:CREATE table tb1 --数据表
(
cpici varchar(10) not null,
cname varchar(10) not null,
cvalue int null
)
--插入测试数据
INSERT INTO tb1 values('T501','x1',31)
INSERT INTO tb1 values('T501','x1',33)
INSERT INTO tb1 values('T501','x1',5)INSERT INTO tb1 values('T502','x1',3)
INSERT INTO tb1 values('T502','x1',22)
INSERT INTO tb1 values('T502','x1',3)INSERT INTO tb1 values('T503','x1',53)
INSERT INTO tb1 values('T503','x1',44)
INSERT INTO tb1 values('T503','x1',50)
INSERT INTO tb1 values('T503','x1',23)SELECT * FROM tb1
解决方案 »
- 关于多表联合查询
- 求高手解sql问题
- sql交叉表
- 用replace替换时如何只替换第一个匹配项?
- 开发数据库最好用什么语言?
- 为何COUNT(DISTINCT 字段名) 在ACCESS中无法使用? 有无替代解决方法?? 在线等待中...
- 高手请进!!!关于多表不相等连接的问题!(急急急……)
- 一个ACCESS数据库的开发小问题
- 哪位大虾能提供一个例子:用DTS import a text file into a table.我可以用dts设计器手动做,但是编程怎么做?
- 级联删除大问题,请高手!
- 不备份master数据库,只备份自建的数据库,会导致备份无法恢复吗?
- 求一子查询将子查询结果作为最终结果集的字段名
go
CREATE table tb1 --数据表
(
cpici varchar(10) not null,
cname varchar(10) not null,
cvalue int null
)
--插入测试数据
INSERT INTO tb1 values('T501','x1',31)
INSERT INTO tb1 values('T501','x1',33)
INSERT INTO tb1 values('T501','x1',5)INSERT INTO tb1 values('T502','x1',3)
INSERT INTO tb1 values('T502','x1',22)
INSERT INTO tb1 values('T502','x1',3)INSERT INTO tb1 values('T503','x1',53)
INSERT INTO tb1 values('T503','x1',44)
INSERT INTO tb1 values('T503','x1',50)
INSERT INTO tb1 values('T503','x1',23)---------------------------------- if object_id('vv')is not null drop view vv
go
CREATE view vv
as
select cpici,
cvalue1=max( case when px=1 then cvalue else 0 end),
cvalue2=max( case when px=2 then cvalue else 0 end),
cvalue3=max( case when px=3 then cvalue else 0 end),
cvalue4=max( case when px=4 then cvalue else 0 end)
from (
select *,px=(select count(distinct cvalue) from tb1 where cpici=t.cpici and cvalue<=t.cvalue) from tb1 t
)b
group by cpici
go
select * from vvcpici cvalue1 cvalue2 cvalue3 cvalue4
---------- ----------- ----------- ----------- -----------
T501 5 31 33 0
T502 3 22 0 0
T503 23 44 50 53(3 行受影响)
go
CREATE table tb1 --数据表
(
cpici varchar(10) not null,
cname varchar(10) not null,
cvalue int null
)
--插入测试数据
INSERT INTO tb1 values('T501','x1',31)
INSERT INTO tb1 values('T501','x1',33)
INSERT INTO tb1 values('T501','x1',5)INSERT INTO tb1 values('T502','x1',3)
INSERT INTO tb1 values('T502','x1',22)
INSERT INTO tb1 values('T502','x1',3)INSERT INTO tb1 values('T503','x1',53)
INSERT INTO tb1 values('T503','x1',44)
INSERT INTO tb1 values('T503','x1',50)
INSERT INTO tb1 values('T503','x1',23)
--在sqlserver2000里需要用自增辅助
alter table tb1 add id int identity
go
declare @s varchar(8000)
set @s='select cpici '
select @s=@s+',max(case when rn='+ltrim(rn)+' then cvalue end) as cvlue'+ltrim(rn)
from (select distinct rn from (select rn=(select count(1) from tb1 where cpici=t.cpici and id<=t.id) from tb1 t)a)t
set @s=@s+' from (select rn=(select count(1) from tb1 where cpici=t.cpici and id<=t.id),* from tb1 t
) t group by cpici'exec(@s)
go
alter table tb1 drop column id --再2005就可以用row_number
declare @s varchar(8000)
set @s='select cpici '
select @s=@s+',max(case when rn='+ltrim(rn)+' then cvalue end) as cvlue'+ltrim(rn)
from (select distinct rn from (select rn=row_number()over(partition by cpici order by getdate()) from tb1)a)t
set @s=@s+' from (select rn=row_number()over(partition by cpici order by getdate()),* from tb1
) t group by cpici'exec(@s)---结果
/*
cpici cvlue1 cvlue2 cvlue3 cvlue4
---------- ----------- ----------- ----------- -----------
T501 31 33 5 NULL
T502 3 22 3 NULL
T503 53 44 50 23
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)*/