数据库大神请点进来，有道SQL面试题求帮助

cetaphil · 最后编辑: 2018-04-20

应届生求职，面试full stack developer，第二面不停地问数据库问题，本人是数据库小白，就会写写sql query什么的。请问有人知道这题怎么做吗？

大概意思就是说如何写一个sql object，使你在任何时间都可以exactly reproduce past results（因为数据库每时每刻都在改变和更新所以在不同时间抓出来的信息不一样）

总共只有两个table：

Dbo.data1 (main data table, primary key is on date,id_security)
Data audit.data1 (audit table that contains history of changes. Changedate is date-time of the changes)

主table叫dbo.data1 ，有[date], [id_security], 和其他fields。
辅table叫data_audit.data1，有[id_data_audit_data1], [date], [id_security], [ChangeDate], [ChangeUser], [ChangeAction] 和其他fields。
每一次主table发生了变化，辅table就会记录下变化的时间，id_security，ChangeAction等等

面试题是：Create SQL objects to return Point-in-time data that generate past results

Create SQL object to show data in the same structure as dbo.data1 but as of certain date/time.

To be more clear

Now time is 2018-02-22 14:42:22.
- I do SELECT * FROM dbo.data1 WHERE date = '2011-12-31'
- and get some records.
In the future, even though records can be updated or deleted, if
- I do SELECT * FROM dbo.data1_PIT('2011-12-31', '2018-02-22 14:42:22')
- I will have to get identical output as I see now with the query under a.a (SELECT * FROM dbo.data1 WHERE date = '2011-12-31')
Based on the code in triggers, I would like you to elaborate on:
- Our approach to data audits. Triggers and PIT functions are generated automatically with stored procedure, so maintenance is not a problem.
- Ideas for improvement. There is certainly room for improvement.
- Is SQL 2008/12/16 built in audit better solution? We audit just selected tables.
- Any suggestions for audit table compression and/or partitioning.
请各位大侠指点迷津，小女子感激不尽。

更新：面试官加了一条：FYI, our live data table have 17 million records and 8 times more in audit table. Therefore, additional indexing is important part of the solution.

dennistan2009

首先，看了一遍题目，我的感觉这是个开放性的问题，也就是说并不一定有统一标准答案。
其次，题目的大概意思是每一次通过时间戳抓取的都是最新更新的数据，排除掉以前变化的数据。
最后，找了一遍目录，没找到这个PIT functions are generated automatically，估计就把这个PIT function写一下就行了，
PIT function也很简单就是抓取point in time数据，就是根据时间戳到Audit表里fetch一些数据就可以了。
至于其他的improvement, solution, suggestion就根据你学过的数据库知识对照这个题目套用一下，稍微延伸一下就可以了。

cetaphil

dennistan2009 说:
首先，看了一遍题目，我的感觉这是个开放性的问题，也就是说并不一定有统一标准答案。
其次，题目的大概意思是每一次通过时间戳抓取的都是最新更新的数据，排除掉以前变化的数据。
最后，找了一遍目录，没找到这个PIT functions are generated automatically，估计就把这个PIT function写一下就行了，
PIT function也很简单就是抓取point in time数据，就是根据时间戳到Audit表里fetch一些数据就可以了。
至于其他的improvement, solution, suggestion就根据你学过的数据库知识对照这个题目套用一下，稍微延伸一下就可以了。

感谢回复!我的思路大概就是建一个table-valued user-defined function: dbo.data1_PIT(@data_date smalldatetime, @as_of_date datetime)，return 一个data for one date。因为在校没上过数据库这门课还不太会写function。。还有就是对dbo.data1进行怎样的操作才会记录在data_audit.data1里面呢？是通过看trigger有哪几种吗？您觉得有哪些improvement/suggestion可取呢？

kirkli

用triggers来写，你的题目第三条已经要求了，网上找一下怎么写这属于t-sql

Creat trigger xxx on xxx
After insert, update, delete
As
If .....
Insert into xxx1 ()

cetaphil

kirkli 说:
用triggers来写，你的题目第三条已经要求了，网上找一下怎么写这属于t-sql

Creat trigger xxx on xxx
After insert, update, delete
As
If .....
Insert into xxx1 ()

大神好，trigger已经写好了，分别是delete，update和insert，现在的问题就是怎么写这个function dbo.data1_PIT，完全没写过function不知道怎么下手。。

代码:

ALTER TRIGGER [dbo].[trg_data1_Delete_all_fields]

    ON [dbo].[data1]

FOR DELETE

AS

    SET NOCOUNT ON
    DECLARE @uid INT
    SET @uid = SUSER_ID()
  
    -- [ChangeAction]=D means delete
    INSERT INTO [data_audit].[data1] ( [ChangeAction], [ChangeDate], [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72] )
        SELECT 'D' AS [ChangeAction],getdate() AS [ChangeDate], @uid AS [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72]
        FROM deleted

fieldmarshal

你把头像换个美女照马上一群人给你把答案做好

zhizhi123 · 最后编辑: 2018-04-20

说实话这个挺难的像是 dba 的问题你是硕士或者是博士吧。
只能讲讲我的思路，具体code 你要自己写因为我也不太会。
Dbo.data1 trigger 记录每次主table 的改变，action 和值 ,时间等写在 audit table 。

然后 query 的时候把现在的 table 根据 audit table 倒叙做 revert 直到 query 的时间。
比如 audit table 写了 update 了一个值，那你要改为原来的值， insert 的row delete, delete 的row insert, 注意顺序必须按倒叙，这样就能还原到原来的那个时间点时那个 table 的样子。

另外一个思路是 snap shot，每次改变table 的时候 trigger snap shot , 记录一个当前database 的快照，然后取的时候取 snap shot,

cetaphil · 最后编辑: 2018-04-20

zhizhi123 说:
说实话这个挺难的像是 dba 的问题你是硕士或者是博士吧。
只能讲讲我的思路，具体code 你要自己写因为我也不太会。
Dbo.data1 trigger 记录每次主table 的改变，action 和值 ,时间等写在 audit table 。
然后 query 的时候把现在的 table 根据 audit table 倒叙做 revert 直到 query 的时间。
比如 audit table 写了 update 了一个值，那你要改为原来的值， insert 的row delete, delete 的row insert, 注意顺序必须按倒叙，这样就能还原到原来的那个时间点时那个 table 的样子。

另外一个思路是 snap shot，每次改变table 的时候 trigger snap shot , 记录一个当前database 的快照，然后取的时候取 snap shot,

我是数学专业本科生，辅修了一些计算机课程，没上过数据库这门课，对sql的了解仅限SELECT FROM。。。本来一面的时候问的都是一些基础数据结构Java Javascript Nodejs等等，哪里知道二面的面试官抓着我问SQL，上来就是trigger，完全没学过。他本来要拒绝我了，看在一面面试官极力推荐的份上给我留个题看能不能解出来。。。
面试官给我的思路是也用trigger，他已经把trigger写好了，分别是insert，delete和update，update的时候往audit table里插入旧的数据，我现在要做的就是写那个dbo.data1_PIT function。您的倒叙思路跟我想的一样，只是我还没搞清楚具体该怎么实现。。。

代码:

ALTER TRIGGER [dbo].[trg_data1_Insert_all_fields]

    ON [dbo].[data1]




FOR INSERT

AS

    SET NOCOUNT ON
    DECLARE @uid INT
    SET @uid = SUSER_ID()
 
    --inserts only - we just need to update user information
    UPDATE u
    SET
        u.[AddLoginID]=@uid,
        u.[AddDate]=getdate()
    FROM [dbo].[data1] u
    INNER JOIN inserted i ON
     i.[date] = u. [date] AND  i.[id_security] = u. [id_security]
    WHERE u.[AddLoginID] IS NULL AND u.[AddDate] IS NULL

代码:

ALTER TRIGGER [dbo].[trg_data1_Delete_all_fields]

    ON [dbo].[data1]

FOR DELETE

AS

    SET NOCOUNT ON
    DECLARE @uid INT
    SET @uid = SUSER_ID()
 
    -- [ChangeAction]=D means delete
    INSERT INTO [data_audit].[data1] ( [ChangeAction], [ChangeDate], [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72] )
        SELECT 'D' AS [ChangeAction],getdate() AS [ChangeDate], @uid AS [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72]
        FROM deleted

代码:

ALTER TRIGGER [dbo].[trg_data1_Update_all_fields]

    ON [dbo].[data1]

FOR UPDATE
AS

    SET NOCOUNT ON
    DECLARE @uid INT
    SET @uid = SUSER_ID()

    IF ( SELECT trigger_nestlevel()
       ) = 1   --don't do anything if triggered from insert trigger
        BEGIN 
        --[ChangeAction]=U means update
        INSERT INTO [data_audit].[data1] ( [ChangeAction], [ChangeDate], [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72] )
        SELECT 'U' AS [ChangeAction],getdate() AS [ChangeDate], @uid AS [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72]FROM
        (
        SELECT [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72]
        FROM deleted
        EXCEPT
        SELECT [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72]
        FROM inserted
        ) t
     
        END

fieldmarshal

zhizhi123 说:
说实话这个挺难的像是 dba 的问题你是硕士或者是博士吧。
只能讲讲我的思路，具体code 你要自己写因为我也不太会。
Dbo.data1 trigger 记录每次主table 的改变，action 和值 ,时间等写在 audit table 。

然后 query 的时候把现在的 table 根据 audit table 倒叙做 revert 直到 query 的时间。
比如 audit table 写了 update 了一个值，那你要改为原来的值， insert 的row delete, delete 的row insert, 注意顺序必须按倒叙，这样就能还原到原来的那个时间点时那个 table 的样子。

另外一个思路是 snap shot，每次改变table 的时候 trigger snap shot , 记录一个当前database 的快照，然后取的时候取 snap shot,

这个很容易！这题都做不了的选手在公司是个废人

zhizhi123

fieldmarshal 说:
这个很容易！这题都做不了的选手在公司是个废人

这么容易，你给把那个pit 的 function 写一下贴上来呗。吹牛胡说谁不会啊。

fieldmarshal

zhizhi123 说:
这么容易，你给把那个pit 的 function 写一下贴上来呗。吹牛胡说谁不会啊。

吹牛不动脑轻松你不知道么？哈
不过真的，我是告诉你业界的标准在那里，你要是不信随便你，呵呵
网上很多公开的面经，你看看大公司稍微有点难度的题目！你还可以找几个面试是开卷考试的公司做做
这题做好了应该可以拿到offer如果公司要求不苛刻，但这个难度一般不是压轴题

zhizhi123

fieldmarshal 说:
吹牛不动脑轻松你不知道么？哈
不过真的，我是告诉你业界的标准在那里，你要是不信随便你，呵呵
网上很多公开的面经，你看看大公司稍微有点难度的题目！你还可以找几个面试是开卷考试的公司做做
这题做好了应该可以拿到offer如果公司要求不苛刻，但这个难度一般不是压轴题

啊呸，

fieldmarshal

zhizhi123 说:
啊呸，

年轻人不服气啊，哈
若干年后你就会明白我说的这都不会的在公司基本是个废人

kirkli

给你找了个例子，和一个有用的中文网站，希望能有所帮助。
http://www.postgres.cn/docs/10/plpgsql-trigger.html

讨论技术本是好事，大家相互学习进步。

CREATE TABLE emp (
empname text,
salary integer,
last_date timestamp,
last_user text
);

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
-- 检查给出了 empname 以及 salary
IF NEW.empname IS NULL THEN
RAISE EXCEPTION 'empname cannot be null';
END IF;
IF NEW.salary IS NULL THEN
RAISE EXCEPTION '% cannot have null salary', NEW.empname;
END IF;

-- 谁会倒贴钱为我们工作？
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
END IF;

-- 记住谁在什么时候改变了工资单
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

道白2

cetaphil 说:

我是数学专业本科生，辅修了一些计算机课程，没上过数据库这门课，对sql的了解仅限SELECT FROM。。。本来一面的时候问的都是一些基础数据结构Java Javascript Nodejs等等，哪里知道二面的面试官抓着我问SQL，上来就是trigger，完全没学过。他本来要拒绝我了，看在一面面试官的面子上给我留个题看能不能解出来。。。
面试官给我的思路是也用trigger，他已经把trigger写好了，分别是insert，delete和update，update的时候往audit table里插入旧的数据，我现在要做的就是写那个dbo.data1_PIT function。您的倒叙思路跟我想的一样，只是我还没搞清楚具体该怎么实现。。。

代码:

ALTER TRIGGER [dbo].[trg_data1_Insert_all_fields]

    ON [dbo].[data1]




FOR INSERT

AS

    SET NOCOUNT ON
    DECLARE @uid INT
    SET @uid = SUSER_ID()
 
    --inserts only - we just need to update user information
    UPDATE u
    SET
        u.[AddLoginID]=@uid,
        u.[AddDate]=getdate()
    FROM [dbo].[data1] u
    INNER JOIN inserted i ON
     i.[date] = u. [date] AND  i.[id_security] = u. [id_security]
    WHERE u.[AddLoginID] IS NULL AND u.[AddDate] IS NULL

代码:

ALTER TRIGGER [dbo].[trg_data1_Delete_all_fields]

    ON [dbo].[data1]

FOR DELETE

AS

    SET NOCOUNT ON
    DECLARE @uid INT
    SET @uid = SUSER_ID()
 
    -- [ChangeAction]=D means delete
    INSERT INTO [data_audit].[data1] ( [ChangeAction], [ChangeDate], [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72] )
        SELECT 'D' AS [ChangeAction],getdate() AS [ChangeDate], @uid AS [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72]
        FROM deleted

代码:

ALTER TRIGGER [dbo].[trg_data1_Update_all_fields]

    ON [dbo].[data1]

FOR UPDATE
AS

    SET NOCOUNT ON
    DECLARE @uid INT
    SET @uid = SUSER_ID()

    IF ( SELECT trigger_nestlevel()
       ) = 1   --don't do anything if triggered from insert trigger
        BEGIN 
        --[ChangeAction]=U means update
        INSERT INTO [data_audit].[data1] ( [ChangeAction], [ChangeDate], [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72] )
        SELECT 'U' AS [ChangeAction],getdate() AS [ChangeDate], @uid AS [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72]FROM
        (
        SELECT [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72]
        FROM deleted
        EXCEPT
        SELECT [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72]
        FROM inserted
        ) t
     
        END

好像你都已经做完了，难道你的问题是数据库里的表的函数，是怎么定义的，写在哪里，怎么被调用吗？我感觉你的问题是不太明白ddl（数据定义语言）,与dml（数据操纵语言）的写在哪里，怎么被调用，他们的运行机制的问题。

让我们理一下这个过程，
1、建立一个辅助表，分别记录insert, update, delete，这个需要在你说的主表上建立trigger（其实也可以用的别方法）, 你已经完成了。
2、从这个辅助表里面，a、把所需的记录根据查询条件取出来，b、要求用一个 function来实现。
a、就是查询语句，就是select * from table where condition in [.......] desc by field, 类似这个样子吧（我好多年不写程序了，记不住精确的语法了

) 。这是dml。
b、怎么把a的语句放到一个function里，并将查询条件传入。
---这涉及到function的含义，时间上你可以理解database里有两种function.
b-1：在database里，表可以有自己的function，定义一个表的function，这个叫ddl。在这个function里可以写dml语言。但是，表的function里是不接受参数，我好像记得不接受。（当然，各种数据库上可能会有差异）
b-2: storage procedure. 这里定义函数(DDL)，接受参数,并返回数据集。我觉得你的问题在存储过程上写一个函数，在函数体内按要求写查询语句，返回结果集就好。
c、一点建议，一般程序员不会重复的去写ddl语句，能看懂就行了。这些ddl都是工具产生的，面试的时候也不会去刻意问你ddl,都是问些dml。你下个navcat或者用access，在上面图像化的界面上操作一下，然后看相应应的dml就明白了。

优化，
简单的说吧，1、patch，只记录不同。然后再function里来生成数据集返回。
2、取决于系统架构和软件结构，是开timer还是用线程，怎么预先生成？等等。。
3、

cetaphil

道白2 说:
好像你都已经做完了，难道你的问题是数据库里的表的函数，是怎么定义的，写在哪里，怎么被调用吗？我感觉你的问题是不太明白ddl（数据定义语言）,与dml（数据操纵语言）的写在哪里，怎么被调用，他们的运行机制的问题。

让我们理一下这个过程，
1、建立一个辅助表，分别记录insert, update, delete，这个需要在你说的主表上建立trigger（其实也可以用的别方法）, 你已经完成了。
2、从这个辅助表里面，a、把所需的记录根据查询条件取出来，b、要求用一个 function来实现。
a、就是查询语句，就是select * from table where condition in [.......] desc by field, 类似这个样子吧（我好多年不写程序了，记不住精确的语法了) 。这是dml。
b、怎么把a的语句放到一个function里，并将查询条件传入。
---这涉及到function的含义，时间上你可以理解database里有两种function.
b-1：在database里，表可以有自己的function，定义一个表的function，这个叫ddl。在这个function里可以写dml语言。但是，表的function里是不接受参数，我好像记得不接受。（当然，各种数据库上可能会有差异）
b-2: storage procedure. 这里定义函数(DDL)，接受参数,并返回数据集。我觉得你的问题在存储过程上写一个函数，在函数体内按要求写查询语句，返回结果集就好。
c、一点建议，一般程序员不会重复的去写ddl语句，能看懂就行了。这些ddl都是工具产生的，面试的时候也不会去刻意问你ddl,都是问些dml。你下个navcat或者用access，在上面图像化的界面上操作一下，然后看相应应的dml就明白了。

优化，
简单的说吧，1、patch，只记录不同。然后再function里来生成数据集返回。
2、取决于系统架构和软件结构，是开timer还是用线程，怎么预先生成？等等。。
3、

真的好感动！谢谢您打了这么多字，这还是您的第一个帖子。
我确实是不太会写这个function，主要是sql的syntax不熟悉碰到复杂一些的query就蒙了。现在的思路是，function 设两个parameter @data_date smalldatetime, @as_of_date datetime 先检查在as_of_date时间点之后是否发生了insert delete update，如果没有改变就直接从主表里select，如果有改变就select离as_of_date时间点最近的那个时间点然后return。大概pseudocode写出来了但是不知道怎么写成function.

这是我的 Inline table-valued function，属于语句完全不通状态，算是pseudocode吧。。
还有就是，同一个@data_date会有多个id_security但还没想好怎么处理这点

代码:

CREATE FUNCTION [dbo].[data1_PIT](@data_date smalldatetime, @as_of_date datetime)
RETURNS TABLE
AS
RETURN
SELECT
IF EXISTS (SELECT * FROM data_audit.data1 AS DA WHERE DA.date = @data_date AND DA.ChangeDate > @as_of_date)
        WITH cte AS
        (
            SELECT DA.*, ROW_NUMBER() OVER(PARTITION BY DA.id_security ORDER BY DA.ChangeDate) AS rk
            FROM data_audit.data1 AS DA WHERE DA.date=@data_date AND DA.ChangeDate > @as_of_date)
        )
        SELECT date,
        id_security,
        column6,
        column7,
        column10,
        column11,
        column12,
        column13,
        column14,
        column15,
        column16,
        column17,
        column18,
        column19,
        column20,
        column21,
        column22,
        column23,
        column24,
        column25,
        column26,
        column27,
        column28,
        column29,
        column30,
        column31,
        column32,
        column33,
        column34,
        column35,
        column36,
        column37,
        column38,
        column39,
        column40,
        column41,
        column42,
        column43,
        column44,
        column45,
        AddLoginID,
        AddDate,
        column48,
        column49,
        column50,
        column51,
        column52,
        column53,
        column54,
        column55,
        column56,
        column57,
        column58,
        column59,
        column60,
        column61,
        column62,
        column64,
        column65,
        column66,
        column67,
        column68,
        column69,
        column70,
        column71,
        column72
        FROM cte
        where rk = 1;
ELSE
    SELECT D.* FROM dbo.data1 AS D WHERE date = @data_date
END

cetaphil

fieldmarshal 说:
这个很容易！这题都做不了的选手在公司是个废人

大神，可否指导我一下怎么写这个function() ？抓耳挠腮想了半天还是没写出来，下周一还有两个final，现在重新啃sql syntax也来不及了。

zhizhi123 · 2018-04-20 #18 good luck 超赞 反馈: cetaphil

good luck

Chinada

zhizhi123 说:
说实话这个挺难的像是 dba 的问题你是硕士或者是博士吧。
只能讲讲我的思路，具体code 你要自己写因为我也不太会。
Dbo.data1 trigger 记录每次主table 的改变，action 和值 ,时间等写在 audit table 。

然后 query 的时候把现在的 table 根据 audit table 倒叙做 revert 直到 query 的时间。
比如 audit table 写了 update 了一个值，那你要改为原来的值， insert 的row delete, delete 的row insert, 注意顺序必须按倒叙，这样就能还原到原来的那个时间点时那个 table 的样子。

另外一个思路是 snap shot，每次改变table 的时候 trigger snap shot , 记录一个当前database 的快照，然后取的时候取 snap shot,

应该不用倒叙那么复杂，一个复杂查询就行了。

查询audit表的条件：
- 记录时间小于等于函数参数指定的时间
- 每个主键值只取时间最新的那条记录
- 记录的状态不能是删除

主键在这里特指主表里面绝对不会被修改、而且唯一的字段。

cetaphil

Chinada 说:
应该不用倒叙那么复杂，一个复杂查询就行了。

查询audit表的条件：
- 记录时间小于等于函数参数指定的时间
- 每个主键只取时间最新的那条记录
- 记录的状态不能是删除

主键在这里特指主表里面绝对不会被修改、而且唯一的字段。

嗯嗯我已经基本上写出query来了，就是不太会sql语法。我的function 设两个parameter @data_date smalldatetime, @as_of_date datetime 先检查在as_of_date时间点之后是否发生了insert delete update，如果没有改变就直接从主表里select，如果有改变就select离as_of_date最近的时间点然后return。大概pseudocode写出来了但是不知道怎么写成function。

代码:

CREATE FUNCTION [dbo].[data1_PIT](@data_date smalldatetime, @as_of_date datetime)
RETURNS TABLE
AS
RETURN
IF EXISTS (SELECT * FROM data_audit.data1 AS DA WHERE DA.date = @data_date AND DA.ChangeDate >= @as_of_date)
    WITH cte AS
        (
            SELECT DA.*, ROW_NUMBER() OVER(PARTITION BY DA.id_security ORDER BY DA.ChangeDate) AS rk
            FROM data_audit.data1 AS DA WHERE DA.date=@data_date AND DA.ChangeDate >= @as_of_date)
        )
        SELECT date,
        id_security,
        column6,
        column7,
        column10,
        column11,
        column12,
        column13,
        column14,
        column15,
        column16,
        column17,
        column18,
        column19,
        column20,
        column21,
        column22,
        column23,
        column24,
        column25,
        column26,
        column27,
        column28,
        column29,
        column30,
        column31,
        column32,
        column33,
        column34,
        column35,
        column36,
        column37,
        column38,
        column39,
        column40,
        column41,
        column42,
        column43,
        column44,
        column45,
        AddLoginID,
        AddDate,
        column48,
        column49,
        column50,
        column51,
        column52,
        column53,
        column54,
        column55,
        column56,
        column57,
        column58,
        column59,
        column60,
        column61,
        column62,
        column64,
        column65,
        column66,
        column67,
        column68,
        column69,
        column70,
        column71,
        column72
        FROM cte
        where rk = 1;
ELSE
    SELECT D.* FROM dbo.data1 AS D WHERE date = @data_date
END

数据库大神请点进来，有道SQL面试题求帮助

更多选项

cetaphil

附件

dennistan2009

cetaphil

附件

kirkli

cetaphil

fieldmarshal

zhizhi123

cetaphil

fieldmarshal

zhizhi123

fieldmarshal

zhizhi123

fieldmarshal

kirkli

道白2

cetaphil

cetaphil

zhizhi123

Chinada

cetaphil

Similar threads