lyfz_repos/铂金版源码/DBFile/新版聚合函数.sql

--要使用.Net写的自定义聚合函数，需以上代码开启clr功能
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FunTrim]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FunTrim]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AggregateString]') AND type = N'AF')
DROP AGGREGATE [dbo].[AggregateString]
GO
IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'LYFZCLR')
DROP ASSEMBLY [LYFZCLR]
GO
CREATE ASSEMBLY [LYFZCLR]
    AUTHORIZATION [dbo]
    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030028EC33580000000000000000E00002210B010B000016000000060000000000000E340000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000C03300004B000000004000007003000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000014140000002000000016000000020000000000000000000000000000200000602E7273726300000070030000004000000004000000180000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001C00000000000000000000000000004000004200000000000000000000000000000000F0330000000000004800000002000500742700004C0C000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004A02036F1000000A731100000A7D010000042A000B300200220000000000000003027B010000046F1200000A6F1300000ADE0E260372010000706F1300000ADE002A000001100000000000001313000E030000015E02731400000A7D0100000402731500000A7D020000042A1B30050066030000010000110F01281600000A2D1E0F01FE16080000016F1200000A6F1700000A72130000706F1800000A2C05DD39030000170A0F01FE16080000016F1200000A0B07721D0000706F1900000A39CF02000007178D1A000001130B110B16721D000070A2110B166F1A00000A0C08179A178D1A000001130C110C16722D000070A2110C166F1A00000A0D08169A6F1B00000A0B098E691A408502000009199A25130D397A020000110D7237000070281C00000A2D38110D723B000070281C00000A3AC5000000110D723F000070281C00000A3A52010000110D7243000070281C00000A3AA3010000383402000009169A6F1B00000A6F1D00000A16314C09169A6F1B00000A6F1200000A6F1700000A72130000706F1800000A2D2E09189A6F1B00000A6F1D00000A16311E09189A6F1B00000A6F1200000A6F1700000A72130000706F1800000A2C07160A38D101000009169A6F1B00000A281E00000A130409189A6F1B00000A281E00000A13050211048C1D00000111058C1D00000109179A28060000060A389601000009169A6F1B00000A6F1D00000A16314C09169A6F1B00000A6F1200000A6F1700000A72130000706F1800000A2D2E09189A6F1B00000A6F1D00000A16311E09189A6F1B00000A6F1200000A6F1700000A72130000706F1800000A2C07160A383301000009169A6F1B00000A281F00000A130609189A6F1B00000A281F00000A13070211068C1E00000111078C1E00000109179A28060000060A38F800000009169A6F1B00000A6F1200000A6F1700000A72130000706F1800000A2D1E09189A6F1B00000A6F1200000A6F1700000A72130000706F1800000A2C07160A38B50000000209169A6F1B00000A09189A6F1B00000A09179A28060000060A389600000009169A6F1B00000A6F1D00000A16314C09169A6F1B00000A6F1200000A6F1700000A72130000706F1800000A2D2E09189A6F1B00000A6F1D00000A16311E09189A6F1B00000A6F1200000A6F1700000A72130000706F1800000A2C04160A2B3609169A6F1B00000A282000000A130809189A6F1B00000A282000000A13090211088C1F00000111098C1F00000109179A28060000060A02072805000006130A062C39027B010000046F2100000A163012027B01000004110A169A6F2200000A262B19027B01000004110A179A110A169A282300000A6F2200000A26DE0326DE002A0000411C0000000000000000000062030000620300000300000003000001133004005700000002000011188D1A0000010A03722D0000706F1900000A2C3003178D1A0000010B0716722D000070A207166F1A00000A0A061606169A6F1B00000AA2061706179A6F1B00000AA22B110616036F1B00000AA206177247000070A2062A001B3004004201000003000011160A05250B3931010000FE137E040000042D611D732500000A25724900007016282600000A25724D00007017282600000A25725300007018282600000A25725700007019282600000A25725B0000701A282600000A2572610000701B282600000A2572670000701C282600000AFE138004000004FE137E04000004071202282700000A39B3000000084507000000050000001500000022000000360000004A0000005E00000072000000388C00000003046F2800000A3980000000170A2B7C03046F2800000A2D73170A2B6F027B0200000403046F2900000A17335F170A2B5B027B0200000403046F2900000A15334B170A2B47027B0200000403046F2900000A163237170A2B33027B0200000403046F2900000A163023170A2B1F036F1200000A6F1700000A046F1200000A6F1700000A6F1900000A2C02170ADE0326DE00062A0000411C000000000000020000003B0100003D010000030000000300000152027B010000040F017B010000046F2A00000A262A0000001B3002002300000004000011027B010000046F1200000A732B00000A0ADE0E267271000070732B00000A0ADE00062A0001100000000000001313000E030000011B300500D9000000050000110F00281600000A2D1E0F00FE16080000016F1200000A6F1700000A72130000706F1800000A2C107247000070732B00000A0CDDA0000000140A0F00281600000A2D3C0F00FE16080000016F1200000A6F1700000A72130000706F1800000A2D1E0F01FE16080000016F1200000A0B076F1D00000A163107076F2D00000A0A062C2E068E691631280F00FE16080000016F1200000A178D230000010D09160616939D096F2E00000A732B00000A0CDE280F00FE16080000016F1200000A6F1B00000A732B00000A0CDE0E267281000070732B00000A0CDE00082A0000000110000000000000C9C9000E030000011E02282F00000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000F8030000237E0000640400006005000023537472696E677300000000C40900008C00000023555300500A0000100000002347554944000000600A0000EC01000023426C6F620000000000000002000001571702080900000000FA253300160000010000002300000004000000040000000A0000000A000000010000002F0000000D0000000500000001000000010000000200000000000A0001000000000006004B0044000A007C00610006008D0044000600A00094000600CB00C1000600DD00C10006000301F0000A0043012E010600E201D0010600F901D00106001602D00106003502D00106004E02D00106006702D00106008202D00106009D02D0010600D502B6020600E902D0010600220302030600420302030600680344000A007E0361000A009F0361000600A603B6020600BC03B6020600E603440006000504440006003A04440006004A0444000600500444000600620444000600C90402030600FF04E4040A00380561000600590544000000000001000000000001000100092110001600000005000100010001001000260000000D000400090000000000840400000D0004000B000100AE000A0001001B011A0006008401400013000C052C01502000000000E601D8000E000100642000000000E601EA0014000200A42000000000860029011E000300BC200000000086004D01220003004C24000000008100580128000400B02400000000810066012E0005001C2600000000860074013500080034260000000086007A013B00090074260000000096008A01430009006C2700000000861892011E000B00000001009801000001009A01000001009C0100000100A70100000100B30100000200B50100000300B70100000100C101000001009C0100000200C70102000900490092014C00510092014C00590092014C00610092014C00690092014C00710092014C00790092014C00810092014C00890092015100910092014C00990092015600A10092011E00A90092011E00B10092015B00C1009201C3002900C703C900210092014C001900D203C9003100EA004C00210092011E00390092011E004100DB03CD00D100ED03C900D100F503D100D100FC03D100D1001804D600D1001E04C900D1002304DF00D1002F04E500E1004204E900E1005804EE00E1006B04F40021002F04E50021007604FA00D1007D040001010192011E000C00920156000C0020053D010C00240545011900F5034E01390030055301210076045F01410092014C00110192011E00D1004D056A01D1001E046F01190092011E002E00230092012E006300CB012E000B007F012E0013008C012E001B008C012E002B007F012E003300A1012E003B008C012E004B008C012E005B00C20143007300610083002301270120016301270106012001590165017501350104800000010000001A1814690000000000006003000002000000000000000000000001003B000000000002000000000000000000000001005500000000000000003C4D6F64756C653E004C59465A434C522E646C6C00416767726567617465537472696E670055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D0056616C7565547970650053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E536572766572004942696E61727953657269616C697A65004F626A6563740053797374656D2E5465787400537472696E674275696C64657200696E7465726D656469617465526573756C740053797374656D2E494F0042696E61727952656164657200526561640042696E6172795772697465720057726974650053797374656D2E436F6C6C656374696F6E730043617365496E73656E736974697665436F6D7061726572004F626A656374436F6D7061726500496E69740053797374656D2E446174612E53716C54797065730053716C537472696E6700416363756D756C617465004765744669656C6456616C756500436F6D70617265526573756C74004D65726765005465726D696E617465005F766172310046756E5472696D002E63746F7200720077004669656C6456616C75650076616C7565537472696E6700610062006F70657261746F72730047726F7570007472696D436861720053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004C59465A434C520053657269616C697A61626C654174747269627574650053716C55736572446566696E656441676772656761746541747472696275746500466F726D6174005374727563744C61796F7574417474726962757465004C61796F75744B696E640052656164537472696E6700546F537472696E67006765745F49734E756C6C00537472696E6700546F4C6F77657200457175616C7300436F6E7461696E7300537472696E6753706C69744F7074696F6E730053706C6974005472696D006F705F457175616C697479006765745F4C656E67746800436F6E7665727400546F496E74333200496E74333200446563696D616C00546F446563696D616C004461746554696D6500546F4461746554696D6500417070656E6400436F6E636174003C50726976617465496D706C656D656E746174696F6E44657461696C733E7B42364542463430362D324342442D343937422D413043352D3744303635414132363346447D00436F6D70696C657247656E6572617465644174747269627574650053797374656D2E436F6C6C656374696F6E732E47656E657269630044696374696F6E61727960320024246D6574686F643078363030303030362D31004164640054727947657456616C756500436F6D706172650053716C46756E6374696F6E41747472696275746500546F43686172417272617900436861720000000011E065D56C045906745E97D56C575B267B01096E0075006C006C00000F7B00770068006500720065007D0000097B00240024007D0000036900000366000003730000037400000100033D0000053C003E0000033E0000033C0000053E003D0000053C003D0000096C0069006B006500000F938F6551864E5E97D56C575B267B01095E97D56C575B267B010006F4EBB6BD2C7B49A0C57D065AA263FD0008B77A5C561934E089030612110520010112150520010112190306121D032000010520010111210520011D0E0E062003021C1C0E0520010111080420001121020608080002112111211121042001010E0420010102042001010805200101115D6101000200000004005402124973496E76617269616E74546F4E756C6C73015402174973496E76617269616E74546F4475706C696361746573005402124973496E76617269616E74546F4F726465720054080B4D61784279746553697A65401F00000520010111650320000E03200002042001020E0820021D0E1D0E116D050002020E0E03200008040001080E05000111790E050001117D0E05200112110E0500020E0E0E19070E020E1D0E1D0E080811791179117D117D1D0E1D0E1D0E0E0607021D0E1D0E0401000000080615128085020E080715128085020E080720020113001301082002021300101301042001021C052002081C1C050703020E0805200112111C04070111210420001D030520010E1D030907041D030E11211D030C0100074C59465A434C5200000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313500000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000E83300000000000000000000FE330000002000000000000000000000000000000000000000000000F03300000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000180300000000000000000000180334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE000001000000010014691A180000010014691A183F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00478020000010053007400720069006E006700460069006C00650049006E0066006F00000054020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F00660074000000380008000100460069006C0065004400650073006300720069007000740069006F006E00000000004C00590046005A0043004C005200000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0036003100370030002E00320036003900300030000000000038000C00010049006E007400650072006E0061006C004E0061006D00650000004C00590046005A0043004C0052002E0064006C006C0000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F0066007400200032003000310035000000000040000C0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004C00590046005A0043004C0052002E0064006C006C000000300008000100500072006F0064007500630074004E0061006D006500000000004C00590046005A0043004C005200000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0036003100370030002E00320036003900300030000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0036003100370030002E003200360039003000300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000103400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
    WITH PERMISSION_SET = SAFE;
GO
CREATE AGGREGATE [dbo].[AggregateString] (@FieldValue [nvarchar](4000))
RETURNS [nvarchar](4000)
EXTERNAL NAME [LYFZCLR].[AggregateString];
GO
CREATE FUNCTION [dbo].[FunTrim] (@FieldValue [nvarchar](4000),@trimChar [nvarchar](4000))
RETURNS [nvarchar](4000) 
AS
EXTERNAL NAME [LYFZCLR].[UserDefinedFunctions].[FunTrim];
GO