TSQL，基于非主键的联接表和左表保持不变。
TSQL，基于非主键的联接表和左表保持不变。
提问于 2015-06-10 08:38:59
如何查询才能得到预期的结果？
Table A
+----+-------+-------------+------------+
+ ID + Model + Description + Material +
+----+-------+-------------+------------+
+ 1 + M1 + CHASIS + AC001 +
+ 2 + M1 + SCREW + AS001 +
+ 3 + M1 + SEAL + ASE01 +
+ 4 + M1 + SEAL + ASE02 +
+ 5 + M1 + SEAL + ASE03 +
+----+-------+-------------+------------+
Table B
+-------+-------------+----------+
+ Model + Description + Material +
+---------+-----------+----------+
+ M2 + CHASIS + BC001 +
+ M2 + ABS + BS001 +
+ M2 + SEAL + BSE01 +
+ M2 + SEAL + BSE02 +
+-------+-------------+----------+
Expected Result
+------+--------+---------------+------------+---------+-----------+
+ A.ID + A.Model+ A.Description + A.Material + B.Model + B.Material+
+------+--------+---------------+------------+---------+-----------+
+ 1 + M1 + CHASIS + AC001 + M2 + BC001 +
+ 2 + M1 + SCREW + AS001 + NULL + NULL +
+ 3 + M1 + SEAL + ASE01 + M2 + BSE01 +
+ 4 + M1 + SEAL + ASE02 + M2 + BSE02 +
+ 5 + M1 + SEAL + ASE03 + NULL + NULL +
+------+--------+---------------+------------+---------+-----------+我在下面尝试过，但是它有重复的SEAL行：
SELECT A.ID, A.model, A.description, A.material,
B.model, B.description, B.material
FROM A
LEFT JOIN B
ON ( A.Description = B.Description and B.model = 'M2')
where A.model = 'M1' 我想基于Description连接两个表。Table A中的序列和行应该保持不变。
回答 1
Stack Overflow用户
回答已采纳
发布于 2015-06-10 08:56:54
您可以尝试以下方法：
select * from
(select *,
row_number() over(partition by Model, Description order by ID) Rn
from TableA) t1
left join
(select *,
row_number() over(partition by Model, Description order by (select null)) Rn
from TableB) t2 on t1.Rn = t2.Rn and t1.Description = t2.Description and t2.Model = 'M2'页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/30751244
复制相关文章
相似问题
腾讯云开发者
