SQL：查询返回不准确的数据条目

发布于 2024-08-20 09:59:53 字数 862 浏览 3 评论 0原文

我的公司需要编写一个查询来确定何时输入的数据不准确。我们的数据库中有一个名为“ProductChanges”的表。它看起来像这样 -

Product    User     ChangeDate         OldValue     NewValue
2344       John     24/01/10 10:00         2344         4324
6435       Greg     28/01/10 13:30          543         6435
6532       Tony     29/01/10 09:45       <NULL>          234

当 OldValue 为时，这意味着它是一个新的产品记录。我们预计在记录创建后的第一个小时内将会出现许多更改，因为所有数据都会更新。但此后的任何变化都被认为意味着最初的记录记录不准确。

所以...我想要提出的是一个查询，该查询返回上个月内创建的所有产品记录，这些记录在创建初始记录一小时后记录了任何后续更改。对于输出，我们需要初始记录以及所有后续更改。例如。 -

Product    User     ChangeDate         OldValue     NewValue
6532       Tony     29/01/10 09:45       <NULL>          234
6532       Fred     01/02/10 11:37          234         4324

提前致谢！

更新：我们正在 SQL Server 2000 上运行此数据库

原文

My company needs to write a query that determines when data is being entered inaccurately. We have a table in our db called "ProductChanges". It looks (something) like this -

Product    User     ChangeDate         OldValue     NewValue
2344       John     24/01/10 10:00         2344         4324
6435       Greg     28/01/10 13:30          543         6435
6532       Tony     29/01/10 09:45       <NULL>          234

When OldValue is <NULL>, it means that it is a new product record. We expect there will be a number of changes within the first hour of a record creation, as all the data is updated. But any changes after that are considered to mean the initial record was inaccurately recorded.

SO...what I am trying to come up with is a query that returns all product records created within the last month, that have any subsequent changes recorded an hour after the initial record was created. For output, we want the initial record plus all subsequent changes. Eg. -

Product    User     ChangeDate         OldValue     NewValue
6532       Tony     29/01/10 09:45       <NULL>          234
6532       Fred     01/02/10 11:37          234         4324

Thanks in advance!

Update: We are running this database on SQL Server 2000

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

坚持沉默 2024-08-27 09:59:53

如果我理解正确的话，看起来非常简单 - 只需获取输入日期并加入一个多小时后发生的任何更改即可。

SELECT pc.Product, pc.User, pc.ChangeDate, pc.OldValue, pc.NewValue
FROM
(
    SELECT Product, ChangeDate AS EntryDate
    FROM ProductChanges
    WHERE OldValue IS NULL
) e
INNER JOIN ProductChanges pc
    ON pc.Product = e.Product
WHERE EXISTS
(
    SELECT 1
    FROM ProductChanges
    WHERE Product = e.Product
    AND ChangeDate > DATEADD(HOUR, 1, e.EntryDate)
)
AND e.EntryDate >= @BeginDate
AND e.EntryDate <= @EndDate

我们只需要第二次连接，因为您提到您想要所有“问题”集的整个历史记录；如果您只需要 ID，则可以通过消除中间连接并仅选择 e.Product 来提高此查询的效率。

Looks pretty straightforward, if I'm understanding correctly - just get the entry dates and join with any changes that happened more than a hour later.

SELECT pc.Product, pc.User, pc.ChangeDate, pc.OldValue, pc.NewValue
FROM
(
    SELECT Product, ChangeDate AS EntryDate
    FROM ProductChanges
    WHERE OldValue IS NULL
) e
INNER JOIN ProductChanges pc
    ON pc.Product = e.Product
WHERE EXISTS
(
    SELECT 1
    FROM ProductChanges
    WHERE Product = e.Product
    AND ChangeDate > DATEADD(HOUR, 1, e.EntryDate)
)
AND e.EntryDate >= @BeginDate
AND e.EntryDate <= @EndDate

We only need the second join because you mention you want the entire history for all of the "problem" sets; if you just want the IDs, you can make this query more efficient by eliminating the middle join and just selecting e.Product.

回复收藏 0 原文

沙沙粒小 2024-08-27 09:59:53

select * from pc where product in (select distinct a.product from pc a, pc b
where a.product = b.product and a.changedate > dateadd(hh, 1, b.changedate))
order by product, changedate

select * from pc where product in (select distinct a.product from pc a, pc b
where a.product = b.product and a.changedate > dateadd(hh, 1, b.changedate))
order by product, changedate

回复收藏 0 原文

~没有更多了~