tsql - 将列更新为不同的聚合值

Question

我正在创建一个用于“合并”和从表中删除重复行的脚本。该表包含地址信息，并使用整数字段将有关电子邮件的信息存储为位标志(列名 lngValue)。例如，lngValue & 1 == 1 表示它的主地址。

有两次输入同一电子邮件的实例，但有时会使用不同的 lngValues。为了解决这个问题，我需要从所有重复项中取出 lngValue 并将它们分配给一个幸存的记录并删除其余记录。

到目前为止，我最头疼的是记录的“合并”。我想要做的是按位或将重复记录的所有 lngValues 放在一起。到目前为止，这是我所拥有的，它只找到所有 lngValues 按位或在一起的值。

警告:前方有乱码

declare @duplicates table
(
lngInternetPK int,
lngContactFK int,
lngValue int
)

insert into @duplicates (lngInternetPK, lngContactFK, lngValue) 
(
select  tblminternet.lngInternetPK, tblminternet.lngContactFK, tblminternet.lngValue   from tblminternet  inner join 
(select strAddress, lngcontactfk, count(*) as count from tblminternet where lngValue & 256 <> 256 group by strAddress, lngcontactfk) secondemail
On tblminternet.strAddress = secondemail.strAddress and
tblminternet.lngcontactfk = secondemail.lngcontactfk 
where count > 1 and tblminternet.strAddress is not null and tblminternet.lngValue & 256 <> 256 --order by lngContactFK, strAddress
)

update @duplicates set lngValue = t.val

from 
                (select (sum(dupes.lngValue) & 65535) as val from 
                    (select  here.lngInternetPK,                     here.lngContactFK, here.lngValue from tblminternet here  inner join 
                    (select strAddress, lngcontactfk, count(*) as count from tblminternet where lngValue & 256 <> 256 group by strAddress, lngcontactfk) secondemail
                    On here.strAddress = secondemail.strAddress     and
                    here.lngcontactfk = secondemail.lngcontactfk 
                    where count > 1 and here.strAddress is not      null and here.lngValue & 256 <> 256) dupes, tblminternet this

                where this.lngContactFK = dupes.lngContactFK
                ) t
where lngInternetPK in (select lngInternetPK from @duplicates)    

编辑:
根据要求，这里是一些示例数据:

表名:tblminternet
列名称:
lngInternetPK
lngContactFK
lng值
字符串地址

示例行 1:
lngInternetPK: 1
lngContactFK: 1
lng值:33
strAddress: "me@myaddress.com"

示例行 2:
lngInternetPK: 2
lngContactFK: 1
lng值:40
strAddress: "me@myaddress.com"

如果将这两个合并到这里是期望的结果:
lngInternetPK: 1
lngContactFK: 1
lng值:41
strAddress: "me@myaddress.com"

其他必要规则:
每个联系人可以有多个电子邮件，但每个电子邮件行必须不同(每个电子邮件只能显示为一行)。

Answer 1

SQL Server 缺少 native 按位聚合，这就是我们需要模拟它们的原因。

这里的主要思想是生成一组从 0 到 15 的位，为每个位应用位掩码到值并选择 MAX(这将为我们提供给定位的 OR)，然后选择 SUM(这将合并位掩码)。

我们只是用 lngValue 的新值更新任何给定 (lngContactFK, strValue) 的第一个 lngInternetPK，并删除所有重复项.

;WITH   bits AS
        (
        SELECT  0 AS b
        UNION ALL
        SELECT  b + 1
        FROM    bits
        WHERE   b < 15
        ),
        v AS
        (
        SELECT  i.*,
                (
                SELECT  SUM(value)
                FROM    (
                        SELECT  MAX(lngValue & POWER(2, b)) AS value
                        FROM    tblmInternet ii
                        CROSS JOIN
                                bits
                        WHERE   ii.lngContactFK = i.lngContactFK
                                AND ii.strAddress = i.strAddress
                        GROUP BY
                                b
                        ) q
                ) AS lngNewValue
        FROM    (
                SELECT  ii.*, ROW_NUMBER() OVER (PARTITION BY lngContactFK, strAddress ORDER BY lngInternetPK) AS rn
                FROM    tblmInternet ii
                ) i
        WHERE   rn = 1
        )
UPDATE  v
SET     lngValue = lngNewValue;

;WITH    v AS
        (
        SELECT  ii.*, ROW_NUMBER() OVER (PARTITION BY lngContactFK, strAddress ORDER BY lngInternetPK) AS rn
        FROM    tblmInternet ii
        )
DELETE  v
WHERE   rn > 1

请参阅我博客中的这篇文章以获得更详细的解释:

• SQL Server: aggregate bitwise OR