如何在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-  (Alex','India','2018-06-01 10:05:00，3)Alex','Japan','2018-06-01 1: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 1:50:00，3)Bob','India','2018-06-03 1:55:00，4)Tim','Brazil','2018-06-  (Tim','Cuba','2018-06-11 00:00:00，3)Tim','India','2018-06-  (Jerry','Cuba','2018-06-  (Jerry','Brazil','2018-06-12 00Jerry','India','2018-06-  (Jerry','USA','2018-06-12 00:15:00，9)  Maulik','Aus','2018-06-  ('')Maulik','Eng','2018-06-  (Maulik','USA','2018-06-  (Maulik','Ind','2018-06-6)；表结果：
0 M* E/ m) R5 i0 ^    CustName    Country    RecordedTime           CurrNo -----------------------------------------------------  Alex        Australia  2018-Jun-01 08:00 AM    1  Alex        China   20188年                20188年-Jun-01 10:00 AM    2  Alex        India   20188年                20188年-Jun-01 10:05 AM   3  Alex        Japan   20188年                20188年-Jun-01 11:00 AM   4  John        Australia  2018-Jun-01 08:00 AM    1  John        China   20188年                20188年-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   20188年                20188年-Jun-03 11:55 AM   4  Tim         Brazil     2018-Jun-10 12:00 AM    2  Tim         Cuba    20188年                20188年-Jun-11 12:00 AM   3  Tim         India   20188年                20188年-Jun-11 12:05 AM   4  Jerry       Cuba    20188年                20188年-Jun-12 12:00 AM   4  Jerry       Brazil     2018-Jun-12 12:05 AM   5  Jerry       India   20188年                20188年-Jun-12 12:10 AM    7  Jerry       USA     20188年                20188年-Jun-12 12:15 AM    9  Maulik      Aus     20188年                20188年-Jun-12 00:00:AM   3  Maulik      Eng     20188年                20188年-Jun-13 00:00:AM   4  Maulik      USA     20188年                20188年-Jun-14 00:00:AM   5  Maulik      Ind     20188年                20188年-Jun-14 00:00:AM    6我需要涵盖以下所有方案的输出。
1 x+ _4 Y+ @% B- p; b如何显示审计和历史记录字段记录字段的经验法则；
[ol]记录仅针对 原始帐户    应具有Audit =“ ADD”或“ CHANGE”＆History =“ NEW”，“ BEFORE”或“ CURRENT (这意味着表中的条目必须从CurrNo = 1开始）
对于这类账户，记录不应针对 迁移帐户的    Audit =“ ADD”和History =“ NEW (这意味着表中的条目不是从CurrNo = 1开始，它可以从2或3或任何升序开始)审计应该是 CHANGE而 History BEFORE”或“ CURRENT”
1 l; b2 O3 [) G& ~% `: @$ h5 G[/ol]方案1    :如果给定输入日期为2018年6月1日，则输出应如下(即当同一天多次添加和编辑记录时)
! N. S4 F; p0 t3 @" H& wCustName    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   20188年                20188年-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   20188年                20188年-Jun-02 08:00 AM   CHANGE   CURRENT   Bob         Australia  2018-Jun-02 09:00 AM   ADD      NEW方案3
：如果给定输入日期为2018年6月3日，则输出应如下（即当记录在同一天多次编辑时，应列出最后一个日期的最后一个记录，然后列出当前给定的最后一个记录）
, i5 j8 ?( Y0 h) w9 \2 T# K            CustName    Country    RecordedTime           Audit    History  ----------------------------------------------------------------   Bob         Australia  2018-Jun-02 09:00 AM   CHANGE   BEFORE   Bob         India   20188年                20188年-Jun-03 12:55 AM   CHANGE   CURRENT方案4    :如果给定输入日期为2018年6月10日，则输出应如下所示
" @- \! h) N) I2 _7 C# k0 j1 c8 |: N            CustName    Country    RecordedTime           Audit    History  ----------------------------------------------------------------   Tim         Brazil     2018-Jun-10 12:00 AM    CHANGE   CURRENT方案5    :如果给定输入日期为2018年6月11日，则输出应如下(类似方案2)
8 u; b- ~; |$ W+ |1 P            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        20188年 201888年                                                                2018820188-Jun-12 12:00 AM    CHANGE   BEFORE    Jerry       USA                                20188年  20188-Jun-12 12:15 AM    CHANGE   CURRENT    Maulik      Aus            20188年  20188-Jun-12 00:00 AM    CHANGE   CURRENT如果输入日期为2018年6月13日，则输出应为以下内容
( j" N) c2 w2 v$ j            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            20188年  20188-Jun-13 00:00 AM    CHANGE   BEFORE    Maulik      Ind            20188年  20188-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,    RecordedTimeRecordedTime任何SQL专家可以修改此查询以满足所有情况？非常感谢。
                                                               
6 V. ]3 f/ t; J/ Z9 \  Y' U    解决方案:                                                               
0 T3 c0 W9 i1 q- R6 M7 [                                                                再会，
, c; W4 M+ W4 \- E! U8 F! N请检查以下解决方案是否满足您的所有需求。我用你的数据和更多的行来测试它，但最好重新检查它。乍一看，它似乎回到了要求的结果。稍后我会添加一些解释
! k' F2 l2 R$ V5 X/ K, S' I我用它的查询:
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)             and D = @Date    where         RN_D =              or (RN = 1 and D = @Date)         or (RN_Old = 1 and D 为了简化测试，我将整个查询插入表函数
DROP FUNCTION IF EXISTS dbo.FGOCREATE 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) RRecordedTime)             and D = @Date    where         RN_D =              or (RN = 1 and D = @Date)         or (RN_Old = 1 and D 使用此函数可以更容易地进行多次测试，但在生产环境中，您可能需要使用直接查询
& M- l, U6 M% r: G) @-- Testselect * from F('2018-06-01') order by CustName ,RecordedTimeselect * from F('2018-06-02') order by CustName ,RecordedTimeselect * from F('2018-06-03') order by CustName ,RecordedTimeselect * from F('2018-06-10') order by CustName ,RecordedTimeselect * from F('2018-06-11') order by CustName ,RecordedTimeselect * from F('2018-06-12') order by CustName ,RecordedTimeselect * from F('2018-06-13') order by CustName ,RecordedTimeselect * from F('2018-06-14') order by CustName ,RecordedTime/ **更新于2018-08-19 14:05色列时间**    /
我注意到添加一些其他信息是很重要的，以参与这个线程。我希望它能有用
笔记！    在Microsoft SQL Server 2017年开发人员版测试
6 q# @7 \$ U* r6 ?+ A4 e首先，让我们根据三个查询实施计划比较资源使用的百分比：（1）我的解决方案，（2）更新第一个解决方案后maulik
kansara秒解决方案和(3)maulik kansara第一个解决方案
查询扫描表11次！
; }' A. U0 G( R( }* r( t$ j1 o2 O    重要！**
EP我们不建议选择查询的唯一参数，但这可能是我们应该检查的第一个信息。此外，我们应该检查它IO以及更多的统计信息和时间统计信息…
$ b6 b# {4 q) ^# |4 k: Y8 z0 Q信用：    使用图像sendryone工具拍摄。有一个免费版本，可以提供大部分DBA所需内容。我用它作为Microsoft
MVP完整版本的免费获获得的完整版本；-)