如何使用表中可能不可用的 ID 构建 SQL 语句？

发布于 2024-11-10 08:18:55 字数 553 浏览 0 评论 0原文

使用 Microsoft SQL Server 2008，假设有一个 table1 保存所选的省、区、公社和村庄的 ID。然后是表2，其中包含省、区、公社和村庄的ID和名称。省份和地区为必填字段，并且始终会被填满。公社和村庄可能会被填满，但甚至可能不会被填满，因为它们不是必需的。

在不知道公社和村庄的 id 是否填充在 table1 中的情况下，构建动态 SQL 语句的最佳方法是什么？

SELECT tbl1.province, tbl1.district, tbl1.commune, tbl1.village 
  FROM dbo.table1 AS tbl1 
   AND dbo.table2 AS tbl2 
 WHERE tbl1.province = tbl2.province_id
   AND tbl1.district = tbl2.district_id 
   AND tbl1.commune = tbl2.commune_id 
   AND tbl1.village = tbl2.village_id

如果 table1 中的 id 未填充，则此语句会给出错误结果。

原文

Using Microsoft SQL Server 2008, let's say there is a table1 that keeps the selected ids of provinces, districts, communes and villages. And then there is table2 with the ids and names of provinces, districts, communes and villages. Provinces and districts are required fields and will always be filled. Communes and villages might be filled but might even not be filled as they are not required.

What is the best way to build a dynamical SQL statement without knowing if the ids for communes and villages are filled in table1 or not.

SELECT tbl1.province, tbl1.district, tbl1.commune, tbl1.village 
  FROM dbo.table1 AS tbl1 
   AND dbo.table2 AS tbl2 
 WHERE tbl1.province = tbl2.province_id
   AND tbl1.district = tbl2.district_id 
   AND tbl1.commune = tbl2.commune_id 
   AND tbl1.village = tbl2.village_id

This statement gives wrong results if the id in table1 is not filled.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

锦欢 2024-11-17 08:18:55

OUTER JOIN 在这里不起作用，因为您不想拥有 table2 中的所有元素，而只想拥有表 1 中存在相应元素的元素

。您可能需要执行以下操作：

SELECT tbl1.province, tbl1.district, tbl1.commune, tbl1.village 
FROM dbo.table2 AS tbl2 
INNER JOIN dbo.table1 AS tbl1
ON tbl1.province = tbl2.province_id 
AND tbl1.district = tbl2.district_id 
AND (tbl1.commune is NULL OR (tbl1.commune = tbl2.commune_id)) 
AND (tbl1.village is NULL OR (tbl1.village = tbl2.village_id))

An OUTER JOIN won't work here, because you don't want to have all elements from table2, but only those where a corresponding element exists in table 1.

You would want to do something like this:

SELECT tbl1.province, tbl1.district, tbl1.commune, tbl1.village 
FROM dbo.table2 AS tbl2 
INNER JOIN dbo.table1 AS tbl1
ON tbl1.province = tbl2.province_id 
AND tbl1.district = tbl2.district_id 
AND (tbl1.commune is NULL OR (tbl1.commune = tbl2.commune_id)) 
AND (tbl1.village is NULL OR (tbl1.village = tbl2.village_id))

回复收藏 0 原文

~没有更多了~