SQL:如何更新多个字段，以便将空字段内容移动到

SQL: How to update multiple fields so empty field content is moved to the logically last columns - lose blank address lines(SQL:如何更新多个字段，以便将空字段内容移动到逻辑上最后一列 - 丢失空白地址行)

问题描述

我有三个地址行列，aline1、aline2、aline3 表示一条街道地址.从不一致的数据上演，它们中的任何一个或全部都可以空白的.我想将第一个非空白移动到 addrline1，第二个非空白到 addrline2，如果没有三个非空​​行，则清除第 3 行，否则离开它.(第一个"意味着 aline1 是第一个，除非它是空白的，如果 aline1 为空，则 aline2 是第一个，如果 aline1 和 2，则 aline3 是第一个都是空白)

I have three address line columns, aline1, aline2, aline3 for a street address. As staged from inconsistent data, any or all of them can be blank. I want to move the first non-blank to addrline1, 2nd non-blank to addrline2, and clear line 3 if there aren't three non blank lines, else leave it. ("First" means aline1 is first unless it's blank, aline2 is first if aline1 is blank, aline3 is first if aline1 and 2 are both blank)

这个临时表中的行没有键，可能有重复的行.我可以添加一个密钥.

The rows in this staging table do not have a key and there could be duplicate rows. I could add a key.

不计算列举可能的大case语句空白和非空白的组合并移动字段，如何我可以更新表格吗?(同样的问题出现了更多超过 3 行，所以这就是为什么我不想使用 case 语句)

Not counting a big case statement that enumerates the possible combination of blank and non blank and moves the fields around, how can I update the table? (This same problem comes up with a lot more than 3 lines, so that's why I don't want to use a case statement)

我使用的是 Microsoft SQL Server 2008

I'm using Microsoft SQL Server 2008

推荐答案

您可以创建一个插入和更新触发器，检查字段是否为空，然后移动它们.

You could make an insert and update trigger that check if the fields are empty and then move them.

这篇关于SQL:如何更新多个字段，以便将空字段内容移动到逻辑上最后一列 - 丢失空白地址行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！