主要是逻辑比较麻烦,想找一个效率高的方法,举个例子:用户A,B 登陆系统第一天2009-04-18只有用户A登陆 那么info表的记录有 A 2009-04-18 此时distinct usercount 为 1,insert active表 1 2009-04-18 第二天2009-04-19只有用户A登陆 那么info表的记录有 A 2009-04-18 A 2009-04-19此时distinct usercount 也是 1,就不做insert第三天2009-04-20有用户B登陆 那么info表的记录有 A 2009-04-18 A 2009-04-19 B 2009-04-20 此时distinct usercount 是 2,因为usercount变化了,那么就insert一条记录到active表 2 2009-04-20 第四天2009-04-21有用户B登陆 那么info表的记录有 A 2009-04-18 A 2009-04-19 B 2009-04-20 此时distinct usercount 是 2,此时的usercount没有变化,就不做insert记录到active表要求中间判断最好不要用到active表,只使用info表
select usercount from info a left join( select usercount from info where datepart('y',dt)=datepart('y',getdate())-1 )b where a.usercount=null
select b.usercount from info a left join( select usercount from info where datepart('y',dt)=datepart('y',getdate())-1 )b where a.usercount=b.usercount and a.usercount=null;
select distinct username,count(*) from info where activetime BETWEEN @YESTODAY AND @TODAY group by convert(char(10),activetime)
--那就用TRIGGER实现吧.CREATE TRIGGER trg_Ins ON info INSTEAD OF INSERT AS IF NOT EXISTS( SELECT * FROM info a,inserted b WHERE a.userid=b.userid ) BEGIN -- INSERT INTO info SELECT * FROM inserted
INSERT INTO active SELECT TOP 1 (SELECT COUNT(DISTINCT userid) FROM info), activetime FROM inserted END
在info表上建立一个trigger才是正解。
if object_id('[info]') is not null drop table [info] go create table [info]([Userid] varchar(10),[activetime] datetime) go if object_id('[active]') is not null drop table [active] go create table [active]([Userid] varchar(10),[activetime] datetime) go if object_id('[tg_info]') is not null drop trigger [tg_info] go create trigger tg_info on info for insert as insert [active] select * from inserted t where not exists (select 1 from info where [activetime]<t.[activetime] and [Userid]=t.[Userid] and convert(varchar,[activetime],23)=convert(varchar,t.[activetime],23)) goinsert [info] select '10001','2009-4-18 7:26:00' insert [info] select '10001','2009-4-18 8:26:00' insert [info] select '10001','2009-4-19 9:26:00' insert [info] select '10001','2009-4-19 11:26:00'select * from info /* Userid activetime ---------- ----------------------- 10001 2009-04-18 07:26:00.000 10001 2009-04-18 08:26:00.000 10001 2009-04-19 09:26:00.000 10001 2009-04-19 11:26:00.000 */ select * from [active] /* Userid activetime ---------- ----------------------- 10001 2009-04-18 07:26:00.000 10001 2009-04-19 09:26:00.000(2 行受影响) */ insert [info] select '10002','2009-4-21 7:26:00' insert [info] select '10002','2009-4-21 9:26:00'select * from info /* Userid activetime ---------- ----------------------- 10001 2009-04-18 07:26:00.000 10001 2009-04-18 08:26:00.000 10001 2009-04-19 09:26:00.000 10001 2009-04-19 11:26:00.000 10002 2009-04-21 07:26:00.000 10002 2009-04-21 09:26:00.000(6 行受影响) */ select * from [active] /* Userid activetime ---------- ----------------------- 10001 2009-04-18 07:26:00.000 10001 2009-04-19 09:26:00.000 10002 2009-04-21 07:26:00.000(3 行受影响) */
登陆系统第一天2009-04-18只有用户A登陆
那么info表的记录有
A 2009-04-18
此时distinct usercount 为 1,insert active表
1 2009-04-18
第二天2009-04-19只有用户A登陆
那么info表的记录有
A 2009-04-18
A 2009-04-19此时distinct usercount 也是 1,就不做insert第三天2009-04-20有用户B登陆
那么info表的记录有
A 2009-04-18
A 2009-04-19
B 2009-04-20
此时distinct usercount 是 2,因为usercount变化了,那么就insert一条记录到active表
2 2009-04-20
第四天2009-04-21有用户B登陆
那么info表的记录有
A 2009-04-18
A 2009-04-19
B 2009-04-20
此时distinct usercount 是 2,此时的usercount没有变化,就不做insert记录到active表要求中间判断最好不要用到active表,只使用info表
left join(
select usercount from info
where datepart('y',dt)=datepart('y',getdate())-1
)b
where a.usercount=null
left join(
select usercount from info
where datepart('y',dt)=datepart('y',getdate())-1
)b
where a.usercount=b.usercount and a.usercount=null;
from info
where activetime BETWEEN @YESTODAY AND @TODAY
group by convert(char(10),activetime)
INSTEAD OF INSERT
AS
IF NOT EXISTS(
SELECT * FROM info a,inserted b
WHERE a.userid=b.userid
)
BEGIN
--
INSERT INTO info
SELECT * FROM inserted
INSERT INTO active
SELECT TOP 1
(SELECT COUNT(DISTINCT userid) FROM info),
activetime
FROM inserted
END
go
create table [info]([Userid] varchar(10),[activetime] datetime)
go
if object_id('[active]') is not null drop table [active]
go
create table [active]([Userid] varchar(10),[activetime] datetime)
go
if object_id('[tg_info]') is not null drop trigger [tg_info]
go
create trigger tg_info on info
for insert
as
insert [active]
select *
from inserted t
where not exists
(select 1
from info
where [activetime]<t.[activetime]
and [Userid]=t.[Userid]
and convert(varchar,[activetime],23)=convert(varchar,t.[activetime],23))
goinsert [info] select '10001','2009-4-18 7:26:00'
insert [info] select '10001','2009-4-18 8:26:00'
insert [info] select '10001','2009-4-19 9:26:00'
insert [info] select '10001','2009-4-19 11:26:00'select * from info
/*
Userid activetime
---------- -----------------------
10001 2009-04-18 07:26:00.000
10001 2009-04-18 08:26:00.000
10001 2009-04-19 09:26:00.000
10001 2009-04-19 11:26:00.000
*/
select * from [active]
/*
Userid activetime
---------- -----------------------
10001 2009-04-18 07:26:00.000
10001 2009-04-19 09:26:00.000(2 行受影响)
*/
insert [info] select '10002','2009-4-21 7:26:00'
insert [info] select '10002','2009-4-21 9:26:00'select * from info
/*
Userid activetime
---------- -----------------------
10001 2009-04-18 07:26:00.000
10001 2009-04-18 08:26:00.000
10001 2009-04-19 09:26:00.000
10001 2009-04-19 11:26:00.000
10002 2009-04-21 07:26:00.000
10002 2009-04-21 09:26:00.000(6 行受影响)
*/
select * from [active]
/*
Userid activetime
---------- -----------------------
10001 2009-04-18 07:26:00.000
10001 2009-04-19 09:26:00.000
10002 2009-04-21 07:26:00.000(3 行受影响)
*/