如何处理 SQL 中的长比较
我对 SQL 有一个病态的问题,所以我通常通过构建快速软件应用程序来处理我的 SQL 问题来对所有数据库问题进行排序。
(正如我在本例中所做的那样)
感谢 StackOverflow,我认为我可能会因正确性而感到羞愧,所以我想学习如何在实际的 SQL 或 T-SQL 中进行这种 SQL 故障排除本身(如果可能):
我有:
数据库 DB1 表 A (unitNo, BuildingNo)
数据库 DB2 表 B (unitNo, BuildingNo)
我想提出数据库 DB1 的表 A 上存在的单位(单位编号)不存在于数据库 DB2 的表 B 上,反之亦然。
可以有多个单元具有相同的单元编号,这种情况会发生,因为不同建筑物的单元可以分配相同的单元编号。
我没有对任何数据库的写访问权限。
我希望这不是' t 被视为“gimme teh codz”帖子,我想知道比我更熟练 SQL 的人如何排序这种算法,教程或提示的帖子非常受欢迎,不需要完整的代码,但如果它有帮助有道理,那么请这样做。
起初我以为我可以从一个表中获取所有单位编号,然后在另一个表中的选择中排除它们,如下所示:
select concated.unit from
( SELECT ( unitNo + ',' + CONVERT(varchar(12), BuildingNo) ) as unit
FROM A) concated
having concated.unit not in
(
'201,1',
'202,1',
'203,1',
'204,1',
'205,1',
'206,1',
[...]
这通常可以工作,但任何一个表中的单位数量都是巨大的,尝试这样做会导致 SQL Server 崩溃和:
“堆栈空间不足”
谢谢,
里克
I have a pathological issue with SQL, so I usually sort all of my database issues by building quickie software applications to handle my SQL problems.
(as I am also doing in this case)
Thanks to StackOverflow I think I can be shamed into correctness, so I would like to learn how to make this kind of SQL troubleshooting in actual SQL or T-SQL itself (if possible):
I have:
database DB1 Table A (unitNo, BuildingNo)
database DB2 Table B (unitNo, BuildingNo)
I want to come up with the Units (unit numbers) existing on Table A from Database DB1 which do not exist on table B from Database DB2 and vice versa.
There can be more than one unit with the same unit number, this will happen because the same unit number can be given to units of different buildings.
I do not have write access to any of the databases.
I hope this isn't seen as a "gimme teh codz" post, I would like to know how people with more SQL fluency than me sort this kind of algorithm, posts to tutorials or hints are more than welcomed, no full code required, but if it helps to makes sense, then please do.
At first I thought I could just get all the unit numbers from one table and exclude them on a select from the other like so:
select concated.unit from
( SELECT ( unitNo + ',' + CONVERT(varchar(12), BuildingNo) ) as unit
FROM A) concated
having concated.unit not in
(
'201,1',
'202,1',
'203,1',
'204,1',
'205,1',
'206,1',
[...]
This would usually work, but the number of units from any one table is immense, trying this crashes the SQL server with:
"ran out of stack space"
Thanks,
Ric
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我认为您正在寻找完整的外部连接。 这将为您提供在第 1 部分中要求的单元编号。
我在下面概述了您可能感兴趣的其他查询。
这会为您提供 A 中而不是 B 中的记录。
您只需反转它即可找到 B 中但 A 中没有的记录。
I think you're looking for full outer join. Which gives you the unit numbers you asked for in Part 1.
Other queries that might be of interest I've outlined below.
This gives you the records in A not in B.
And you just reverse it to find the records in B that aren't in A.
正如 JPunyon 所说,但如果您希望将它们全部放在一个列表中,那么如下所示:
As JPunyon said, but if you want them all in one list then something like: