请教大家个SQL 语句:
表A结构和内容:
(列名) ab a
(值) 1 111
1 123
2 222
2 321
表B结构和内容
(列名) ab b
(值) 1 NULL
2 NULL
2 NULL
1 NULL现在想更新表B,使得在A.ab=B.ab的条件下,B.b的值要更新为A.a的第一个值;
因此上面的例子更新完表B后结果是:
(列名) ab b
(值) 1 111
2 222
2 222
1 111非常感谢!!!
表A结构和内容:
(列名) ab a
(值) 1 111
1 123
2 222
2 321
表B结构和内容
(列名) ab b
(值) 1 NULL
2 NULL
2 NULL
1 NULL现在想更新表B,使得在A.ab=B.ab的条件下,B.b的值要更新为A.a的第一个值;
因此上面的例子更新完表B后结果是:
(列名) ab b
(值) 1 111
2 222
2 222
1 111非常感谢!!!
set b=aa.a
from A aa
where b.ab=aa.ab and not exists(select * from A where a.ab=aa.ab and a<aa.a)
;with cte1 as
(
select * from A join B on A.ab=B.ab and a=(select min(a) from B where ab=A.ab)
)update cte1 set a=b
update B
set b=T.b
from (select AB,MIN(b) as b from A group by ab) t
where b.ab=T.ab
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('A') IS NOT NULL
DROP TABLE A
GO
CREATE TABLE A( AB INT,A INT )
go
insert A SELECT
1 , 111 UNION ALL SELECT
1 , 123 UNION ALL SELECT
2 , 222 UNION ALL SELECT
2 , 321
GO
IF OBJECT_ID('b') IS NOT NULL
DROP TABLE b
GO
CREATE TABLE b( AB INT,b INT )
go
insert b SELECT
1 , NULL UNION ALL SELECT
2 , NULL UNION ALL SELECT
2 , NULL UNION ALL SELECT
1 , NULL
go
update B
set b=T.b
from (select AB,MIN(a) as b from A group by ab) t
where b.ab=T.ab
select * from b
go
/*------------
4 行受影响)(4 行受影响)
AB b
----------- -----------
1 111
2 222
2 222
1 111-------*/
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('A') IS NOT NULL
DROP TABLE A
GO
CREATE TABLE A( AB INT,A INT )
go
insert A SELECT
1 , 111 UNION ALL SELECT
1 , 123 UNION ALL SELECT
2 , 222 UNION ALL SELECT
2 , 321
GO
IF OBJECT_ID('b') IS NOT NULL
DROP TABLE b
GO
CREATE TABLE b( AB INT,b INT )
go
insert b SELECT
1 , NULL UNION ALL SELECT
2 , NULL UNION ALL SELECT
2 , NULL UNION ALL SELECT
1 , NULL
go
update B
set b=aa.a
from A aa
where b.ab=aa.ab and not exists(select * from A where a.ab=aa.ab and a<aa.a)
select * from b
go
/*------------
4 行受影响)(4 行受影响)
AB b
----------- -----------
1 111
2 222
2 222
1 111-------*/
insert into a values(1,111)
insert into a values(1,123)
insert into a values(2,222)
insert into a values(2,234)create table b(ab int,b int)
insert into b values(1,null)
insert into b values(1,null)
insert into b values(2,null)
insert into b values(2,null)
update b set b.b=c.b from a as c
where c.ab=b.ab
and
exists (select 1 from a where ab=c.ab and b<c.b)
/*ab b
----------- -----------
1 123
1 123
2 234
2 234(4 行受影响)*/code]
insert into a values(1,111)
insert into a values(1,123)
insert into a values(2,222)
insert into a values(2,234) create table b(ab int,b int)
insert into b values(1,null)
insert into b values(1,null)
insert into b values(2,null)
insert into b values(2,null)
update b set b.b=c.b from a as c
where c.ab=b.ab
and
exists (select 1 from a where ab=c.ab and b <c.b)
/*ab b
----------- -----------
1 123
1 123
2 234
2 234 (4 行受影响)*/
declare @t table(ab int,a int)
insert into @t
select 1,111 union all
select 1 , 123 union all
select 2 ,222 union all
select 2 , 321
declare @t1 table(ab int,b int)
insert into @t1
select 1,null union all
select 2 , null union all
select 2 ,null union all
select 1 , null
;with cte1 as
(
select A.ab,a,b from @t A join @t1 B on A.ab=B.ab and a=(select min(a) from @t where ab=A.ab) )
update cte1 set b=aselect * from @t1/*ab b
----------- -----------
1 111
2 222
2 222
1 111(4 行受影响)*/