--测试资料 CREATE TABLE tb( ID1 char(2) NOT NULL, ID2 char(4) NOT NULL, col int, PRIMARY KEY(ID1,ID2)) INSERT tb SELECT 'aa','0001',1 UNION ALL SELECT 'aa','0003',2 UNION ALL SELECT 'aa','0004',3 UNION ALL SELECT 'bb','0005',4 UNION ALL SELECT 'bb','0006',5 UNION ALL SELECT 'cc','0007',6 UNION ALL SELECT 'cc','0009',7 GO--重排编号处理 DECLARE @ID1 char(2),@ID2 int UPDATE a SET ID2=RIGHT(10000 +(SELECT COUNT(*) FROM tb WHERE ID1=a.ID1 AND ID2<=a.ID2) ,4) FROM tb a SELECT * FROM tb /*--结果 ID1 ID2 col ---- ---- ----------- aa 0001 1 aa 0002 2 aa 0003 3 bb 0001 4 bb 0002 5 cc 0001 6 cc 0002 7 --*/
啥意思呀? update 表a set val='101' where row=2 insert into 表a select 2,'102' 呵呵
create table test1(id int,value int);insert into test1 values(1,101),(1,102),(2,1);select * from test1;select * from test1 where id=1 union select f1.id,f2.value from f f1,f f2 where f2.id=f1.value;
select * from tbname where id=1 union select f1.id,f2.value from tbname f1,f f2 where f2.id=f1.value;
select distinct b.row,a.val from tempaa a,tempaa b where b.val=a.row or a.row=1
create table test1(id int,value int); insert into test1 values(1,101) insert into test1 values(1,102) insert into test1 values(2,1) goselect * from test1select b.id,isnull(a.value,b.value) as value from test1 a full join test1 b on a.id = b.value where b.id is not nulldrop table test1/*id value ----------- ----------- 1 101 1 102 2 1(所影响的行数为 3 行)id value ----------- ----------- 1 101 1 102 2 101 2 102(所影响的行数为 4 行)*/
CREATE TABLE tb(
ID1 char(2) NOT NULL,
ID2 char(4) NOT NULL,
col int,
PRIMARY KEY(ID1,ID2))
INSERT tb SELECT 'aa','0001',1
UNION ALL SELECT 'aa','0003',2
UNION ALL SELECT 'aa','0004',3
UNION ALL SELECT 'bb','0005',4
UNION ALL SELECT 'bb','0006',5
UNION ALL SELECT 'cc','0007',6
UNION ALL SELECT 'cc','0009',7
GO--重排编号处理
DECLARE @ID1 char(2),@ID2 int
UPDATE a SET ID2=RIGHT(10000
+(SELECT COUNT(*) FROM tb WHERE ID1=a.ID1 AND ID2<=a.ID2)
,4)
FROM tb a
SELECT * FROM tb
/*--结果
ID1 ID2 col
---- ---- -----------
aa 0001 1
aa 0002 2
aa 0003 3
bb 0001 4
bb 0002 5
cc 0001 6
cc 0002 7
--*/
update 表a set val='101' where row=2
insert into 表a select 2,'102'
呵呵
union
select f1.id,f2.value from f f1,f f2 where f2.id=f1.value;
union
select f1.id,f2.value from tbname f1,f f2 where f2.id=f1.value;
where b.val=a.row or a.row=1
insert into test1 values(1,102)
insert into test1 values(2,1)
goselect * from test1select b.id,isnull(a.value,b.value) as value
from test1 a
full join test1 b on a.id = b.value
where b.id is not nulldrop table test1/*id value
----------- -----------
1 101
1 102
2 1(所影响的行数为 3 行)id value
----------- -----------
1 101
1 102
2 101
2 102(所影响的行数为 4 行)*/