面试题真不会.
表A
ID (int) name (varchar(50))
ID name
1 中国
2 美国
表B
ID (int) AID (int)
ID AID
1 1
2 2
3 1
表C
ID (int) BID(int) SSS(int) BBB(int)
ID BID SSS BBB
1 1 3 0
2 2 3 0
3 1 2 0
4 1 3 0
5 2 3 0
6 1 9 0
求一个存储过程
exec '美国'
实现 表C中MAX(sss)对应的BBB更新为2 面试题
[解决办法]
declare @id int=0;
select @id =Id from 表A where name='美国'
update 表C
set BBB=@id
where sss=(select MAX(sss) from 表C)
if object_id('Tempdb..#a') is not null drop table #a
if object_id('Tempdb..#b') is not null drop table #b
if object_id('Tempdb..#c') is not null drop table #c
create table #a(
ID int not null,
[name] nvarchar(100) null
)
create table #b(
ID int not null,
[AID] int null
)
create table #c(
ID int not null,
[BID] int null,
[SSS] int null,
[BBB] int null
)
Insert into #a
select 1,'中国' union all
select 2,'美国'
insert into #b
select 1,1 union all
select 2,2 union all
select 3,1
insert into #c
select 1,1,3,0 union all
select 2,2,3,0 union all
select 3,1,2,0 union all
select 4,1,3,0 union all
select 5,2,3,0 union all
select 6,1,9,0
select * from #a
select * from #b
select * from #c
-------------把下边的修改一下即可
declare @name nvarchar(100)
set @name='美国'
;with cte as(
select c.ID,max(c.SSS)sss from #c c
join #b b on b.ID=c.BID
join #a a on a.ID=b.AID
where a.name=@name
group by c.ID
)
update #c set BBB=2 where ID in(select ID from cte)
select * from #c
-------------------------------
ID BID SSS BBB
----------- ----------- ----------- -----------
1 1 3 0
2 2 3 2
3 1 2 0
4 1 3 0
5 2 3 2
6 1 9 0
(6 行受影响)
