bugfix> sql > 投稿

次の表があります。

表: TblLocation

CREATE TABLE TblLocation
(
    ColDate DATE,
    ColTime time,
    Colvalue VARCHAR(50)
);
INSERT INTO TblLocation VALUES('2018-01-01','01:10:11','Location1');
INSERT INTO TblLocation VALUES('2018-01-01','02:10:11','Location2');
INSERT INTO TblLocation VALUES('2018-01-01','03:10:11','Location3');
INSERT INTO TblLocation VALUES('2018-01-01','11:10:11','Location4');
INSERT INTO TblLocation VALUES('2018-01-02','01:10:11','Location1');
INSERT INTO TblLocation VALUES('2018-01-02','02:10:11','Location2');
INSERT INTO TblLocation VALUES('2018-01-02','03:10:11','Location2');
INSERT INTO TblLocation VALUES('2018-01-02','01:10:11','Location3');
INSERT INTO TblLocation VALUES('2018-01-02','03:15:11','Location4');
INSERT INTO TblLocation VALUES('2018-01-03','02:10:11','Location1');
INSERT INTO TblLocation VALUES('2018-01-03','02:50:11','Location1');
INSERT INTO TblLocation VALUES('2018-01-03','03:10:11','Location1');

注意:各日付の最初、中間、および最後の場所を検索したい。

期待される出力

ColDate         FirstLocation   MidLocation     LastLocation
-------------------------------------------------------------
2018-01-01      Location1       Location3       Location4
2018-01-02      Location1       Location2       Location4
2018-01-03      Location1       NULL            Location1

出力の説明

  1. FirstLocation - This should be based on ORDER by date and time and will be FIRST time of that date.

  2. MidLocation - This should NOT be equal to FirstLocation and LastLocation but which is present in between location which is more in count or any in tie scenario. If nothing between then NULL as showed in 3rd row.

  3. LastLocation - This should be based on ORDER by date and time and will be LAST time of that date.

私のトライ

WITH CTEMain AS
(
    SELECT ColDate,ColTime, ColValue, ROW_NUMBER() OVER(PARTITION BY ColDate ORDER BY ColDate,ColTime ASC) Rn 
    FROM dbo.TblLocation
),
CTEMinMax AS
(
    SELECT MIN(Rn) AS MinRn,MAX(Rn) AS MaxRn
    FROM CTEMain
),
CX AS 
(
    SELECT c1.ColDate,c1.Colvalue AS FirstLocation,
       c2.Colvalue AS LastLocation
    FROM CTEMain c1 
    INNER JOIN CTEMinMax cmin ON c1.Rn = cmin.MinRn
    INNER JOIN CTEMain c2 ON 1 = 1
    INNER JOIN CTEMinMax cmax ON c2.Rn = cmax.MaxRn
), 
CXX AS
( 
    SELECT TOP 1 ROW_NUMBER() OVER(PARTITION BY Colvalue ORDER BY Rn DESC) Rnk,ColValue,Rn
    FROM CTEMain c1
    INNER JOIN CX x ON c1.colvalue <> x.FirstLocation AND c1.colvalue <> x.LastLocation
    GROUP BY ColValue,Rn
    ORDER BY Rnk DESC
)
SELECT ColDate,CX.FirstLocation,(SELECT ColValue FROM CXX) AS MidName, cx.LastLocation
FROM CX

出力

ColDate     FirstLocation   MidName     LastLocation
-----------------------------------------------------
2018-01-01  Location1       Location2   Location4
2018-01-02  Location1       Location2   Location4
2018-01-03  Location1       Location2   Location4

回答 2 件
  • 閉じる

    declare @t TABLE (ColDate DATE, ColTime time, Colvalue VARCHAR(50));
    INSERT INTO @t VALUES
    ('2018-01-01','01:10:11','Location1'),
    ('2018-01-01','02:10:11','Location2'),
    ('2018-01-01','03:10:11','Location3'),
    ('2018-01-01','11:10:11','Location4'),
    ('2018-01-02','01:10:11','Location1'),
    ('2018-01-02','02:10:11','Location2'),
    ('2018-01-02','03:10:11','Location2'),
    ('2018-01-02','01:10:11','Location3'),
    ('2018-01-02','03:15:11','Location4'),
    ('2018-01-03','02:10:11','Location1'),
    ('2018-01-03','02:50:11','Location1'),
    ('2018-01-03','03:10:11','Location1');
    --select * from @t order by ColDate, ColTime
    with cte as 
    ( select * 
           , row_number() over (partition by ColDate order by ColTime asc)  as tasc
           , row_number() over (partition by ColDate order by ColTime desc) as tdsc
      from @t 
    ) 
    select distinct
           cteMin.ColDate, cteMin.ColTime
         , cteMin.Colvalue as [cteMin.Colvalue]
         , cteMid.Colvalue as [cteMid.Colvalue]
         , cteMax.Colvalue as [cteMax.Colvalue]
    from cte cteMin 
    join cte cteMax
      on cteMin.ColDate = cteMax.ColDate 
     and cteMin.tasc = 1 
     and cteMax.tdsc = 1
    left join cte cteMid 
      on cteMid.ColDate = cteMin.ColDate 
     and cteMid.tasc > 1
     and cteMid.tdsc = 2
     and cteMid.Colvalue <> cteMin.Colvalue 
     and cteMid.Colvalue <> cteMax.Colvalue
    
    

  • 私は使うだろう相関する  subquery    apply  演算子:

    select distinct t.coldate, t1.*, 
           ( select top 1 ColValue
             from TblLocation
             where (coldate = t.coldate and 
                    ColValue <> t1.FirstLocation and ColValue <> t2.LastLocation)
             order by count(*) over() desc
           ) as MidName, t2.*
    from TblLocation t cross apply 
         (select top 1 ColValue as FirstLocation 
          from TblLocation
          where coldate = t.coldate
          order by coltime
         ) t1 cross apply 
         (select top 1 ColValue as LastLocation
          from TblLocation
          where coldate = t.coldate
          order by coltime desc
         ) t2;
    
    

あなたの答え