SQL Server 2012:JOIN 3 个表的条件 [英] SQL Server 2012: JOIN 3 tables for a condition

问题描述

我有 3 个表:TABLEA (ID INT, name VARCHAR(2))

I have 3 tables: TABLEA (ID INT, name VARCHAR(2))

ID  Name 
01  A4
01  SH
01  9K
02  M1
02  L4
03  2G
03  99

TableB(名称 VARCHAR(2))

TableB(Name VARCHAR(2))

Name 
5G
U8
02
45
23
J7
99
9F
A4
H2....

TableC(ID INT, Name VARCHAR(2))(预填充来自 tableA 的 ID)(与 A 的记录数相同)

TableC(ID INT, Name VARCHAR(2)) (prepopulated with ID from tableA) (same number of records as A)

ID  Name 
01  NULL
01  NULL
01  NULL
02  NULL
02  NULL
03  NULL
03  NULL

我想从 B.Name 填充 C.Name 以便对于相同的 ID(比如 1)，它应该具有与 A.Name 不同的值.因此，C.Name 不能有 ID = 1 的 (A4, SH, 9K)，因为它们已经存在于 A.name 中.此外，A.name 可能存在也可能不存在于 B.name 中.

I want to populate C.Name from B.Name so that for same ID (say 1), it should have different values than A.Name . So, C.Name can not have (A4, SH, 9K) for ID = 1 because they already exist in A.name. Also, A.name may or maynot exist in B.name.

这里的问题是我在 tableB 中没有其他列.我需要更多的列来加入我的表吗?谢谢你的帮助！

Problem here is that I dont have additional columns in tableB. Do I need more columns to JOIN my tables? Thanks for your help!

推荐答案

您可以在 update 子句中使用相当低效的嵌套查询结构来实现这一点.

You can do this with a rather inefficient, nested query structure in an update clause.

在 SQL Server 语法中:

In SQL Server syntax:

update tableC
    set Name = (select top 1 b.name
                from TableB b 
                where b.name not in (select name from TableA a where a.id = TableC.id)
                order by NEWID()
               )

最里面的select 来自TableA 从同一个id 中获取所有的名字.where 子句选择不在此列表中 的名称.order by () limit 1 随机选择一个名字.

The inner most select from TableA gets all the names from the same id. The where clause chooses names that are not in this list. The order by () limit 1 randomly selects one of the names.

根据我对问题的理解，这是一个有效的代码示例:

Here is an example of the code that works, according to my understanding of the problem:

declare @tableA table (id int, name varchar(2));
declare @tableB table (name varchar(2));
declare @tableC table (id int, name varchar(2))

insert into @tableA(id, name)
    select 01, 'A4' union all
    select 01, 'SH' union all
    select 01, '9K' union all
    select 02, 'M1' union all
    select 02, 'L4' union all
    select 03, '2G' union all
    select 03, '99';

insert into @tableB(name)
    select '5G' union all
    select 'U8' union all
    select '02' union all
    select '45' union all
    select '23' union all
    select 'J7' union all
    select '99' union all
    select '9F' union all
    select 'A4' union all
    select 'H2';


insert into @tableC(id)
    select 01 union all
    select 01 union all
    select 01 union all
    select 02 union all
    select 02 union all
    select 03 union all
    select 03;

/*    
select * from @tableA;
select * from @tableB;
select * from @tableC;
 */

update c
    set Name = (select top 1 b.name
                from @TableB b 
                where b.name not in (select name from @TableA a where a.id = c.id)
                order by NEWID()
               )
from @tableC c

select *
from @tableC

这篇关于SQL Server 2012:JOIN 3 个表的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！