求SQL语句，谢谢

Question

需求如下，先谢谢大家。表A：//id是主键
id name bianhao
1  张三 BH001
2  李四 BH002
3  王五 BH003表B：//id是主键和外键，hetongID主键
id hetongID hetongzhuangtai kaishishijian jieshushijian
1  id1      新签 '2001-01-01'
1  id2      续签            '2002-01-01'
1  id3      续签            '2003-01-01'
2  id4      新签            '2008-01-01'我现在要将hetongID字段更新，规则是此人员的人员bianhao+签订次数。
比如张三的第一次合同编号为'BH001-1'
第二次合同编号为'BH001-2'
第一次和第二次的区别在于kaishishijian字段。

Answer 1

update b set b.hetongID=a.bianhao+'-'+(select ltrim(count(1)) from 表B where id=b.id and kaishishijian <=b.kaishishijian )
from 表B b,表A a
where b.id=a.id

Answer 2

if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[name] varchar(4),[bianhao] varchar(5))
insert [A]
select 1,'张三','BH001' union all
select 2,'李四','BH002' union all
select 3,'王五','BH003'
if object_id('[B]') is not null drop table [B]
go
create table [B]([id] int,[hetongID] varchar(30),[hetongzhuangtai] varchar(10),[kaishishijian] datetime,[jieshushijian] datetime)
insert [B]
select 1,'id1','新签','2001-01-01',null union all
select 1,'id2','续签','2002-01-01',null union all
select 1,'id3','续签','2003-01-01',null union all
select 2,'id4','新签','2008-01-01',null
 
update t
set t.hetongID=a.bianhao+'-'+ltrim((select count(1)+1 from b where id=t.id and kaishishijian<t.kaishishijian))
from a
join b t on a.id=t.idselect * from b
/**
id          hetongID                       hetongzhuangtai kaishishijian           jieshushijian
----------- ------------------------------ --------------- ----------------------- -----------------------
1           BH001-1                        新签              2001-01-01 00:00:00.000 NULL
1           BH001-2                        续签              2002-01-01 00:00:00.000 NULL
1           BH001-3                        续签              2003-01-01 00:00:00.000 NULL
2           BH002-1                        新签              2008-01-01 00:00:00.000 NULL(4 行受影响)**/

Answer 3

update b 
set hetongID=a.bianhao+'-'+ltrim((select count(1) from b t where id=b.id and kaishishijian <=b.kaishishijian ))
from a
where b.id=a.id

Answer 4

;with t as(
select [B].*, 
[A].[bianhao]+'-'+ltrim(row_number() over (partition by [B].id order by [B].[kaishishijian])) [bianhao]
from [A] inner join [B] on [A].id=[B].id
)
update t set [hetongID]=bianhao;

Answer 5

update b1
set b1.hetongID=a.bianhao+'-'+ltrim((select count(1)+1 from b where id=b1.id and kaishishijian<b1.kaishishijian))
from a join b b1 on a.id=b1.id

Answer 6

--sql 2000
create table [A]([id] int,[name] varchar(4),[bianhao] varchar(5))
insert [A]
select 1,'张三','BH001' union all
select 2,'李四','BH002' union all
select 3,'王五','BH003'
create table [B]([id] int,[hetongID] varchar(30),[hetongzhuangtai] varchar(10),[kaishishijian] datetime,[jieshushijian] datetime)
insert [B]
select 1,'id1','新签','2001-01-01',null union all
select 1,'id2','续签','2002-01-01',null union all
select 1,'id3','续签','2003-01-01',null union all
select 2,'id4','新签','2008-01-01',nullupdate b
set hetongID = a.bianhao + '-' + ltrim((select count(1) from b where id = t.id and kaishishijian < t.kaishishijian) + 1)
from b t , a
where t.id = a.idselect * from bdrop table a , b/*
id          hetongID                       hetongzhuangtai kaishishijian                                          jieshushijian                                          
----------- ------------------------------ --------------- ------------------------------------------------------ ------------------------------------------------------ 
1           BH001-1                        新签              2001-01-01 00:00:00.000                                NULL
1           BH001-2                        续签              2002-01-01 00:00:00.000                                NULL
1           BH001-3                        续签              2003-01-01 00:00:00.000                                NULL
2           BH002-1                        新签              2008-01-01 00:00:00.000                                NULL（所影响的行数为 4 行）*/

Answer 7

update b set b.hetongID=a.bianhao+'-'+ltrim((select count(1) from b where id = t.id and kaishishijian < t.kaishishijian) + 1)
from 表B b,表A a
where b.id=a.id

Answer 8

IF OBJECT_ID('[A]') IS NOT NULL
    DROP TABLE [A]
GOCREATE TABLE [A]
(
[id]       INT
   ,[name]     VARCHAR(4)
   ,[bianhao]  VARCHAR(5)
)
INSERT [A]
SELECT 1, '张三', 'BH001' UNION ALL
SELECT 2, '李四', 'BH002' UNION ALL
SELECT 3, '王五', 'BH003'IF OBJECT_ID('[B]') IS NOT NULL
    DROP TABLE [B]
GOCREATE TABLE [B]
(
[id]               INT
   ,[hetongID]         VARCHAR(30)
   ,[hetongzhuangtai]  VARCHAR(10)
   ,[kaishishijian]    DATETIME
   ,[jieshushijian]    DATETIME
)
INSERT [B]
SELECT 1, 'id1', '新签', '2001-01-01', NULL UNION ALL
SELECT 1, 'id2', '续签', '2002-01-01', NULL UNION ALL
SELECT 1, 'id3', '续签', '2003-01-01', NULL UNION ALL
SELECT 2, 'id4', '新签', '2008-01-01', NULL
UPDATE b
SET    hetongID = a.bianhao+'-'+LTRIM(
           (
               SELECT COUNT(1) 
               FROM b t
               WHERE id = b.id
                AND kaishishijian<= b.kaishishijian
           )
       )
FROM   a
WHERE  b.id = a.idSELECT * FROM B
/*
id          hetongID                       hetongzhuangtai kaishishijian           jieshushijian
----------- ------------------------------ --------------- ----------------------- -----------------------
1           BH001-1                        新签              2001-01-01 00:00:00.000 NULL
1           BH001-2                        续签              2002-01-01 00:00:00.000 NULL
1           BH001-3                        续签              2003-01-01 00:00:00.000 NULL
2           BH002-1                        新签              2008-01-01 00:00:00.000 NULL(4 行受影响)
*/

Answer 9

请问 htl258  ，你的语句中，  能帮我把每个字段都加上表前缀吗？
谢谢，我有些字段不知道是哪个表的