表A 字段A B 表B 字段A B
1 1 1 1
2 2 1 2
3 3 2 1
4 4 3 1
5 5 3 2
3 3
3 4
关联条件 A.A = B.A
问题一 : 查询表A中所对应表B含有2条记录的数据 (即查询出1 ,1这条数据)
问题二 :更新表A的B字段,使他成为表B所对应字段的最大值 (即结果如下)
1 2
2 1
3 4
4 4
5 5
咋写呢 ?脑子转不过来了 。
1 1 1 1
2 2 1 2
3 3 2 1
4 4 3 1
5 5 3 2
3 3
3 4
关联条件 A.A = B.A
问题一 : 查询表A中所对应表B含有2条记录的数据 (即查询出1 ,1这条数据)
问题二 :更新表A的B字段,使他成为表B所对应字段的最大值 (即结果如下)
1 2
2 1
3 4
4 4
5 5
咋写呢 ?脑子转不过来了 。
1 1
2 2
3 3
4 4
5 5
表B
1 1
1 2
2 1
3 1
3 2
3 3
3 4
csdn不能编辑帖子 真是的
create table A(A int, B int)
create table B(A int, B int)
insert into a values(1 ,1)
insert into a values(2 ,2)
insert into a values(3 ,3)
insert into a values(4 ,4)
insert into a values(5 ,5)
insert into b values(1 ,1)
insert into b values(1 ,2)
insert into b values(2 ,1)
insert into b values(3 ,1)
insert into b values(3 ,2)
insert into b values(3 ,3)
insert into b values(3 ,4)
goselect m.* from a m,
(select a , count(1) cnt from b group by a) n
where m.a = n.a and n.cnt = 2/*
A B
----------- -----------
1 1(所影响的行数为 1 行)*/drop table a , b
create table B(A int, B int)
insert into a values(1 ,1)
insert into a values(2 ,2)
insert into a values(3 ,3)
insert into a values(4 ,4)
insert into a values(5 ,5)
insert into b values(1 ,1)
insert into b values(1 ,2)
insert into b values(2 ,1)
insert into b values(3 ,1)
insert into b values(3 ,2)
insert into b values(3 ,3)
insert into b values(3 ,4)
goupdate a set b = isnull((select max(b) from b where b.a = a.a),a.b) from aselect * from a/*
A B
----------- -----------
1 2
2 1
3 4
4 4
5 5(所影响的行数为 5 行)
*/drop table a , b
declare @表A table (A int,B int)
insert into @表A
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4 union all
select 5,5 declare @表B table (A int,B int)
insert into @表B
select 1,1 union all
select 1,2 union all
select 2,1 union all
select 3,1 union all
select 3,2 union all
select 3,3 union all
select 3,4
select A,B from @表A a
where (select count(1) from @表B where A=a.A)=2
/*
A B
----------- -----------
1 1
*/
create table A(A int, B int)
create table B(A int, B int)
insert into a values(1 ,1)
insert into a values(2 ,2)
insert into a values(3 ,3)
insert into a values(4 ,4)
insert into a values(5 ,5)
insert into b values(1 ,1)
insert into b values(1 ,2)
insert into b values(2 ,1)
insert into b values(3 ,1)
insert into b values(3 ,2)
insert into b values(3 ,3)
insert into b values(3 ,4)
goselect a.* from a , b where a.a = b.a group by a.a , a.b having count(1) = 2/*
A B
----------- -----------
1 1(所影响的行数为 1 行)*/
declare @表A table (A int,B int)
insert into @表A
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4 union all
select 5,5 declare @表B table (A int,B int)
insert into @表B
select 1,1 union all
select 1,2 union all
select 2,1 union all
select 3,1 union all
select 3,2 union all
select 3,3 union all
select 3,4 --问题一
select A,B from @表A a
where (select count(1) from @表B where A=a.A)=2
/*
A B
----------- -----------
1 1
*/--问题二update @表A set B=b.B
from @表A a left join
(select A,max(B) as B from @表B group by A)
b on a.A=b.A where b.A is not nullselect * from @表A
/*
A B
----------- -----------
1 2
2 1
3 4
4 4
5 5
*/