更新列为不同的聚合值

问题描述：

我正在创建一个脚本，用于“合并”并从表中删除重复行。该表包含地址信息，并使用整数字段将有关电子邮件的信息存储为位标志（列名称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
lngValue
strAddress

实施例第1行：
lngInternetPK：1
lngContactFK：1
lngValue：33
strAddress： “[email protected]”

例行2：
lngInternetPK：2
lngContactFK ：1
lngValue：40
strAddress：“[email protected]”

如果这两个在这里合并是所期望的结果：
lngInternetPK：1
lngContactFK：1
lngValue：41
strAddress： “[email protected]”

其他必要的规则：
每个接触可以有多个电子邮件，但每个电子邮件行必须是不同的（每个电子邮件只能显示为一行）。

你可以分享一些样本数据的任何机会呢？看看数据现在的样子以及期望的结果可能有助于产生一些答案。 – 2009-07-11 21:05:07

Griswold：已添加数据 – 2009-07-13 16:11:10

感谢您发布数据。它有帮助。 – 2009-07-13 16:49:40

答

SQL Server缺乏原生按位聚集，这就是为什么我们要模仿他们。

这里的主要思想是生成一组位从0到15，对每一位申请位掩码的价值和选择MAX（这将给我们对于给定的有点OR），然后选择SUM（这将合并位掩码）。

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

;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

答

我相信下面的查询得到你想要的。该例程假定每个联系人最多有两个重复地址。如果每个联系人有多个副本，则该查询将不得不进行修改。我希望这有帮助。

Declare @tblminternet 
Table 
(lngInternetPK int, 
    lngContactFK int, 
    lngValue int, 
    strAddress varchar(255) 
) 

Insert Into @tblminternet 
select 1, 1, 33, '[email protected]' 
union 
select 2, 1, 40, '[email protected]' 
union 
select 3, 2, 33, '[email protected]' 
union 
select 4, 2, 40, '[email protected]' 
union 
select 5, 3, 2, '[email protected]' 

--Select * from @tblminternet 

Select Distinct 
    A.lngContactFK , 
    A.lngValue | B.lngValue as 'Bitwise OR', 
    A.strAddress 
From @tblminternet A, @tblminternet B 
Where A.lngContactFK = B.lngContactFK 
And A.strAddress = B.strAddress 
And A.lngInternetPK != B.lngInternetPK

答

您可以在.NET中创建的SQL Server聚合函数，然后可以在SQL服务器在线执行。我认为这需要最少的SQL Server 2005和Visual Studio 2010.我使用Visual Studio 2013 Community Edition（即使用于商业用途也是免费的）用于.NET 2和SQL Server 2005.

请参阅MSDN文章：https://msdn.microsoft.com/en-us/library/91e6taax(v=vs.90).aspx

首先，您需要启用SQL Server中的CLR功能：https://msdn.microsoft.com/en-us/library/ms131048.aspx

sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE; 
GO 
sp_configure 'clr enabled', 1; 
GO 
RECONFIGURE; 
GO

创建SQL Server - > SQL Server数据库项目
右键CLI CK的新项目，并选择属性
配置项目设置
配置下SQL CLR目标CLR语言（如Java）的新项目
右键单击，然后选择添加下，有针对性的SQL Server版本 - >新项...
当弹出对话框，选择SQL Server - > SQL CLR VB - > SQL CLR VB总结

现在你可以在VB编写的代码按位：

Imports System 
Imports System.Data 
Imports System.Data.SqlClient 
Imports System.Data.SqlTypes 
Imports Microsoft.SqlServer.Server 


<Serializable()> _ 
<Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)> _ 
Public Structure AggregateBitwiseOR 

    Private CurrentAggregate As SqlTypes.SqlInt32 

    Public Sub Init() 
     CurrentAggregate = 0 
    End Sub 

    Public Sub Accumulate(ByVal value As SqlTypes.SqlInt32) 
     'Perform Bitwise OR against aggregate memory 
     CurrentAggregate = CurrentAggregate OR value 
    End Sub 

    Public Sub Merge(ByVal value as AggregateBitwiseOR) 
     Accumulate(value.Terminate()) 
    End Sub 

    Public Function Terminate() As SqlInt32 
     Return CurrentAggregate 
    End Function 

End Structure

现在部署：https://msdn.microsoft.com/en-us/library/dahcx0ww(v=vs.90).aspx

构建使用菜单栏项目：建设 - >建设项目名（如果构建失败，错误04018然后下载的数据工具的新版本@http://msdn.microsoft.com/en-US/data/hh297027或将菜单栏：工具 - >扩展和更新，然后在更新下选择更新为Microsoft SQL Server更新为数据库工具）
将编译的DLL复制到C：\ Program Files \ Microsoft SQL Server \ MSSQL.1 \ MSSQL \ Binn和C：\
寄存器DLL：

CREATE ASSEMBLY [CLRTools] FROM'c：CLRTools。DLL” WITH PERMISSION_SET = SAFE
创建SQL骨料：。

CREATE AGGREGATE [DBO] [AggregateBitwiseOR]（@值INT）返回int EXTERNAL NAME [CLRTools] [CLRTools.AggregateBitwiseOR] ;

如果你得到错误 “附近有语法错误 '外部'”，然后用下面的命令更改数据库兼容级别：

对于SQL Server 2005：EXEC sp_dbcmptlevel的 '数据库名称'，90

对于SQL Server 2008：EXEC sp_dbcmptlevel的 '数据库名称'，100

测试代码：

SELECT dbo.AggregateBitwiseOR（富）为Foo从酒吧

我发现这篇文章有帮助：http://www.codeproject.com/Articles/37377/SQL-Server-CLR-Functions

开始=>

更新列为不同的聚合值

相关推荐