可能需要高手哦：生成某一时刻的用户状况报表。

[microsealkey]

现有数据库主要属性设计如下:
create table account(id varchar(16),status char(1));

当前的用户状况报表可以这样获取:
select status,count(*) from account group by status;

其结果示例:
status count(*)
A(ctive) 200
D(eleted) 30
P(ending) 10

如果我想获取任意时刻（也就是说不是当前，例如2009年3月30日11点正)，则数据库应该怎样设计，通过怎样的方式才能获取到这个状况报表?

[microsealkey]

我觉得快照不能满足要求，因为需要生成任意时刻的报表，而快照只能定期，达不到"任意"的效果。

至于历史表，就是觉得性能影响可能很大，特别是变动频繁的情况下（系统会自动调整状态，所以会比较频繁），会产生大量的记录，这样使用历史表来复原效率就会很低。

[永生天地]

[Quote=引用 14 楼 xman_78tom 的回复:]
在 SQL Server 中，只能通过设计历史表实现。具体的，
在原表中创建一个触发器，将每次修改的行（修改前的值）都插入历史表，并且标记时间。查询特定时间的历史记录时，可以查询历史表中在此时间前所有的行，然后与原表中的行合并，消除原表中与历史表中重复的行（用 主键标示）。当然，这个只是主要思路，还细节问题。

oracle 数据库可以直接使用 flashback 查询实现。
[/Quote]
这样都不行，那就没什么好办法了

[fcuandy]

还是看你实际情况吧， 要任一时刻的状态
如果一个用户，在每分钟甚至每秒钟都可能变, 这样频繁的变化无论是数据库快照还是用trigger捕捉变化记录插入change_log表，都是吃不销的
如果是这种情况，那么一般情况下，在设计时不会考滤到任一时刻，可能是按天聚合存放一个快照。

如果变化不很频繁，那么使用trigger记录change log是个做法。

[microsealkey]

感觉上这似乎是一个简单的需求，却似乎找不到简单的办法

[xman_78tom]

-- 基本思路：用 [timestamp] 和 [deleted] 列跟踪老数据；用 update 语句执行删除操作；用 update、insert 语句执行更新操作
if OBJECT_ID('tab') is not null
 drop table tab;
go
create table tab 
(id int, c char(1),
[deleted] bit default 0, 
[timestamp] datetime default getdate()
);
go

-- insert rows
insert into tab (id,c) values(1,'a'),(2,'b'),(3,'c');
go

-- delete rows (delete from tab where id=3;)
update tab set deleted=1 where id=3;
go

-- update rows (update tab set c='x' where id=2)
begin tran
 update tab set deleted=1 where id=2;
 insert into tab (id,c,[timestamp]) 
  select id,'x',SYSDATETIME() from tab where id=2
commit tran
go

-- query (select * from tab;)
select id,c from tab where deleted=0;

-- query (select * from tab as of timestamp 'yyyy-mm-dd hh:mi:ss')
;with t as(
select * from tab t where [timestamp]<='yyyy-mm-dd hh:mi:ss' )
select id, c from t t1 where
not exists (select * from t where t1.id=id and [timestamp]>t1.[timestamp]);

-- clear old snapshot
delete from tab where deleted=1;

[永生天地]

加日期字段
alter table account add dc datetime default getdate()

select status,count(*) from account where dc=... group by status

[htl258_Tony]

select status,count(*) 
from account 
where time >='2009-03-30 11:00' and time<'2009-03-30 12:00' 
group by status;

或者

select status,count(*) 
from account 
where time ='2009-03-30 11:00' 
group by status;

总有一个是你要的结果吧

[microsealkey]

很明显，都不是。你们要仔细想想啊。
这不是想获取那一个时刻/时间段注册的用户。

而是那个时候的用户状况，自然包括以前注册的。

[microsealkey]

你们看我获得当前报表的时候，根本就不带时间的。

获取当前的，获取过去1秒的，获取过去1天的，这些结果都应该相似的。所以这个时间条件必须要仔细考虑。

[htl258_Tony]

[Quote=引用 4 楼 microsealkey 的回复:]
你们看我获得当前报表的时候，根本就不带时间的。

获取当前的，获取过去1秒的，获取过去1天的，这些结果都应该相似的。所以这个时间条件必须要仔细考虑。
[/Quote]
时间处理的问题是SQL最好解决的问题,贴上代码大家才不会误解.

[microsealkey]

这不是时间处理的问题。

而是这里的时间是很特殊的概念。你想想，你这个时间指的是注册时间吗？还是其他什么时间？
要注意状态(status)是可能会修改的。

你仔细考虑一下，这个问题不是那么简单的啦。我不是想查询某一个时间段注册的用户。

[东那个升]

set transaction isolation level snapshot
select * from tb_name

[东那个升]

快照隔离
set transaction isolation level snapshot
select status,count(*) from account group by status;

[jhonlonehou]

按楼主目前的数据库设计无法达到你的要求，若想查找任意时刻的用户状态，很显然，你需要一个同步的带时间序列的历史表

[microsealkey]

ldslove，确实有这方面的概念，但应该不行的。你将时间条件(2009年3月30日11点正)放哪儿呢？

[htl258_Tony]

　　如果要在一个特定的时间分析数据库中的数据，你会怎么做?例如，你想要分析晚上12点的数据，你会采取什么样的措施?最经常用到的方法，创建一个计划任务，在晚上12点的时候执行备份，将当前数据库以一个新的名字备份到服务器上，然后再开始分析这个备份数据库中的数据。这样做的问题就在于，如果这个数据库很大，那么备份它就需要花费大量的时间和磁盘空间。如果你需要在数据访问高峰期做备份的话，它花费的资源足以让你的服务器宕机。然而，在SQL Server2005中，有一项新的功能，名为数据库快照，可以让你很方便的处理类似的问题。

　　除了上面提到的优点，使用数据库快照还有很多其它的好处，本文将集中讨论数据库快照的优点。数据库快照是一项不太起眼的功能，这意味着，许多DBA和开发者都没有注意到它的存在。

　　数据库快照是什么

　　数据库快照是当前数据库的只读静态视图，不包括那些还没有提交的事务。没有提交的事务被回滚了，这样才能保证数据库的事务一致性。

　　工作原理

　　通常，我们使用一项功能的时候并不需要知道它的工作原理。然而，知道其工作原理将会给我们的工作带来大大的好处。

　　一旦你创建数据库快照，快照数据库将被分配到一个空闲文件中。当原始数据页发生变化的时候，该页就会被移动到这个空闲文件。当你访问数据库快照的时候，你会访问到空闲文件，以及原始数据库上那些没有发生变化的数据页。我们可以从下图看出数据库快照是如何工作的。


嗯,SQL2005以上可以用快照

[microsealkey]

jhonlonehou,是的，你说的对极了，
但怎样设计这个历史表呢？我觉得会太烦。而且即使设计了，可能也很难生成这个报表。你能否试着设计一下？

[microsealkey]

是的，快照就说到点子了，但很明显，快照太死板，我需要获得任意时刻的数据，但肯定无法生成"任意时刻"的快照。也就是说我不可能时时刻刻去生成快照。

[xman_78tom]

在 SQL Server 中，只能通过设计历史表实现。具体的，
在原表中创建一个触发器，将每次修改的行（修改前的值）都插入历史表，并且标记时间。查询特定时间的历史记录时，可以查询历史表中在此时间前所有的行，然后与原表中的行合并，消除原表中与历史表中重复的行（用 主键标示）。当然，这个只是主要思路，还细节问题。

oracle 数据库可以直接使用 flashback 查询实现。

[microsealkey]

谢谢xman_78tom，
不过如果是这样的方式，那还是另外设计历史表合适。从而不影响现有系统。

只是觉得因为状态变化比较频繁，而且有好些表都有这种要求，感觉到比较繁杂了。