22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].Login_Detail(
[auto_no] [bigint] IDENTITY(1,1) NOT NULL,
[userid] [varchar](50) NOT NULL,
[logdate] [char](8) NOT NULL
) ON [ps_logdate]([logdate])
CREATE CLUSTERED INDEX [index_login_logdate] ON [dbo].Login_Detail
(
[logdate] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)
ON [ps_logdate]([logdate])
CREATE NONCLUSTERED INDEX [index_login_userid] ON [dbo].login
(
[userid] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)
ON [ps_logdate]([logdate])
CREATE TABLE [dbo].login_item(
[userid] [varchar](50) NOT NULL,
[mindate] [char](8) NULL,
[maxdate] [char](8) NULL,
[lastdate] [char](8) NULL
) ON [psc_userid]([userid])
UPDATE dbo.login_item
SET [lastdate] = (
SELECT TOP 1 log_datetime FROM
dbo.Login_Detail a WITH(NOLOCK)
WHERE log_datetime BETWEEN login_item.mindate
AND CONVERT(CHAR(8),DATEADD(d,-1,login_item.[maxdate]),112)
AND a.[USER_ID] = login_item.[USER_ID]
ORDER BY log_datetime DESC
)