各位大大好,想詢問大家一個SQL查詢語法的應用~
我想在pp資料表中,查詢裡面的function_code(功能碼),reverse_reason(沖轉原因),reason_code(原因碼)欄位有多少組合,並且"只要"顯示這個組合最近一筆的policy_no(保單號碼),tran_date(交易日期),agent_code(業務員ID)就好
我先使用這個語法查詢pp資料表中的內容
select distinct function_code,
reverse_reason,
reason_code,
policy_no,
agent_code,
tran_date
into ##123
from pp
第一段語法查詢出的結果
然後想再針對此查詢結果的"function_code","reverse_reason","reason_code"欄位組合排除重覆的值,並顯示最近一筆tran_date的資料,如果tran_date當天有多筆資料則以最小的agent_code來顯示就好。
預期示意圖如下
CREATE TABLE pp (
function_code NVARCHAR(50),
reverse_reason NVARCHAR(50) NULL,
reason_code NVARCHAR(50) NULL,
policy_no NVARCHAR(50) NULL,
agent_code NVARCHAR(50) NULL,
tran_date NVARCHAR(50) NULL);
INSERT INTO pp (function_code,reverse_reason,reason_code,policy_no,agent_code,tran_date) VALUES
('CA','','UR','*****','X22****875','102/11/01'),
('CA','2','UR','*****','R12****296','112/04/01'),
('CA','','UR','*****','B22****899','106/12/01'),
('CA','','UR','*****','B12****650','102/11/01'),
('CA','','UR','*****','Q22****296','106/12/01'),
('CA','','UR','*****','L12****081','106/12/01'),
('CA','','UG','*****','S22****735','103/10/01'),
('CA','','UG','*****','N22****610','106/12/01'),
('CA','','T4','*****','Q22****171','102/09/01'),
('CC','','T4','TW0*****','F22****895','109/12/31'),
('CC','','T4','TW0*****','B12****269','111/08/31'),
('CC','','T4','TW0*****','H22****456','108/09/30'),
('CC','','T4','TW0*****','L12****267','108/12/31'),
('F1','','20','160*****9897','T12****600','108/03/07'),
('F1','2','20','176*****2974','Q12****583','108/03/07'),
('F1','3','20','171*****0666','T12****286','108/03/07'),
('F1','','10','178*****3835','T22****421','112/07/24'),
('F1','','16','147*****7693','X12****674','103/07/14');
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY function_code,reverse_reason,reason_code
ORDER BY tran_date DESC,agent_code) AS No,*
FROM pp
) AS QQ
WHERE No = 1
建議:提問時先將資料建好,以免每個邦友都要建一 次。