＋设为首页 ＋添加到收藏夹
现在想把 mainlog表的一些数据合并，全并前执行了
update mainlog set lngIs=0
update mainlog set lngIs=1
where mainlog.lngID in
(
select lngID from mainlog
where strTypeName in (select top 100 strTypeName from add_type order by totalcount desc)
and strRealhost in (select top 100 strTypeName from add_site order by totalcount desc)
)
现在想把lngIs=0的记录中lngEmployeeID和strBeginDate均相同时合并为一条记录，将该条记录的strfilename保留为原合并前的strFileName的累加，lngSize也是原lngSize的累加，lngEmployeeID和strBeginDate不变
然后将这些合并后的记录插入到新表NewManiLog中，该 如何做？
MainLog表结构如下：
CREATE TABLE [MainLog] (
[lngID] [bigint] IDENTITY (1, 1) NOT NULL ,
[strAreaIP] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[strUserDomain] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[strProtocal] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[dtBeginDate] [datetime] NULL ,
[dtUsedTime] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[lngDownSum] [int] NULL ,
[strFileSize] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[lngTypeID] [int] NULL CONSTRAINT [DF_MainLog_lngTypeID] DEFAULT (4),
[strTypeName] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[strMachine] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[strFileName] [varchar] (300) COLLATE Chinese_PRC_CI_AS NULL ,
[strRealhost] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[strRealIP] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[strRealPort] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[strEmployeeName] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[lngEmployeeID] [int] NULL ,
[strDateTime] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[lngSize] [int] NULL CONSTRAINT [DF_MainLog_lngSize] DEFAULT (0),
[lngSiteID] [int] NULL CONSTRAINT [DF_MainLog_lngSiteID] DEFAULT (7),
[lngShopID] [int] NULL ,
[strShopName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[lngTempID] [int] NULL CONSTRAINT [DF_MainLog_strTempID] DEFAULT (1),
[lngIsWork] [tinyint] NULL CONSTRAINT [DF_MainLog_lngIsWork] DEFAULT (0),
[lngIsHoliday] [tinyint] NULL ,
[strTmpe] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[strRUL] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[DateID] [int] NULL ,
[lngIs] [int] NULL CONSTRAINT [DF_MainLog_lngIs] DEFAULT (0),
CONSTRAINT [PK_MainLog] PRIMARY KEY NONCLUSTERED
(
[lngID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
NewMainLog表结构如下：
CREATE TABLE [NewMainLog] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[lngID] [bigint] NULL ,
[strAreaIP] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[strProtocal] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[dtBeginDate] [datetime] NULL ,
[dtUsedTime] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[dateID] [int] NULL ,
[lngTypeID] [int] NULL CONSTRAINT [DF_NewMainLog_lngTypeID] DEFAULT (4),
[strFileName] [varchar] (300) COLLATE Chinese_PRC_CI_AS NULL ,
[strRealhost] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[strRealIP] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[lngEmployeeID] [int] NULL ,
[strDateTime] [datetime] NULL ,
[lngSize] [int] NULL CONSTRAINT [DF_NewMainLog_lngSize] DEFAULT (0),
[lngSiteID] [int] NULL CONSTRAINT [DF_NewMainLog_lngSiteID] DEFAULT (7),
[lngShopID] [int] NULL ,
[strIsWork] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_NewMainLog_lngIsWork] DEFAULT (0),
[strTypeName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[strDomainName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[AllFileName] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[lngIs] [int] NULL CONSTRAINT [DF_NewMainLog_lngIs] DEFAULT (0),
CONSTRAINT [PK_NewMainLog] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
...up
利用函数合并字符串。
create function dbo.fns_getSumStr(@lngEmployeeID int,@strBeginDate datetime)
returns varchar(5000)
as
begin
declare @re_str varchar(5000)
select @re_str=
select @re_str=@re_str+,+strFileName--可更换分隔符为自己需要的。
from MainLog
where lngEmployeeID=@lngEmployeeID and strBeginDate=@strBeginDate
return stuff(@re_str,1,1,)
end
Insert into NewManiLog(...,strFileName,...)
select ...,dbo.fns_getSumStr(lngEmployeeID,strBeginDate) as strFileName,sum(lngSize),
min(strProtocal),min(........
from MainLog
group by lngEmployeeID,strBeginDate
