求SQL语句，有没有周末上班的高手，给帮个忙吧！

版本:sql2000SP4
OS:win2003

问题：我有一个如图所示的表

需求：我想得到下面的表格：并且不想用临时表，最好是视图，可以有中间视图。


cpicicvalue1,cvalue2,cvalue3,cvalue4
-----------------
T501|31|33|5|
T502|22|3||
T503|23|44|50|53

数据：


CREATEtabletb1--数据表
(
cpicivarchar(10)notnull,
cnamevarchar(10)notnull,
cvalueintnull
)
--插入测试数据
INSERTINTOtb1values('T501','x1',31)
INSERTINTOtb1values('T501','x1',33)
INSERTINTOtb1values('T501','x1',5)

INSERTINTOtb1values('T502','x1',3)
INSERTINTOtb1values('T502','x1',22)
INSERTINTOtb1values('T502','x1',3)

INSERTINTOtb1values('T503','x1',53)
INSERTINTOtb1values('T503','x1',44)
INSERTINTOtb1values('T503','x1',50)
INSERTINTOtb1values('T503','x1',23)

SELECT*FROMtb1

5 个解决方案

#1

ifobject_id('tb1')isnotnulldroptabletb1
go
CREATEtabletb1--数据表
(
cpicivarchar(10)notnull,
cnamevarchar(10)notnull,
cvalueintnull
)
--插入测试数据
INSERTINTOtb1values('T501','x1',31)
INSERTINTOtb1values('T501','x1',33)
INSERTINTOtb1values('T501','x1',5)

INSERTINTOtb1values('T502','x1',3)
INSERTINTOtb1values('T502','x1',22)
INSERTINTOtb1values('T502','x1',3)

INSERTINTOtb1values('T503','x1',53)
INSERTINTOtb1values('T503','x1',44)
INSERTINTOtb1values('T503','x1',50)
INSERTINTOtb1values('T503','x1',23)

----------------------------------



ifobject_id('vv')isnotnulldropviewvv
go
CREATEviewvv
as

	selectcpici,
		cvalue1=max(casewhenpx=1thencvalueelse0end),
		cvalue2=max(casewhenpx=2thencvalueelse0end),
		cvalue3=max(casewhenpx=3thencvalueelse0end),
		cvalue4=max(casewhenpx=4thencvalueelse0end)
	from(
		select*,px=(selectcount(distinctcvalue)fromtb1wherecpici=t.cpiciandcvalue<=t.cvalue)fromtb1t
	)b
	groupbycpici

go


select*fromvv

cpicicvalue1cvalue2cvalue3cvalue4
------------------------------------------------------
T501531330
T50232200
T50323445053

(3行受影响)

5 个解决方案

#1

更多相关文章

随机推荐