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

xiaoa

Mimi2007 说:
是因为长得漂亮的人写Code都很糟糕吗？呵呵。我写Code也只有两年，初级水平，还在努力进步中，见笑了。

我自己都6年 full stack developer ，而且自认作数据库还可以，都没做对。
你爱热心帮助人，还挺自信，这只能是来自要么你是大神，大牛，要么你长得非常漂亮。
你逻辑一般，所以.....

Mimi2007 · 最后编辑: 2018-04-21

xiaoa 说:
我自己都6年 full stack developer ，而且自认作数据库还可以，都没做对。
你爱热心帮助人，还挺自信，这只能是来自要么你是大神，大牛，要么你长得非常漂亮。
你逻辑一般，所以.....

我的头像是我本人。不过每个人审美不同，所以我也不敢说我很漂亮。我是学会计出身，所以写code前要问好多问题确定stakeholders 要什么结果才开始写，也算是文科生的一个毛病吧。不过一旦我理解了要什么结果，写的Code还是不太差的，呵呵。

kirkli

Mimi2007 说:
首先指出我和楼主对题目理解不同的几点：

1. In the future, even though records can be updated or deleted-所以我认为这里只是指Update 和Delete 而不包括insert。
2. I do SELECT * FROM dbo.data1_PIT('2011-12-31', '2018-02-22 14:42:22')-这里的dbo.data1_PIT 是function的名字，不是指原始的table: data1；题目中并没有说history log要从原始table data1中选出. 我是这样理解的，用delete 做例子，既然delete已经发生，原始data1 table里就不会再存在那条信息，又如何能从原始table: data1里select那条信息呢？别的公司我不知道，我们公司是原始table delete records, 然后只在audit table 中保留历史记录，用于万一需要revert的时候用。

帮楼主把Trigger 和Function写好了，也在我电脑上简单测试了一下，运行没问题。刚毕业都不容易，特别是女生做developer可能会更难一些。祝楼主好运。

---Create Trigger-I used delete as an an example；but this trigger can be modified to use for all update/delete/insert.
Create trigger Demo_Trigger
on data1
for delete
as
begin
set nocount on
insert into data_audit_data1
(date, id_security,changedate,ChangeUser,column6,column7)
select date, id_security,getdate(),SUser_SName(),column6,column7 ---I only used two columns as example, but you can reference all the columns
from deleted
end
go

---Test--Delete any row from data1 to confirm the history log will be inserted into the audit table: data_audit_data1
delete from data1
where date='1976-12-31' and id_security=57 and column6='2030' ---This is the first row in your sample table: data1; you can delete any other row as well.

---Create function: dbo.data1_PIT
Create function dbo.data1_PIT
(@OriginalDate datetime, @ChangeDate datetime)
returns table
as
return
select date,id_security,column6, column7 from data_audit_data1 where date=@OriginalDate and changedate=@ChangeDate
go

---Use function: dbo.data1_PIT to retrieve data that has been deleted above (See attached screenshot below for testing result)

select * from dbo.data1_PIT ('1976-12-31 00:00:00.000','2018-04-21 16:13:52.430')

这位美女做的挺好的，简明易懂。

1.根据原始表的删除修改等变化进而触发trigger在audit表里面做记录。
2. 创建查询audit记录的function
3.调用这个function浏览结果

cetaphil

kirkli 说:
这位美女做的挺好的，简明易懂。

1.根据原始表的删除修改等变化进而触发trigger在audit表里面做记录。
2. 创建查询audit记录的function
3.调用这个function浏览结果

Mimi写的第二个parameter changeDate是数据更改并记录进audit的时间，然而面试官要求的parameter是上次进行select操作的时间

xiaoa

cetaphil 说:
Mimi写的第二个parameter changeDate是数据更改并记录进audit的时间，然而面试官要求的parameter是上次进行select操作的时间

所以你要改她的那个 function 里面的逻辑。

cetaphil

xiaoa 说:
所以你要改她的那个 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,
        FROM cte
        where rk = 1;
ELSE
    SELECT D.* FROM dbo.data1 AS D WHERE date = @data_date
END

。。我写了一个query但是不知道怎么变成function，而且我还没有完全考虑不同security id的问题，您看看能修改一下吗？

fieldmarshal

xiaoa 说:
我自己都6年 full stack developer ，而且自认作数据库还可以，都没做对。
你爱热心帮助人，还挺自信，这只能是来自要么你是大神，大牛，要么你长得非常漂亮。
你逻辑一般，所以.....

和美女搭讪也不能光靠贬低自己水平……

kirkli

mimi已经做出来了并且简明易懂，唯一个逻辑上需要修改的地方红色部分已经指出
打个比方现在是 2019年1月1日，我们要查询2018年4月21日那个时候的结果。那么在function里面 @ChangeDate ＝ '2018-04-21 0:0:0.000', 我们只需要在函数里面限定changedate<=@ChangeDate 就可以了。
因为任何在2018-04-21之后的修改删除都是新的纪录纪录在deleted表里面。跟你要查询的这个时间无关。

仅供参考。

多谢mimi的code。

---Create Trigger-I used delete as an an example；but this trigger can be modified to use for all update/delete/insert.
Create trigger Demo_Trigger
on data1
after delete,update
as
begin
set nocount on
insert into audit_data1
(date, id_security,changedate,ChangeUser,column6,column7)
select date, id_security,getdate(),ChangeUserName,column6,column7 ---I only used two columns as example, but you can reference all the columns
from deleted
end
go

---Test--Delete any row from data1 to confirm the history log will be inserted into the audit table: data_audit_data1
delete from data1
where date='1976-12-31' and id_security=57 and column6='2030' ---This is the first row in your sample table: data1; you can delete any other row as well.

---Create function: dbo.data1_PIT
Create function dbo.data1_PIT
(@OriginalDate datetime, @ChangeDate datetime)
returns table
as
return
select date,id_security,column6, column7 from audit_data1 where date=@OriginalDate and changedate<=@ChangeDate
go

---Use function: dbo.data1_PIT to retrieve data that has been deleted above (See attached screenshot below for testing result)

select * from dbo.data1_PIT ('1976-12-31 00:00:00.000','2018-04-21 16:13:52.430')

xiaoa

fieldmarshal 说:
和美女搭讪也不能光靠贬低自己水平……

大爷，我就这一招。你行你up 给我们看看怎么搭讪。

楼上的，如果有一个记录从来没被改过，所以肯定不在 audit table 里，你这样选出来的 table 肯定不包含这个记录，所以....
如果有一个记录被改过2次，所以在 audit table 里有两个记录，你这样选出来的 2个都有。这肯定也不对。
等等。所以.....

cetaphil

kirkli 说:
mimi已经做出来了并且简明易懂，唯一个逻辑上需要修改的地方红色部分已经指出
打个比方现在是 2019年1月1日，我们要查询2018年4月21日那个时候的结果。那么在function里面 @ChangeDate ＝ '2018-04-21 0:0:0.000', 我们只需要在函数里面限定changedate<=@ChangeDate 就可以了。
因为任何在2018-04-21之后的修改删除都是新的纪录纪录在deleted表里面。跟你要查询的这个时间无关。

仅供参考。

多谢mimi的code。

---Create Trigger-I used delete as an an example；but this trigger can be modified to use for all update/delete/insert.
Create trigger Demo_Trigger
on data1
after delete,update
as
begin
set nocount on
insert into audit_data1
(date, id_security,changedate,ChangeUser,column6,column7)
select date, id_security,getdate(),ChangeUserName,column6,column7 ---I only used two columns as example, but you can reference all the columns
from deleted
end
go

---Test--Delete any row from data1 to confirm the history log will be inserted into the audit table: data_audit_data1
delete from data1
where date='1976-12-31' and id_security=57 and column6='2030' ---This is the first row in your sample table: data1; you can delete any other row as well.

---Create function: dbo.data1_PIT
Create function dbo.data1_PIT
(@OriginalDate datetime, @ChangeDate datetime)
returns table
as
return
select date,id_security,column6, column7 from audit_data1 where date=@OriginalDate and changedate<=@ChangeDate
go

---Use function: dbo.data1_PIT to retrieve data that has been deleted above (See attached screenshot below for testing result)

select * from dbo.data1_PIT ('1976-12-31 00:00:00.000','2018-04-21 16:13:52.430')

您写的changedate<=@ChangeDate会把这个row自创建以来一直到@ChangeDate的所有update/delete的log都return出来的，根据要求，我们只想return出一条row。而且应该是在@ChangeDate之后的第一条row。原因是无论是delete还是update存进audit table都是被删除的row。
我的表达能力不太好，就举个栗子：
假设这条row是2018-04-20日创建的，我们在2018-04-21 做了select * from dbo.data1 where date = '2018-04-20', 接着这条row马上被更新了好几次，这个时候存进audit table里面的是这条row每次被更新之前的状态（更新之后的就update在主table里面）那么我们在2018-02-22重新做select * from dbo.data1 where date = '2018-04-20'的话，我们看到的是更新之后的状态。如果想看到同在2018-04-21 return出来一样的row，我们只能去audit table里面找2018-04-21之后的第一条记录。
还有一种情况是2018-04-21后这个table没有做过改变，那么我们只要从主table里面select就可以了。不知道解释是否清楚。

Mimi2007 · 最后编辑: 2018-04-21

cetaphil 说:
美女姐姐别生气，我说的有些绕，xiaoa表达的比我清楚。您的思路是对的，只是跟面试官的要求不太一致。面试官后来还加了一句hint：
I also wanted to give you few hints about my own solution:

I decided to use Table-Valued User-Defined Function

I created function: dbo.data1_PIT(@data_date smalldatetime, @as_of_date datetime).

The function returns data for one date, in structure identical to the structure of dbo.data1

According to my simple measurements, overhead of SELECT * statement over our live table is just 10%.
我再看看tutorial怎么写function吧。这个操作的话inline table-valued function可以实现吗？我要从两个table里select的话是不是要写multi statement的那种？

我没有生气啊。理解不同是常有的事。我在公司做Project的时候也会有和Project manger理解不同的时候，所以有时候即使code 写出来，还是要重新改过的，呵呵。

我按照你的思路改了一下我之前写的function，你看这回满足你们面试官的要求吗？我明天要早起出门，只能帮你到这里了，祝你好运！

---Create dbo.data1_PIT function
alter function dbo.data1_PIT
(@data_date smalldatetime, @as_of_date datetime)
returns @history table
(
date datetime, id_security int, column6 varchar (100),column7 varchar (100)
)
as
begin
if (select count(*) from data_audit_data1 where date=@data_date and changedate>=@as_of_date)>0
begin
insert into @history
select date, id_security, column6, column7 from data_audit_data1
where changedate=(select min(changedate) from data_audit_data1
where changedate>@as_of_date and date=@data_date
group by date)
end
else
insert into @history
select date, id_security, column6, column7 from data1 where date=@data_date
return
end
go

---Retrive data
select * from dbo.data1_PIT ('1976-12-31 00:00:00.000','2018-02-22 14:42:22')

道白2

Mimi2007 说:
我是按英文这部分来理解给的建议。我的理解可能是错的，不过我也只是好心给出我的看法而已。我们做project 的话都有详细的project doc, 所以不存在理解有分歧的情况。因为如果什么理解有歧义，在写code 之前就会找 stakeholder 问明白了。呵呵。我
不是很理解楼主的题目，所以不好意思帮不到楼主。之前楼主说不会写function的 syntax, 我的例子中至少syntax 是对的，楼主可以按你的理解改一下来用吧。

面试题是：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.

给你点个赞。你做的已经足够好了。
技术无对错。这个对错并非是技术问题本身没有是非曲直。而是说技术讨论中的对错标准，往往掺杂了讨论者自身的技术能力，理解水平、判断标准和个人素养，最终的讨论往往偏离了技术问题本身的对与错。
所以不用在意技术问题之外评价，做自己想做的事情，帮能帮的事情就好。

Mimi2007

道白2 说:
给你点个赞。你做的已经足够好了。
技术无对错。这个对错并非是技术问题本身没有是非曲直。而是说技术讨论中的对错标准，往往掺杂了讨论者自身的技术能力，理解水平、判断标准和个人素养，最终的讨论往往偏离了技术问题本身的对与错。
所以不用在意技术问题之外评价，做自己想做的事情，帮能帮的事情就好。

谢谢道白兄点赞，

。

dimples

呵呵，Mimi也是Developer啊，赞。
audit log table在workflow应用中非常常见。我若面试楼主，倒想问问如何优化这个table。

Mimi2007

一网打尽说:
呵呵，Mimi也是Developer啊，赞。
audit log table在workflow应用中非常常见。我若面试楼主，倒想问问如何优化这个table。

是呀，我也是做developer的，小码农一枚，呵呵。

dimples

Mimi2007 说:
是呀，我也是做developer的，小码农一枚，呵呵。

我也是，混碗饭吃。以后帖里还要请Mimi多多指教。

Mimi2007

一网打尽说:
我也是，混碗饭吃。以后帖里还要请Mimi多多指教。

大叔也是Developer哦，真巧。我经验还少，连senior 都没做上呢。希望是我向你请教才是，呵呵。

dimples

Mimi2007 说:
大叔也是Developer哦，真巧。我经验还少，连senior 都没做上呢。希望是我向你请教才是，呵呵。

你谦虚。软件行业是年轻人的天下。
我们这种中年移民做计算机，无非是通过工作求生存。
而你们年轻人，可以实现个人抱负，造就一番大事业。

Mimi2007 · 最后编辑: 2018-04-21

一网打尽说:
你谦虚。软件行业是年轻人的天下。
我们这种中年移民做计算机，无非是通过工作求生存。
而你们年轻人，可以实现个人抱负，造就一番大事业。

呵呵，我没那么大野心。对我来说也只是一份工作而已。高薪就更好了， :wdb6:

。大叔我闪人睡觉去了，你也早点休息。改天聊。

foreverjia

感觉目前回答的思路好像都不大对，简单说说我的想法：
1. 第一个 WHERE date = '2011-12-31', 不要管他，这个给人带了很多confusion, 你可以把它当成任何一个Where Condition, 就像Where CompanyName='xxx'.
反正是得到一个DataSet，这个时间点可以完全忽略。

2. 两个关键的时间点，第一个是ChangeDate, 像题目中的'2018-02-22 14:42:22', 是Hisotry Date, 另外一个其实是隐含的时间点，题目中没有给出了,但实际上
很重要，就是当前的时间点，CurrentTime = GetDate()

3. 所有的DataSet还是只能从主DataTable里选取，这是唯一的Source，但是主DataTable里只有当前时间点的数据，所以也要用到Audit Table.

4. 基本思路就是： SELECT * FROM dbo.data1 WHERE date = '2011-12-31'，得到当前时间点的DataSet, 然后再Revert所有的Changes between History Data between Current Time from audit Table:
分三种：Insert的必须要Exclude, Delete的要Union起来，Update的要找回原值。

SELECT * FROM dbo.data1 WHERE date = '2011-12-31' AND PrimaryID NOT IN (SELECT PrimaryID FROM data_audit_data1 WHERE date = '2011-12-31' and TriggerType='Insert' and ChangeDate > '2018-02-22 14:42:22' )
UNION
SELECT * FROM data_audit_data1 WHERE date = '2011-12-31' and TriggerType='Delete' and ChangeDate > '2018-02-22 14:42:22'

这个可以把Hisotry Data和Current Time之间的 Insert和Delete给 Revert回去，

还有一个Update的比较麻烦一点，因为同样一个PrimaryID可能会被Update几次，应该是只用最早时间的的一次Record的原值，这个部分就懒得写了，把这部分和上面的DataSet合并
，就得到你想要的结果了。

上面的所有的只是思路和抽象代码，你理解了剩下的应该很容易写出来的。

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

更多选项

xiaoa

Mimi2007

程序员妹子

kirkli

cetaphil

xiaoa

cetaphil

fieldmarshal

kirkli

xiaoa

cetaphil

Mimi2007

程序员妹子

道白2

Mimi2007

程序员妹子

dimples

一网

Mimi2007

程序员妹子

dimples

一网

Mimi2007

程序员妹子

dimples

一网

Mimi2007

程序员妹子

foreverjia

Similar threads