select a.[],b.name as shuxing1,c.name as shuxing2,d.name as shuxing3,... from tb a left join newtable b on a.col1 = b.id left join newtable c on a.col2 = c.id left join newtable d on a.col3 = d.id where ....
sql学的不咋地,平常遇到问题都是想办法在编程上想办法,谢谢二位了。
--> 生成测试数据表: [t1] IF OBJECT_ID('[t1]') IS NOT NULL DROP TABLE [t1] GO CREATE TABLE [t1] ([Aid] [int],[名字] [nvarchar](10),[属性1] [int],[属性2] [int],[属性3] [int]) INSERT INTO [t1] SELECT '1','name1','1','3','5' UNION ALL SELECT '2','name2','2','4','6'--> 生成测试数据表: [t2] IF OBJECT_ID('[t2]') IS NOT NULL DROP TABLE [t2] GO CREATE TABLE [t2] ([Bid] [int],[名字] [nvarchar](10),[类型] [nvarchar](10)) INSERT INTO [t2] SELECT '1','value1','属性1' UNION ALL SELECT '2','value2','属性1' UNION ALL SELECT '3','value3','属性2' UNION ALL SELECT '4','value4','属性2' UNION ALL SELECT '5','value5','属性3' UNION ALL SELECT '6','value6','属性3' -->SQL查询如下: SELECT a.Aid,a.名字,b.名字 属性1,c.名字 属性2,d.名字 属性3 FROM [t1] a JOIN [t2] b ON a.属性1=b.bid JOIN [t2] c ON a.属性2=c.bid JOIN [t2] d ON a.属性3=d.bid /* Aid 名字 属性1 属性2 属性3 ----------- ---------- ---------- ---------- ---------- 1 name1 value1 value3 value5 2 name2 value2 value4 value6(2 行受影响) */
select a.[],b.name as shuxing1,c.name as shuxing2,d.name as shuxing3,...
from tb a left join newtable b on a.col1 = b.id
left join newtable c on a.col2 = c.id
left join newtable d on a.col3 = d.id
where ....
sql学的不咋地,平常遇到问题都是想办法在编程上想办法,谢谢二位了。
IF OBJECT_ID('[t1]') IS NOT NULL
DROP TABLE [t1]
GO
CREATE TABLE [t1] ([Aid] [int],[名字] [nvarchar](10),[属性1] [int],[属性2] [int],[属性3] [int])
INSERT INTO [t1]
SELECT '1','name1','1','3','5' UNION ALL
SELECT '2','name2','2','4','6'--> 生成测试数据表: [t2]
IF OBJECT_ID('[t2]') IS NOT NULL
DROP TABLE [t2]
GO
CREATE TABLE [t2] ([Bid] [int],[名字] [nvarchar](10),[类型] [nvarchar](10))
INSERT INTO [t2]
SELECT '1','value1','属性1' UNION ALL
SELECT '2','value2','属性1' UNION ALL
SELECT '3','value3','属性2' UNION ALL
SELECT '4','value4','属性2' UNION ALL
SELECT '5','value5','属性3' UNION ALL
SELECT '6','value6','属性3'
-->SQL查询如下:
SELECT a.Aid,a.名字,b.名字 属性1,c.名字 属性2,d.名字 属性3
FROM [t1] a
JOIN [t2] b ON a.属性1=b.bid
JOIN [t2] c ON a.属性2=c.bid
JOIN [t2] d ON a.属性3=d.bid
/*
Aid 名字 属性1 属性2 属性3
----------- ---------- ---------- ---------- ----------
1 name1 value1 value3 value5
2 name2 value2 value4 value6(2 行受影响)
*/