| cus_id | 序列號 | 街道 | 城市 |
|---|---|---|---|
| 123 | 4 | 2 主要 | 紐約市 |
| 512 | 2 | 5 榆樹 | 洛杉磯 |
| 512 | 1 | 5 榆樹 | 洛杉磯 |
| 423 | 3 | 7 韋斯 | 巴黎 |
| 512 | 3 | 4 導航 | 洛杉磯 |
我有一個帶有 SeqNum 的表地址，我想選擇地址最低的 seqnum 作為 MainAdress。如果客戶有多個地址，請選擇下一個為 ScondaryAddress。如果街道與前一條街道匹配，則跳過它并移動下一個序列。
如果客戶有多個地址，我的查詢是選擇了錯誤的街道。
;with base AS
(
select SEQNUM,
STREET AS MainAdress,
--ROW_NUMBER() OVER(partition by street ORDER BY SEQNUM ASC) AS ROW,
*
FROM ADDRESS WHERE seqnum =1
)
,ADDRESS AS
(
SELECT
STREET AS SecondaryAdress,
*
FROM BASE WHERE seqnum =2
)
但這不是動態地獲得街道
uj5u.com熱心網友回復：
我認為這是您要求的查詢：
with base as
(
select
ROW_NUMBER() over (partition by cus_id order by min(seqnum)) as rn,
cus_id, min(seqnum) as seqnum, street, city
from Address
group by cus_id, street, city
)
select cus_id, max(MainAddress) MainAddress, max(SecondaryAddress) SecondaryAddress, city from (
select
cus_id,
case when rn = 1 then street end MainAddress,
case when rn = 2 then street end SecondaryAddress,
city
from base
) as a
group by cus_id, city
having max(MainAddress) is not null or max(SecondaryAddress) is not null
order by cus_id
它回傳這個：
| cus_id | 主地址 | 次要地址 | 城市 |
|---|---|---|---|
| 123 | 2 主要 | 紐約市 | |
| 423 | 7 韋斯 | 巴黎 | |
| 512 | 5 榆樹 | 4 導航 | 洛杉磯 |
DBFiddle：https ://dbfiddle.uk/ ? rdbms = sqlserver_2019 & fiddle = b1b7caf84a374f2f387012e08fe49b59
uj5u.com熱心網友回復：
declare @table table (cus_id int, seqnum int, street varchar(10), city varchar (10));
insert into @table
values
(123, 4, '2 Main', 'NYC' ),
(512, 2, '5 Elm' , 'LA' ),
(512, 1, '5 Elm' , 'LA' ),
(423, 3, '7 Wes' , 'Paris' ),
(512, 3, '4 Nav' , 'LA' ),
(512, 4, '6 Lane', 'WA' );
with base as
(
select
cus_id,
seqnum,
main_address = concat(street, ' ', city),
cnt = count(seqnum) over(partition by cus_id),
secondary_address = lead(concat(street, ' ', city), 1) over(partition by cus_id order by seqnum)
from
@table
)
select top 1 with ties
cus_id,
main_address,
secondary_address
from
base
where
cnt = 1 or main_address != secondary_address
order by
row_number() over(partition by cus_id order by seqnum);
SQL小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/351623.html
標籤：sql sql-server 查询语句 公用表表达式
下一篇：SQL：優先考慮MAX計算