22,210
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE tabA(
A1 varchar(20),A2 varchar(20),A3 varchar(20),A4 varchar(20),A5 VARCHAR(20)
)
CREATE TABLE TabB(
B1 VARCHAR(20),B2 VARCHAR(20)
)
INSERT INTO TabB
SELECT 'A1','A1' UNION all
SELECT 'A2','年龄' UNION all
SELECT 'A3','大小' UNION all
SELECT 'A4','性别' UNION all
SELECT 'A5','住址'
--测试数据结束
select a.name,c.B2 from sys.columns AS a
INNER JOIN sys.objects AS b ON a.[object_id]=b.[object_id]
INNER JOIN TabB AS c ON a.name=c.B1
WHERE b.name='Taba' AND c.B2 LIKE '%[吖-座]%'
name B2
-------------------------------------------------------------------------------------------------------------------------------- --------------------
A2 年龄
A3 大小
A4 性别
A5 住址
SELECT * FROM B_TableName
WHERE B1 NOT IN ('A1')
AND B1 IN (SELECT a.name FROM sys.columns a, sys.tables b
WHERE a.object_id=b.object_id AND b.name='A_TableName')
WITH A_Cols AS
(SELECT a.name as B1 FROM sys.columns AS a INNER JOIN sys.tables AS b
WHERE a.object_id=b.object_id
AND b.name='A_TableName')
SELECT a.B1, b.B2
FROM A_Cols a
LEFT JOIN B_TableName b ON a.B1=b.B1
WHERE a.B1 NOT IN ('A1')
WITH A_Cols AS
(SELECT a.name as B1 FROM sys.columns AS a INNER JOIN sys.tables AS b
WHERE a.object_id=b.object_id
AND b.name='A_TableName')
SELECT a.B1, b.B2
FROM A_Cols a
LEFT JOIN B_TableName b ON a.B1=b.B1
WHERE a.name NOT IN ('A1')
SELECT * FROM B_TableName a, sys.columns b, sys.tables c
WHERE B1 NOT IN ('A1')
AND a.B1 = b.name
AND b.object_id=c.object_id
AND c.name = 'A1_TableName'