我正在嘗試創建一個 SQL 查詢，我可以在其中獲取最新日期每天的記錄數。
我在資料表中有 4 列：
File_Name, Insert_TimeStamp, MobileNumber, Message_Id (unique)
假設在 Insert_TimeStamp 13/12/2021 的檔案中插入了 100 條記錄，根據日期 XYZ_1312021，File_Name 對于所有記錄都相同。就像明智的日常檔案將被插入。
我只是想通過查看此資料表，在 SQL 的幫助下每天創建 1 個新記錄。結果會像
TotalNumberRecords、File_Name、Insert_TimeStamp(latestdate)、Status(received)。
但我收到一個錯誤
保存查詢欄位時出錯。無效的列名“Total_Record_Received”
我創建的 SQL：
Select Top 1
file_name,
INSERT_TIMESTAMP,
Total_Record_Received,
'Received' as Status
from
(
Select
file_name,
INSERT_TIMESTAMP,
Count(*) as Total_Record_Received,
ROW_NUMBER() OVER(PARTITION by file_name ORDER BY INSERT_TIMESTAMP ASC ) as rn
from [Master-SMS MessageALL] a
Group by file_name, INSERT_TIMESTAMP) t
Where t.rn = 1
樣本資料 ：
File_Name Insert_TimeStamp Mobile_Number MessageID
xyz_13122021 13/12/2021 7814154 mm_001
xyz_13122021 13/12/2021 7516546 mm_002
xyz_13122021 13/12/2021 1541646 mm_003
xyz_13122021 13/12/2021 5446561 mm_004
xyz_13122021 13/12/2021 5456456 mm_005
xyz_13122021 13/12/2021 8749849 mm_006
xyz_12122021 12/12/2021 7814154 mm_007
xyz_12122021 12/12/2021 8749849 mm_008
xyz_11122021 11/12/2021 8749848 mm_009
結果 ：
File_Name TotalRecords Insert_TimeStamp Status
xyz_13122021 6 13/12/2021 Received
uj5u.com熱心網友回復：
你需要的是一個簡單的GROUP BY查詢
SELECT File_Name,
COUNT(*) AS TotalRecords,
Insert_TimeStamp,
'Received' AS Status
FROM [Master-SMS MessageALL]
WHERE Insert_TimeStamp = (SELECT MAX(Insert_TimeStamp) FROM [Master-SMS MessageALL])
GROUP BY File_Name, Insert_TimeStamp
或者您可以TOP 1通過Insert_TimeStamp降序使用行順序
SELECT TOP 1
File_Name,
COUNT(*) AS TotalRecords,
Insert_TimeStamp,
'Received' AS Status
FROM [Master-SMS MessageALL]
GROUP BY File_Name, Insert_TimeStamp
ORDER BY Insert_TimeStamp DESC
或者，如果您想使用 row_number()
SELECT *
FROM
(
SELECT File_Name,
COUNT(*) AS TotalRecords,
Insert_TimeStamp,
'Received' AS Status,
RN = ROW_NUMBER() OVER (ORDER BY Insert_TimeStamp DESC)
FROM [Master-SMS MessageALL]
GROUP BY File_Name, Insert_TimeStamp
) AS D
WHERE D.RN = 1
uj5u.com熱心網友回復：
別 Group by Total_Record_Received
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/380114.html
標籤：sql sql-server 查询语句 salesforce-marketing-cloud
上一篇：T-SQL：將int視為作業日