文章/答案/技术大牛

发布

社区首页 >问答首页 >如何在SQL server中查询此输出

问如何在SQL server中查询此输出
EN

Stack Overflow用户

提问于 2018-08-14 10:31:18

回答 4查看 340关注 0票数 2

我有一张桌子上有这样的数据：

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);

表1.结果：

 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

我需要输出，它应该涵盖以下所有场景。

对于“审计”和“历史”字段应该如何呈现价值，有一条经验法则；

记录应该只有原始Accts (即表中的条目肯定从CurrNo =1开始)的审核=“添加”或“更改”& History = "NEW“、”“或"CURRENT”。
对于这种类型的帐户，记录不应该有迁移的Accts (这意味着表中的条目不是从CurrNo = 1开始，它可以从2或3或任何升序数字开始)的审核=“添加”& History = "NEW“(这意味着表中的条目不是从CurrNo=1开始)--审计应该具有”更改“，而历史字段应该有”前“或”当前“。

Scenario1：如果将输入日期设为2018-Jun-01，则输出应如下所示(即在同一天添加和编辑记录多次时)

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

Scenario2：如果将输入日期设为2018-6月-02，则输出应如下(即前几天已经有记录，今天编辑了相同的记录，而今天有任何新的记录)

   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

Scenario3：如果输入日期为2018-6月-03，则输出应如下(即，当记录在同一天进行多次编辑时，则应列出最新日期的最后记录，然后列出当前给定日期的最后记录)

   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

Scenario4：如果输入日期为2018-Jun-10，则输出应如下所示

   CustName    Country    RecordedTime           Audit    History
  ----------------------------------------------------------------
   Tim         Brazil     2018-Jun-10 12:00 AM    CHANGE   CURRENT

Scenario5：如果将输入日期定为2018-Jun-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

Scenario6：如果输入日期为2018-Jun-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专家都可以修改此查询以满足所有方案？非常感谢和感谢。

注:这里有类似的问题，仅供参考- 如何基于下面的示例从Server检索数据？

sql-server-2012

sql

sql-server

sql-server-2008

tsql

回答 4

Stack Overflow用户

回答已采纳

发布于 2018-08-18 15:16:38

日安，

请检查以下解决方案是否解决了您的所有需求。我用您的数据和更多的行测试了它，但是最好还是重新检查一下。乍一看，它似乎返回了所请求的结果。我稍后会补充一些解释。

我正在使用的查询如下：

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 2017开发版上进行了测试

首先，根据三个查询的执行计划比较资源使用的百分比：(1)我的解决方案，(2)更新第一个解决方案后的maulik kansara解决方案，(3) maulik kansara第一个解决方案

现在让我们检查一下maulik kansara秒解决方案的EP图像：

此查询扫描表11次！

** 重要！ EP不是建议我们应该选择哪个查询的唯一参数，但这可能是我们应该检查的第一个信息。此外，我们应该检查IO统计和时间统计，以及更多. 信用：图像是用sentryone工具拍摄的。有一个免费版本，可以满足DBA所需的大部分内容。我正在使用作为微软MVP免费获得的完整版本，所以谢谢;-)

票数 1

Stack Overflow用户

发布于 2018-08-16 08:37:09

根据我的理解，下面是所需输出的逻辑

步骤1

为每个CurrNo获取给定日期的最大值( custName )。
这里是cte。

步骤2

首先输入上述日期的记录，如果有的话，所有的CustNames都可用。
在上述记录的合并中，如果有任何CustNames中可用的记录，请为低于当前日期编辑的记录获取最后编辑的记录。
这里是cte2。

步骤3

从# currNo=maximun或currno=1的测试表中获取记录，在此步骤中，您将得到最后更新的记录和任何新添加的记录(如果有特定日期的话)。
这里是cte和cte2之后的第一个查询。

步骤4

获取给定日期的第一次添加记录(如果有的话)，其中审计为更改，历史记录为以前。
这是定制的特殊情况要求。
这里是第二个查询。

步骤5

从cte2获取第一条记录，以便为给定日期以前编辑的记录获取一条记录。
这里是第三个查询。

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 00: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);

select * from #Test

   declare @selectedDate date='2018-06-14';
with cte as
(
select CustName,max(CurrNo) maxno,count(1) cnt
from #Test where datediff(day,RecordedTime,@selectedDate)=0 
group by CustName
),cte2 as (
select top 1 t.*,cnt
 from #Test t join cte c on c.CustName =t.CustName
where datediff(day,RecordedTime,@selectedDate)>0 and t.CurrNo!=c.maxno
order by t.RecordedTime  desc
union select top 1 t.*,cnt
 from #Test t join cte c on c.CustName =t.CustName
where datediff(day,RecordedTime,@selectedDate)=0 and t.CurrNo!=c.maxno
order by t.RecordedTime 
)
select t.CustName,t.Country,t.RecordedTime,cnt
,case when t.CurrNo=1 then 'ADD' else 'CHANGE' End as Audit
,case when t.CurrNo=1 then 'NEW' when t.CurrNo=c.maxno then 'CURRENT' else 'BEFORE' end History
 from #Test t join cte c on c.CustName =t.CustName
where datediff(day,RecordedTime,@selectedDate)=0 and (t.CurrNo=c.maxno or t.CurrNo=1)
union 
select top 1 t.CustName,t.Country,t.RecordedTime,cnt
,'CHANGE' Audit
,'BEFORE' History
 from #Test t join cte c on c.CustName =t.CustName
where datediff(day,RecordedTime,@selectedDate)=0 and t.CurrNo=1 and c.cnt>1
union 
select top 1 t.CustName,t.Country,t.RecordedTime,cnt
,'CHANGE' Audit
,'BEFORE' History
 from cte2 t order by RecordedTime

drop table #Test

“罗宁·阿里利”中有一个很好的观点。下面是更新的查询，使用2次表扫描，而不是8次。

with cte as
(
select CustName,max(CurrNo) maxno,count(1) cnt
from #Test where datediff(day,RecordedTime,@selectedDate)=0 
group by CustName
),cte2 as (
select * from #Test where CustName in 
(
select distinct custname from cte
)
and datediff(day,RecordedTime,@selectedDate)>=0
),cte3 as (
select top 1 t.*,cnt
 from cte2 t join cte c on c.CustName =t.CustName
where datediff(day,RecordedTime,@selectedDate)>0 and t.CurrNo!=c.maxno
order by t.RecordedTime  desc
union select top 1 t.*,cnt
 from cte2 t join cte c on c.CustName =t.CustName
where datediff(day,RecordedTime,@selectedDate)=0 and t.CurrNo!=c.maxno
order by t.RecordedTime 
)
select t.CustName,t.Country,t.RecordedTime
,case when t.CurrNo=1 then 'ADD' else 'CHANGE' End as Audit
,case when t.CurrNo=1 then 'NEW' when t.CurrNo=c.maxno then 'CURRENT' else 'BEFORE' end History
 from cte2 t join cte c on c.CustName =t.CustName
where datediff(day,RecordedTime,@selectedDate)=0 and (t.CurrNo=c.maxno or t.CurrNo=1)
union 
select top 1 t.CustName,t.Country,t.RecordedTime
,'CHANGE' Audit
,'BEFORE' History
 from cte2 t join cte c on c.CustName =t.CustName
where datediff(day,RecordedTime,@selectedDate)=0 and t.CurrNo=1 and c.cnt>1
union 
select top 1 t.CustName,t.Country,t.RecordedTime
,'CHANGE' Audit
,'BEFORE' History
 from cte3 t order by RecordedTime

票数 1

Stack Overflow用户

发布于 2018-08-16 09:21:08

根据我从不同场景中了解到的情况，它们基本上只能通过两个规则来实现。

规则1: CustName和RecordedTime的记录都在同一天。
规则2: CustName与RecordedTime的记录超过一天。

在外部查询中，有几个窗口函数和一个OR，我们可以有两个规则来针对所有场景。

然后用CASE WHEN计算“审计”和“历史”。

 SELECT
  CustName, Country, 
  FORMAT(RecordedTime, 'yyyy-MMM-dd hh:mm tt') as RecordedTime,
  (CASE CurrNo 
   WHEN 1 then 'ADD'
   ELSE 'CHANGE'
   END) as [Audit],
  (CASE  
   WHEN CurrNo = 1 then 'NEW'
   WHEN ReverseCurrRN = 1 then 'CURRENT'
   ELSE 'BEFORE'
   END) as [History]
   --, CurrNo, ReverseCurrRN, MinCurrNoPerCust, MaxCurrNoPerCustDate ,MinCurrNoPerCust, MinDatePerCust, MaxDatePerCust
 FROM
 (
    SELECT CustName, Country, RecordedTime, CurrNo
      ,ROW_NUMBER() OVER (PARTITION BY CustName ORDER BY CurrNo DESC) AS ReverseCurrRN
      ,MIN(CurrNo) OVER (PARTITION BY CustName) AS MinCurrNoPerCust
      ,MAX(CurrNo) OVER (PARTITION BY CustName, cast(RecordedTime AS DATE)) AS MaxCurrNoPerCustDate
      ,CAST(MIN(RecordedTime) OVER (PARTITION BY CustName) AS DATE) AS MinDatePerCust
      ,CAST(MAX(RecordedTime) OVER (PARTITION BY CustName) AS DATE) AS MaxDatePerCust
    FROM Test t
    WHERE CAST(RecordedTime AS DATE) between DATEADD(day,-1,@Date) and @Date
 ) q
 WHERE MaxDatePerCust = @Date
 AND (
    -- Scenario 1 & 2 & 4 & 6
       (MinDatePerCust = MaxDatePerCust AND (ReverseCurrRN = 1 OR CurrNo = MinCurrNoPerCust OR (MinCurrNoPerCust = 1 AND ReverseCurrRN = 2)))
    -- Scenario 2 & 3 & 5 & 7 & 8
    OR (MinDatePerCust != MaxDatePerCust AND (ReverseCurrRN = 1 OR CurrNo = 1 OR CurrNo = MaxCurrNoPerCustDate))
 )
 ORDER BY MinDatePerCust, CustName, CurrNo;

您可以在db<>fiddle 这里上测试它。

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/51839355

复制

相似问题

问如何在SQL server中查询此输出
EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL server中查询此输出EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL server中查询此输出
EN