select pat,vid,a1,a2 from bendor group by pat,vid order by pat,vid
--建立測試環境 create table bendor(vid varchar(10),pat varchar (10),a1 money,a2 money) insert into bendor select 'v1', ' 1_b' , 0.7, 0.7 union all select 'v2', '1_b' , 0.3 , 0.2 union all select 'v3', '1_b' , 0.45, 0.34 union all select 'v4' , '2_b' , 0.3, 0.34 union all select 'v2', ' 2_b' , 0.33, 0.31 --測試 select identity(int,1,1) as [id],* into #temp from bendor drop table bendor select * into bendor from #tempCreate function up_pat(@pat varchar(50),@vid varchar(50)) returns varchar(200) as begin declare @a varchar(20) if exists ( select pat from( select B.vid,B.pat from bendor B,(select min(id) as id from bendor group by pat) C where C.id=B.id) as A where A.vid=@vid and A.pat=@pat ) print 'good' select @a=pat from (select B.vid,B.pat from bendor B,(select min(id) as id from bendor group by pat) C where C.id=B.id) as A where A.vid=@vid and A.pat=@pat else select @a='' from ( select B.vid,B.pat from bendor B,(select min(id) as id from bendor group by pat) C where C.id=B.id) as A where A.vid<>@vid and A.pat=@pat return @a end goUpdate bendor set pat=dbo.up_pat(pat,vid) alter table bendor drop column [id] select * from bendor我的好麻煩不過結果是你想的了。 哈哈哈 大家有什么好方法﹗﹗
select identity(int,1,1) as [id],* into #temp from bendor drop table bendor select * into bendor from #temp 可以改為以下的語句實現 alter table bendor add [id] int identity
create table bendor(vid varchar(10),pat varchar (10),a1 money,a2 money)
insert into bendor
select 'v1', ' 1_b' , 0.7, 0.7 union all
select 'v2', '1_b' , 0.3 , 0.2 union all
select 'v3', '1_b' , 0.45, 0.34 union all
select 'v4' , '2_b' , 0.3, 0.34 union all
select 'v2', ' 2_b' , 0.33, 0.31
--測試
select identity(int,1,1) as [id],* into #temp from bendor
drop table bendor
select * into bendor from #tempCreate function up_pat(@pat varchar(50),@vid varchar(50))
returns varchar(200)
as
begin
declare @a varchar(20)
if exists
(
select pat from( select B.vid,B.pat from bendor B,(select min(id) as id from bendor group by pat) C where C.id=B.id) as A
where A.vid=@vid and A.pat=@pat
)
print 'good'
select @a=pat from (select B.vid,B.pat from bendor B,(select min(id) as id from bendor group by pat) C where C.id=B.id) as A
where A.vid=@vid and A.pat=@pat
else
select @a='' from ( select B.vid,B.pat from bendor B,(select min(id) as id from bendor group by pat) C where C.id=B.id) as A
where A.vid<>@vid and A.pat=@pat
return @a
end
goUpdate bendor set pat=dbo.up_pat(pat,vid)
alter table bendor drop column [id]
select * from bendor我的好麻煩不過結果是你想的了。
哈哈哈
大家有什么好方法﹗﹗
select identity(int,1,1) as [id],* into #temp from bendor
drop table bendor
select * into bendor from #temp
可以改為以下的語句實現
alter table bendor add [id] int identity
一个都经不住考验.