数据库大神请点进来，有道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面试题求帮助

程序员妹子

程序员妹子

程序员妹子

一网

程序员妹子

一网

程序员妹子

一网

程序员妹子

Similar threads

家园推荐黄页

家园币系统数据