如何在SQL Server中查询此输出
我有一个表，像这样的数据：
CREATE TABLE Test
(CustName varchar(20), Country varchar(20), RecordedTime datetime, CurrNo tinyint);
INSERT INTO Test
(CustName, Country, RecordedTime, CurrNo)
VALUES
('Alex', 'Australia', '2018-06-01 08:00:00', 1),
('Alex', 'China', '2018-06-01 10:00:00', 2),
('Alex', 'India', '2018-06-01 10:05:00', 3),
('Alex', 'Japan', '2018-06-01 11:00:00', 4),
('John', 'Australia', '2018-06-01 08:00:00', 1),
('John', 'China', '2018-06-02 08:00:00', 2),
('Bob', 'Australia', '2018-06-02 09:00:00', 1),
('Bob', 'Brazil', '2018-06-03 09:50:00', 2),
('Bob', 'Africa', '2018-06-03 11:50:00', 3),
('Bob', 'India', '2018-06-03 11:55:00', 4),
('Tim', 'Brazil', '2018-06-10 00:00:00', 2),
('Tim', 'Cuba', '2018-06-11 00:00:00', 3),
('Tim', 'India', '2018-06-11 00:05:00', 4),
('Jerry', 'Cuba', '2018-06-12 00:00:00', 4),
('Jerry', 'Brazil', '2018-06-12 00:05:00', 5),
('Jerry', 'India', '2018-06-12 00:10:00', 7),
('Jerry', 'USA', '2018-06-12 00:15:00', 9)
('Maulik', 'Aus', '2018-06-12 00:00:00',3),
('Maulik', 'Eng', '2018-06-13 00:00:00',4),
('Maulik', 'USA', '2018-06-14 00:00:00',5),
('Maulik', 'Ind', '2018-06-14 00:00:00',6);
表结果：
CustName Country RecordedTime CurrNo
-----------------------------------------------------
Alex Australia 2018-Jun-01 08:00 AM 1
Alex China 2018-Jun-01 10:00 AM 2
Alex India 2018-Jun-01 10:05 AM 3
Alex Japan 2018-Jun-01 11:00 AM 4
John Australia 2018-Jun-01 08:00 AM 1
John China 2018-Jun-02 08:00 AM 2
Bob Australia 2018-Jun-02 09:00 AM 1
Bob Brazil 2018-Jun-03 09:50 AM 2
Bob Africa 2018-Jun-03 11:50 AM 3
Bob India 2018-Jun-03 11:55 AM 4
Tim Brazil 2018-Jun-10 12:00 AM 2
Tim Cuba 2018-Jun-11 12:00 AM 3
Tim India 2018-Jun-11 12:05 AM 4
Jerry Cuba 2018-Jun-12 12:00 AM 4
Jerry Brazil 2018-Jun-12 12:05 AM 5
Jerry India 2018-Jun-12 12:10 AM 7
Jerry USA 2018-Jun-12 12:15 AM 9
Maulik Aus 2018-Jun-12 00:00:AM 3
Maulik Eng 2018-Jun-13 00:00:AM 4
Maulik USA 2018-Jun-14 00:00:AM 5
Maulik Ind 2018-Jun-14 00:00:AM 6
我需要涵盖以下所有方案的输出。
对于“审计”和“历史记录”字段应如何显示值有一条经验法则；
-
记录仅对于 原始帐户 应具有Audit =“ ADD”或“ CHANGE”＆History =“ NEW”，“ BEFORE”或“ CURRENT” （这意味着表中的条目肯定从CurrNo = 1开始）
-
对于这种类型的帐户，记录不应该具有针对 迁移帐户的 Audit =“ ADD”和History =“ NEW” （这意味着表中的条目不是从CurrNo = 1开始，它可能从2或3或任何升序开始）审核应具有“ CHANGE”（更改），而“ History”（历史记录）字段应具有“ BEFORE”或“ CURRENT”
方案1 ：如果给定输入日期为2018年6月1日，则输出应如下所示（即，当同一天多次添加和编辑记录时）
CustName Country RecordedTime Audit History
----------------------------------------------------------------
Alex Australia 2018-Jun-01 08:00 AM ADD NEW
Alex Australia 2018-Jun-01 08:00 AM CHANGE BEFORE
Alex Japan 2018-Jun-01 11:00 AM CHANGE CURRENT
John Australia 2018-Jun-01 08:00 AM ADD NEW
方案2 ：如果给定输入日期为2018年6月2日，则输出应如下所示（即，当前几天已经存在一条记录并且今天编辑了同一条记录并且今天出现了任何新记录时）
CustName Country RecordedTime Audit History
-----------------------------------------------------------------
John Australia 2018-Jun-01 08:00 AM CHANGE BEFORE
John China 2018-Jun-02 08:00 AM CHANGE CURRENT
Bob Australia 2018-Jun-02 09:00 AM ADD NEW
方案3
：如果给定输入日期为2018年6月3日，则输出应如下所示（即，当某记录在同一天被多次编辑时，它应列出最后一个上一个日期的最后一条记录，然后列出当前给定的最后一条记录）日期）
CustName Country RecordedTime Audit History
----------------------------------------------------------------
Bob Australia 2018-Jun-02 09:00 AM CHANGE BEFORE
Bob India 2018-Jun-03 12:55 AM CHANGE CURRENT
方案4 ：如果给定输入日期为2018年6月10日，则输出应如下所示
CustName Country RecordedTime Audit History
----------------------------------------------------------------
Tim Brazil 2018-Jun-10 12:00 AM CHANGE CURRENT
方案5 ：如果给定输入日期为2018年6月11日，则输出应如下所示（即类似于方案2）
CustName Country RecordedTime Audit History
----------------------------------------------------------------
Tim Brazil 2018-Jun-10 12:00 AM CHANGE BEFORE
Tim India 2018-Jun-11 12:05 AM CHANGE CURRENT
方案6 ：如果输入日期为2018年6月12日，则输出应如下所示（即类似于方案3）
CustName Country RecordedTime Audit History
----------------------------------------------------------------
Jerry Cuba 2018-Jun-12 12:00 AM CHANGE BEFORE
Jerry USA 2018-Jun-12 12:15 AM CHANGE CURRENT
Maulik Aus 2018-Jun-12 00:00 AM CHANGE CURRENT
如果输入日期为2018年6月13日，则输出应为以下内容
CustName Country RecordedTime Audit History
----------------------------------------------------------------
Maulik Aus 2018-Jun-12 00:00 AM CHANGE BEFORE
Maulik Eng 2018-Jun-13 00:00 AM CHANGE CURRENT
如果输入日期为2018年6月14日，则输出应为以下内容
CustName Country RecordedTime Audit History
----------------------------------------------------------------
Maulik Eng 2018-Jun-13 00:00 AM CHANGE BEFORE
Maulik Ind 2018-Jun-14 00:00 AM CHANGE CURRENT
下面是我正在使用的当前代码（满足场景2和3，但不满足其余部分）；
declare @d date='2018-Jun-03'
; with Indexer as
(
select
*,
rn= row_number() over(partition by CustName order by RecordedTime),
rn2=row_number() over(partition by CustName order by RecordedTime desc)
from records
)
,GetValidCustomerRecords as
(
select
CustName,
Country,
RecordedTime,
Audit = case when cast(RecordedTime as date)=@d and rn=1 then 'add' else 'change' end,
History = case
when cast(RecordedTime as date)=@d and rn=1
then 'new'
when cast(RecordedTime as date)<@d and rn=1
then 'before'
else 'current' end
from Indexer i
where CustName in
(
select
distinct CustName
from records
where cast(RecordedTime as date)=@d
)
and (rn=1 or rn2=1) and cast(RecordedTime as date)<=@d
)
select * from GetValidCustomerRecords
order by CustName, RecordedTime
任何SQL专家都可以修改此查询来满足所有情况？非常感谢和感谢。
问题答案：
再会，
请检查以下解决方案是否满足您的所有需求。我用您的数据和更多的行对其进行了测试，但始终最好重新检查一次。乍一看，它似乎返回了请求的结果。稍后我会添加一些解释
我正在使用它的查询：
DECLARE @Date DATE = '2018-06-12';
with MyCTE as (
SELECT
t.CustName,t.Country,t.RecordedTime,t.CurrNo, D = CONVERT(DATE, RecordedTime)
,RN_D = ROW_NUMBER()
OVER (partition by t.CustName order by t.CurrNo desc)
,RN = ROW_NUMBER()
OVER (partition by t.CustName order by t.CurrNo)
,RN_Old = ROW_NUMBER()
OVER (partition by t.CustName, (CASE WHEN CONVERT(DATE, RecordedTime) < @Date then 0 else 1 END) order by t.CurrNo desc)
,Cnt = COUNT(*)
OVER (partition by t.CustName)
,CntToday = COUNT(CASE WHEN CONVERT(DATE, RecordedTime) = @Date THEN 1 ELSE NULL END)
OVER (partition by t.CustName)
FROM Test t
where
-- returns rows untill current date
CONVERT (DATE, RecordedTime) <= @Date
-- only if relevnat to current date
and EXISTS (
SELECT * FROM test t0
where CONVERT (DATE, RecordedTime) = @Date and t0.CustName = t.CustName
)
)
,MyCTE2 as (
select
CustName, Country, RecordedTime, D, CurrNo, RN_D, RN, Cnt, t2.c, History, CntToday, RN_Old
from MyCTE t1
left JOIN (select * from (values(1, 'NEW'),(1, 'BEFORE')) t2(c, History) ) t2
on t1.CurrNo = t2.c
and CntToday > 1
and D = @Date
where
RN_D = 1
or (RN = 1 and D = @Date)
or (RN_Old = 1 and D < @Date)
)
,MyCTE3 as (
select CustName, Country, RecordedTime
-- unmarke the bellow comment in order to get the accessories columns I used
-- This is recommended to understand the line-of-thinking
--, D, c, RN_D, RN, CurrNo, Cnt, CntToday, RN_Old
, History = CASE
WHEN CurrNo = 1 and Cnt = 1 then 'NEW'
WHEN RN_D = 1 then 'CURRENT'
else ISNULL(History,'BEFORE')
END
from MyCTE2
)
select CustName, Country, RecordedTime--, D, c, RN_D, RN, CurrNo, Cnt, CntToday, RN_Old
,Audit = CASE when History='New' then 'ADD' else 'CHANGE' END
, History
from MyCTE3
为了简化测试，我将整个查询插入表函数中
DROP FUNCTION IF EXISTS dbo.F
GO
CREATE FUNCTION dbo.F(@Date DATE)
RETURNS TABLE AS RETURN (
--DECLARE @Date DATE = '2018-06-12';
with MyCTE as (
SELECT
t.CustName,t.Country,t.RecordedTime,t.CurrNo, D = CONVERT(DATE, RecordedTime)
,RN_D = ROW_NUMBER()
OVER (partition by t.CustName order by t.CurrNo desc)
,RN = ROW_NUMBER()
OVER (partition by t.CustName order by t.CurrNo)
,RN_Old = ROW_NUMBER()
OVER (partition by t.CustName, (CASE WHEN CONVERT(DATE, RecordedTime) < @Date then 0 else 1 END) order by t.CurrNo desc)
,Cnt = COUNT(*)
OVER (partition by t.CustName)
,CntToday = COUNT(CASE WHEN CONVERT(DATE, RecordedTime) = @Date THEN 1 ELSE NULL END)
OVER (partition by t.CustName)
FROM Test t
where
-- returns rows untill current date
CONVERT (DATE, RecordedTime) <= @Date
-- only if relevnat to current date
and EXISTS (
SELECT * FROM test t0
where CONVERT (DATE, RecordedTime) = @Date and t0.CustName = t.CustName
)
)
,MyCTE2 as (
select
CustName, Country, RecordedTime, D, CurrNo, RN_D, RN, Cnt, t2.c, History, CntToday, RN_Old
from MyCTE t1
left JOIN (select * from (values(1, 'NEW'),(1, 'BEFORE')) t2(c, History) ) t2
on t1.CurrNo = t2.c
and CntToday > 1
and D = @Date
where
RN_D = 1
or (RN = 1 and D = @Date)
or (RN_Old = 1 and D < @Date)
)
,MyCTE3 as (
select CustName, Country, RecordedTime
-- unmarke the bellow comment in order to get the accessories columns I used
-- This is recommended to understand the line-of-thinking
--, D, c, RN_D, RN, CurrNo, Cnt, CntToday, RN_Old
, History = CASE
WHEN CurrNo = 1 and Cnt = 1 then 'NEW'
WHEN RN_D = 1 then 'CURRENT'
else ISNULL(History,'BEFORE')
END
from MyCTE2
)
select CustName, Country, RecordedTime--, D, c, RN_D, RN, CurrNo, Cnt, CntToday, RN_Old
,Audit = CASE when History='New' then 'ADD' else 'CHANGE' END
, History
from MyCTE3
--order by CustName, RecordedTime
)
GO
使用该函数可以更轻松地进行多次测试，但是在生产环境中，您可能需要使用直接查询
-- Test
select * from F('2018-06-01') order by CustName , RecordedTime
select * from F('2018-06-02') order by CustName , RecordedTime
select * from F('2018-06-03') order by CustName , RecordedTime
select * from F('2018-06-10') order by CustName , RecordedTime
select * from F('2018-06-11') order by CustName , RecordedTime
select * from F('2018-06-12') order by CustName , RecordedTime
select * from F('2018-06-13') order by CustName , RecordedTime
select * from F('2018-06-14') order by CustName , RecordedTime
/ **更新于2018-08-19 14:05以色列时间** /
我注意到，为了参与该线程，需要添加一些其他信息很重要。我希望这会有用
笔记！ 在Microsoft SQL Server 2017开发人员版上进行了测试
首先，让我们根据三个查询的执行计划比较资源使用的百分比：（1）我的解决方案，（2）更新第一个解决方案后的maulik
kansara秒解决方案，以及（3）maulik kansara第一个解决方案
该查询扫描表11次！
重要！**
EP不是建议我们选择哪个查询的唯一参数，但这可能是我们应该检查的第一个信息。此外，我们应该检查IO统计信息和时间统计信息，以及更多…信用： 图像是使用sendryone工具拍摄的。有一个免费版本，可以提供大多数DBA所需的内容。我使用的是作为Microsoft
MVP免费获得的完整版本，因此，谢谢;-)
-
问题内容： 我想我正在解决这个问题，但是遇到了另一个障碍，并且不知道如何解决它 此查询有什么问题- 只需获取通用的“您有一个错误”，它指向联接，但我不知道如何解决它。我想要对customer_address_entity进行联接，因为它具有我需要在其中一条select语句中使用的另一个唯一ID。 问题答案： 首先，您的代码如下所示： 很明显，您在FROM之前有JOIN。 其次，您有一个带有unic
-
问题内容： 您能否将我引导到与Oracle中此查询等效的内容： 谢谢 问题答案： 查询从中检索所有行，然后在中检索行数。这样做只是性能优化：它使您不必进行两次查询。 如果性能不是问题，那么Oracle的等效项将是： 如果您可以重写客户端，则可以在一个查询中执行以下两项操作：
-
问题内容： 我真的是编程新手，但我正在研究它。我有一个我不知道如何解决的问题。我在mongoDB中收集了许多文档，并且正在使用Elasticsearch查询字段。问题是我想将搜索的输出存储回mongoDB中，但存储在不同的DB中。我知道我必须创建一个临时数据库，该数据库必须随每个搜索结果进行更新。但是该怎么做呢？或者给我阅读文档，以便我可以学习。我将非常感谢您的帮助！ 问题答案： Mongo本身不
-
问题内容： 今天，我遇到了一个有趣的SQL问题，尽管我想出了一个行之有效的解决方案，但我怀疑这是最佳还是最有效的答案。在这里，我请专家- 帮助我学习一些知识并改善查询条件！RDBMS是SQL Server 2008 R2，查询是SSRS报告的一部分，该报告将针对约100,000行运行。 本质上，我有一个ID列表，该ID可能具有多个与之关联的值，这些值是Yes，No或其他字符串。对于ID x，如果任
-
我使用cakephp2.3，并试图分页查询检索我的所有'工作'在一定的英里半径内。幸运的是，我找到了我认为我需要的查询，但是我在获取正确的数据时遇到了问题。 编辑，使每个“作业”记录都有一个lat和lng字段，另一个用户通过在搜索表单中输入自己的lat和lng值以及英里半径来执行搜索。在下面的代码中，php变量$lat$lng和$miles在我的分页器中是用户输入的搜索参数。 我在这里找到的查询h
-
问题内容： 此子查询可在SQL Server中使用： 如何在SQL Compact中制作？ 谢谢！ 问题答案： 试试这个： [编辑：] 我最初像Tomalak一样拥有一个INNER JOIN，但是我意识到这将排除没有事件的用户，而不是向他们显示0。甚至可能是您想要的，但与您的原始内容不符。