34,590
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('TableA') IS NOT NULL DROP TABLE TableA
IF OBJECT_ID('TableB') IS NOT NULL DROP TABLE TableB
GO
CREATE TABLE TableA(
id INT IDENTITY(1,1) PRIMARY KEY,
part VARCHAR(10)
)
CREATE TABLE TableB(
id INT IDENTITY(1,1) PRIMARY KEY,
part VARCHAR(10),
color NVARCHAR(10)
)
GO
INSERT INTO TableA( part ) VALUES('PA1'),('PA2'),('PB1'),('PB2'),('PB3'),('PC1')
INSERT INTO TableB( part,color) VALUES('PA','红'),('PB','绿'),('PC','蓝')
GO
SELECT ta.*,tb.color
FROM TableA AS ta INNER JOIN TableB AS tb ON ta.part LIKE tb.part+'%'
/*
id part color
----------- ---------- ----------
1 PA1 红
2 PA2 红
3 PB1 绿
4 PB2 绿
5 PB3 绿
6 PC1 蓝
*/
--测试数据
if not object_id(N'TableA') is null
drop table TableA
Go
Create table TableA([id] int,[part] nvarchar(23))
Insert TableA
select 1,N'PA1' union all
select 2,N'PA2' union all
select 3,N'PB1' union all
select 4,N'PB2' union all
select 5,N'PB3' union all
select 6,N'PC1'
GO
if not object_id(N'TableB') is null
drop table TableB
Go
Create table TableB([id] int,[part] nvarchar(22),[color] nvarchar(21))
Insert TableB
select 1,N'PA',N'红' union all
select 2,N'PB',N'绿' union all
select 3,N'PC',N'蓝'
Go
--测试数据结束
SELECT
tablea.*,
tableb.color
FROM
tablea
JOIN
tableb
ON LEFT(tablea.part, 2) = tableb.part;
USE tempdb
GO
IF OBJECT_ID('TableA') IS NOT NULL DROP TABLE TableA
IF OBJECT_ID('TableB') IS NOT NULL DROP TABLE TableB
GO
CREATE TABLE TableA(
id INT IDENTITY(1,1) PRIMARY KEY,
part VARCHAR(10)
)
CREATE TABLE TableB(
id INT IDENTITY(1,1) PRIMARY KEY,
part VARCHAR(10),
color NVARCHAR(10)
)
GO
INSERT INTO TableA( part ) VALUES('PA1'),('PA2'),('PB1'),('PB2'),('PB3'),('PC1')
INSERT INTO TableB( part,color) VALUES('PA','红'),('PB','绿'),('PC','蓝')
GO
SELECT ta.*,tb.color
FROM TableA AS ta INNER JOIN TableB AS tb ON LEFT(ta.part,2)=tb.part
/*
id part color
----------- ---------- ----------
1 PA1 红
2 PA2 红
3 PB1 绿
4 PB2 绿
5 PB3 绿
6 PC1 蓝
*/