我正在嘗試獲取特定帳戶名稱的 startdate 不同的記錄。我有以下樣本資料。
示例資料腳本如下
create table Meter (AccountNumer varchar(50), MeterNumber varchar(50), StartDate date)
Insert into Meter Values('0142628117','123470203','4/22/2020')
Insert into Meter Values('0142628117','123470205','4/22/2020')
Insert into Meter Values('0160059948','100094717','4/24/2020')
Insert into Meter Values('0160059948','328144931','4/24/2020')
Insert into Meter Values('0500000178767001363445','TCA105238304','10/2/2018')
Insert into Meter Values('0500000178767001363445','TCA130359929','8/12/2019')
Insert into Meter Values('0500071816677001432356','1ND386803501','8/20/2019')
Insert into Meter Values('0500071816677001432356','99D024666064','7/18/2019')
RowNo AccountNumber MeterNumber StartDate
1 0500000178767001363445 TCA105238304 10/2/2018
2 0500000178767001363445 TCA130359929 8/12/2019
3 0160059948 100094717 4/24/2020
4 0160059948 328144931 4/24/2020
5 0500071816677001432356 1ND386803501 8/20/2019
6 0500071816677001432356 99D024666064 7/18/2019
7 0142628117 123470203 4/22/2020
8 0142628117 123470205 4/22/2020
我試圖只獲取帳號（兩行）的開始日期不同的記錄。所以我期望的結果如下表所示。
預期結果
RowNo AccountNumber MeterNumber StartDate
1 0500000178767001363445 TCA105238304 10/2/2018
2 0500000178767001363445 TCA130359929 8/12/2019
5 0500071816677001432356 1ND386803501 8/20/2019
6 0500071816677001432356 99D024666064 7/18/2019
任何幫助將不勝感激。
uj5u.com熱心網友回復：
你可以試試下面的查詢
select ROW_NUMBER() OVER(ORDER BY m.AccountNumer)RowNo , m.AccountNumer,m.MeterNumber,m.StartDate
from meter m
INNER JOIN meter n ON m.AccountNumer=n.AccountNumer
and m.StartDate <> n.StartDate
如果您遇到任何問題或需要任何更改，請告訴我。
uj5u.com熱心網友回復：
您可以為此使用視窗函式。
我們只是將結果磁區AccountNumber并檢查StartDate每個磁區的最大值和最小值是否不同。
SELECT *
FROM (
SELECT *,
MinDate = MIN(m.StartDate) OVER (PARTITION BY m.AccountNumber),
MaxDate = MAX(m.StartDate) OVER (PARTITION BY m.AccountNumber)
FROM Meter m
) m
WHERE MinDate <> MaxDate;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/422594.html
標籤：