SQL Server 2008 R2：複数のCTEを使用して日付ごとのレコードを取得する

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

sql

sql-server

tsql

回答 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;

あなたの答え

関連記事