救命!我已经没有力气喊了!
高分求一程序。具体如下：
表:product(id,pinpai,xinghao)
要求：字段pinpai对应于不同的字段xinghao
用程序如何控制。各位大吓帮忙啊。
例：id pinpai xinghao
1 1 a
2 1 b
3 5 c
4 9 d
5 5 m
要求显示结果如下：
pinpai xinghao1 xinghao2
1 a b
5 c m
9 d null
大家帮帮我吧。
问题点数：100、回复次数：16Top
1 楼xxrl（孔曰成仁,孟曰取媳妇-->她太有才了）回复于 2003-12-02 17:48:31 得分 0
求一sql语句Top
2 楼txlicenhe（马可）回复于 2003-12-02 17:50:51 得分 0
try:
select pinpai,
(select xinghao from 表 where pinpai = a.pinpai and
id = (select min(id) from 表 where pinpai = a.pinpai)) as xinghao1,
(select xinghao from 表 where pinpai = a.pinpai and
id <> (select min(id) from 表 where pinpai = a.pinpai)) as xinghao2
from 表 a
Top
3 楼xxrl（孔曰成仁,孟曰取媳妇-->她太有才了）回复于 2003-12-02 17:54:38 得分 0
不行啊,怎么办啊!!
关select xinghao from 表 where pinpai = a.pinpai这个就不对嘛,
5555555
谁能帮我?
我已经加了80分了!!!
555555Top
4 楼wzh1215（懒猫）回复于 2003-12-02 17:59:47 得分 10
try:
select a.pinpai,
(select b.xinghao from 表 b where b.pinpai = a.pinpai and
b.id = (select min(c.id) from 表 c where c.pinpai = a.pinpai)) as xinghao1,
(select b.xinghao from 表 where b.pinpai = a.pinpai and
b.id <> (select min(c.id) from 表 c where c.pinpai = a.pinpai)) as xinghao2
from 表 aTop
5 楼dlpseeyou（豆子）回复于 2003-12-02 18:06:57 得分 10
select distinct(pinpai),
(select xinghao from test1 where pinpai = a.pinpai and
id = (select min(id) from test1 where pinpai = a.pinpai)) as xinghao1,
(select xinghao from test1 where pinpai = a.pinpai and
id <> (select min(id) from test1 where pinpai = a.pinpai)) as xinghao2
from test1 a
我试了Top
6 楼txlicenhe（马可）回复于 2003-12-02 18:09:50 得分 10
create table product(id int identity,pinpai int,xinghao char(1))
insert product values(1,'a')
insert product values(1,'b')
insert product values(5,'c')
insert product values(9,'d')
insert product values(5,'m')
select distinct pinpai,
(select xinghao from product where pinpai = a.pinpai and
id = (select min(id) from product where pinpai = a.pinpai)) as xinghao1,
(select xinghao from product where pinpai = a.pinpai and
id <> (select min(id) from product where pinpai = a.pinpai)) as xinghao2
from product a
pinpai xinghao1 xinghao2
----------- -------- --------
1 a b
5 c m
9 d NULL
（所影响的行数为 3 行）
Top
7 楼wzh1215（懒猫）回复于 2003-12-02 18:15:07 得分 10
select pinpai,xinghao1,(case when xinghao1=xinghao2 then NULL else xinghao2 end) from
(select pinpai,min(xinghao) as xinghao1,max(xinghao) as xinghao2 from product group by pinpai) tep
Top
8 楼ghosthjt（天煞孤星）回复于 2003-12-02 18:59:47 得分 10
如果还有型号3呢，4号，不是要累死了，所以，楼上的做法都是不可取的。
Top
9 楼ghosthjt（天煞孤星）回复于 2003-12-02 19:16:15 得分 0
发现楼上的只能解决楼主提出的这几条记录的问题，添加一条记录就出错了Top
10 楼Arionma（）回复于 2003-12-02 19:58:38 得分 10
难度比较大，sql里面字段名不可以为变量，所以在插入数据时没法判断，如果写在一个字段里就很简单。
期待高人出现。Top
11 楼zjcxc（邹建）回复于 2003-12-02 20:15:37 得分 30
--动态SQL处理就可以解决你的问题
--数据处理
declare @s varchar(8000),@i int
select @s='',@i=max(aa) from
(select pinpai,aa=count(*) from product group by pinpai) a
while @i>0
select @s=',xinghao'+cast(@i as varchar)+'=max(case sortid when '
+cast(@i as varchar)+' then xinghao end)'+@s
,@i=@i-1
exec('select pinpai'+@s+' from(
select pinpai,xinghao
,sortid=(select sum(1) from product where pinpai=a.pinpai and id<=a.id)
from product a) a group by pinpai')
Top
12 楼zjcxc（邹建）回复于 2003-12-02 20:16:37 得分 0
--下面是数据测试(我加了两个型号)
--创建测试表
create table product(id int identity,pinpai int,xinghao char(1))
insert product values(1,'a')
insert product values(1,'b')
insert product values(1,'c')
insert product values(1,'d')
insert product values(5,'c')
insert product values(9,'d')
insert product values(5,'m')
--数据处理
declare @s varchar(8000),@i int
select @s='',@i=max(aa) from
(select pinpai,aa=count(*) from product group by pinpai) a
while @i>0
select @s=',xinghao'+cast(@i as varchar)+'=max(case sortid when '
+cast(@i as varchar)+' then xinghao end)'+@s
,@i=@i-1
exec('select pinpai'+@s+' from(
select pinpai,xinghao
,sortid=(select sum(1) from product where pinpai=a.pinpai and id<=a.id)
from product a) a group by pinpai')
go
--删除测试表
drop table product
/*--测试结果
pinpai xinghao1 xinghao2 xinghao3 xinghao4
----------- -------- -------- -------- --------
1 a b c d
5 c m NULL NULL
9 d NULL NULL NULL
--*/Top
13 楼zjcxc（邹建）回复于 2003-12-02 20:17:55 得分 0
--为方便调用,可以改为存储过程
--数据处理,改为存储过程
create proc p_qry
as
declare @s varchar(8000),@i int
select @s='',@i=max(aa) from
(select pinpai,aa=count(*) from product group by pinpai) a
while @i>0
select @s=',xinghao'+cast(@i as varchar)+'=max(case sortid when '
+cast(@i as varchar)+' then xinghao end)'+@s
,@i=@i-1
exec('select pinpai'+@s+' from(
select pinpai,xinghao
,sortid=(select sum(1) from product where pinpai=a.pinpai and id<=a.id)
from product a) a group by pinpai')
go
--调用示例
exec p_qry
Top
14 楼ghosthjt（天煞孤星）回复于 2003-12-02 20:31:51 得分 0
下面给出一个效率很低但能解决问题的写法:
create table T3(ID1 int,pingtai char(10),xinghao char(10))
delete from t3
insert into t3 values(1,'1','a')
insert into t3 values(2,'1','b')
insert into t3 values(3,'5','c')
insert into t3 values(4,'9','d')
insert into t3 values(5,'5','m')
insert into t3 values(6,'1','g')
declare @SQL varchar(8000)
declare @MaxV int
declare @i int
create table #t11(v1 int)
insert into #t11
select count(xinghao) from t3 group by pingtai
select @maxv=(select max(v1) from #t11)
print @maxv
set @sql='create table tmpTabel(pingtai varchar(10)'
set @i=1
while @maxv>=@i
begin
set @sql=@sql+',xinghao'+convert(varchar,@i) +' varchar(18)'
set @i=@i+1
end
set @sql=@sql+')'
print @sql
exec (@sql)
declare Tmpcur cursor scroll for select distinct pingtai from t3
declare @pingtai varchar(10)
open tmpcur
fetch next from tmpcur into @pingtai
while @@fetch_status=0
begin
declare tmpcur1 cursor scroll for select xinghao from t3 where pingtai=@pingtai
declare @value varchar(10)
open tmpcur1
fetch next from tmpcur1 into @value
set @i=1
while @@fetch_status=0
begin
select * from tmpTabel where pingtai=@pingtai
if @@rowcount=0
set @sql='insert into tmpTabel(pingtai,xinghao'+convert(varchar,@i)+')
values('''+@pingtai+''','''+@value+''')'
else
set @sql='update tmpTabel set xinghao'+convert(varchar,@i)+'='''+@value+'''where pingtai='''+@pingtai+''''
print @sql
exec(@sql)
set @i=@i+1
fetch next from tmpcur1 into @value
end
close tmpcur1
deallocate tmpcur1
fetch next from tmpcur into @pingtai
end
close tmpcur
deallocate tmpcur
select * from tmpTabel
drop table tmpTabel
drop table t3Top
15 楼ghosthjt（天煞孤星）回复于 2003-12-02 20:37:05 得分 10
结果为：
pingtai xinghao1 xinghao2 xinghao3
---------- ------------------ ------------------ ------------------
1 a b g
5 c m NULL
9 d NULL NULLTop
16 楼pengdali（）回复于 2003-12-02 20:59:08 得分 0
呵呵。我当是什么那。Top
