594
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([ID] int,[name] nvarchar(22),[num] int)
Insert #T1
select 1,N'张三',96 union all
select 2,N'李四',98 union all
select 3,N'赵六',90 union all
select 4,N'陈二',86
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([ID] int,[name] nvarchar(22),[unit] nvarchar(22))
Insert #T2
select 1,N'张三',N'磅' union all
select 2,N'王五',N'公斤' union all
select 3,N'刘一',N'磅' union all
select 4,N'赵六',N'磅'
Go
--测试数据结束
SELECT #T1.* ,
#T2.unit
FROM #T1
LEFT JOIN #T2 ON #T2.name = #T1.name
--不需要表3, 表3是多余的,要维护一个多余的表是非常麻烦的
CREATE VIEW view_3
AS
SELECT
t1.id
,t1.[name]
,t1.num
,t1.unit
FROM t1 INNER JOIN t2
ON t1.id=t2.id --t1.[name]=t2.[name] 你的要求是用 name, 但一般用主键 id 更合适
select a.*,b.unit from 表1 a
left join 表2 b on a.name=b.name