次の表があります。
表:
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
出力の説明:
FirstLocation - This should be based on ORDER by date and time and will be FIRST time of that date.
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.
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 件
私は使うだろう相関する
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;
関連記事
- JavaScriptを使用して複数の選択ボックスを動的にロードする
- 複数のリクエストがJavagRPCサーバーの同じスレッドに入ります
- 角度を使用してサーバーにPOSTリクエストを行う
- Nodejsでaxiosを使用した複数のgetリクエスト
- 柔軟性を維持するために、幅のフレーム修飾子を使用せずに、複数列リストの1列を同じ幅にする方法
- RxJs forkJoinを使用して、複数のAPI呼び出しを単一の出力配列にマージします
- ParallelForEachループを使用してファイルをSFTPサーバーにアップロードし、パフォーマンスを向上させる方法
- フォームを使用して複数のレコードをdbに保存しようとしていますが、次のエラーが発生します:配列から文字列への変換
- ステンシルマスキングを使用して複数のオブジェクトをマスクする方法
- BigQueryを使用して各レコードグループの最後の3つのレコードの平均値を取得するにはどうすればよいですか?
閉じる