問題描述
我需要為標題道歉.我想了很多，但沒有走得太遠.
我有一張看起來像這樣的表格:
+--------------------------------------+--------------------------------------+--------------------------------------+------------------------------------+--------+|帳號|價格xxxxxid |帳號|價格水平|計數 |+--------------------------------------+-----------------------------------------------------+-------------------------------------+--------------------------------------+------+|36B077D4-E765-4C70-BE18-2ECA871420D3 |00000000-0000-0000-0000-000000000000 |36B077D4-E765-4C70-BE18-2ECA871420D3 |F43C47CE-28C6-42E2-8399-92C58ED4BA9D |1 ||EBC18CBC-2D2E-44CB-B36A-0ADE9E2BDE9F |00000000-0000-0000-0000-000000000000 |EBC18CBC-2D2E-44CB-B36A-0ADE9E2BDE9F |3BEEA9D3-F26B-47E4-88FA-A2AA366980ED |1 ||8DC8D0FC-3138-425A-A922-2F0CAC57E887 |00000000-0000-0000-0000-000000000000 |8DC8D0FC-3138-425A-A922-2F0CAC57E887 |F1B8AD5D-B008-4C3F-94A0-AD3F90C777D7 |1 ||8F908A92-1327-4655-BAE4-C890D971A554 |00000000-0000-0000-0000-000000000000 |8F908A92-1327-4655-BAE4-C890D971A554 |2E0EC67E-5F8F-4305-932E-BBF8DF83DBEC |1 ||37221AAC-B885-4002-B7D9-591F8C14D019 |00000000-0000-0000-0000-000000000000 |37221AAC-B885-4002-B7D9-591F8C14D019 |F4A2A0CA-FDFF-4C21-AE92-D4583DC18DED |1 ||66F406B4-0D9B-40B8-9A23-119EE74B00B7 |00000000-0000-0000-0000-000000000000 |66F406B4-0D9B-40B8-9A23-119EE74B00B7 |204B8570-CEBA-4C72-9B72-8B9B14AF625E |2 ||D0168CE3-479E-439E-967C-4FF0D701291A |00000000-0000-0000-0000-000000000000 |D0168CE3-479E-439E-967C-4FF0D701291A |204B8570-CEBA-4C72-9B72-8B9B14AF625E |2 ||57E5F6E5-0A8A-4E54-B793-2F6493DC1EA3 |00000000-0000-0000-0000-000000000000 |57E5F6E5-0A8A-4E54-B793-2F6493DC1EA3 |893F9FD2-43C9-4355-AEFC-08A62BF2B066 |3 |+--------------------------------------+-----------------------------------------------------+-----------------------------+--------------------------------------+------+按計數升序排序.
我想用相應的 pricelevelid 更新所有 00000000-0000-0000-0000-000000000000 的 pricexxxxid.
例如 accountid = 36B077D4-E765-4C70-BE18-2ECA871420D3 我希望 pricexxxxid 為 F43C47CE-28C6-42E2-8399-92C58ED4BA9D.>
完成后，我希望刪除 accountid = 36B077D4-E765-4C70-BE18-2ECA871420D3 之后的所有記錄.
換句話說，我最終會得到一個不同的 accountid 列表，其中 pricexxxxid 將被分配到 pricelevelid 中的相應值.
非常感謝您的指導.
對于你的第一個案例！
更新表設置 pricexxxids=pricelevelid.如果我正確理解你的第二種情況:(刪除重復項/選擇不同的)?
從(select *,rn=row_number()over(partition by accountid order by accountid) from table)X其中 rn＞1--從表中選擇不同的*已編輯
select * from(select *,rn=row_number()over(partition by accountid order by accountid) from table)X其中 x.rn=1更新
SELECT accountid,pricelevelid FROM((選擇 *，Row_number() OVER ( partition BY accountid ORDER BY counts, pricelevelid ) AS Recency發件人表)X哪里 x.Recency = 1I need to apologize for the title. I put a lot of thought into it but didn't get too far.
I have a table that looks like this:
+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+--------+
| accountid | pricexxxxxid | accountid | pricelevelid | counts |
+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+--------+
| 36B077D4-E765-4C70-BE18-2ECA871420D3 | 00000000-0000-0000-0000-000000000000 | 36B077D4-E765-4C70-BE18-2ECA871420D3 | F43C47CE-28C6-42E2-8399-92C58ED4BA9D | 1 |
| EBC18CBC-2D2E-44CB-B36A-0ADE9E2BDE9F | 00000000-0000-0000-0000-000000000000 | EBC18CBC-2D2E-44CB-B36A-0ADE9E2BDE9F | 3BEEA9D3-F26B-47E4-88FA-A2AA366980ED | 1 |
| 8DC8D0FC-3138-425A-A922-2F0CAC57E887 | 00000000-0000-0000-0000-000000000000 | 8DC8D0FC-3138-425A-A922-2F0CAC57E887 | F1B8AD5D-B008-4C3F-94A0-AD3F90C777D7 | 1 |
| 8F908A92-1327-4655-BAE4-C890D971A554 | 00000000-0000-0000-0000-000000000000 | 8F908A92-1327-4655-BAE4-C890D971A554 | 2E0EC67E-5F8F-4305-932E-BBF8DF83DBEC | 1 |
| 37221AAC-B885-4002-B7D9-591F8C14D019 | 00000000-0000-0000-0000-000000000000 | 37221AAC-B885-4002-B7D9-591F8C14D019 | F4A2A0CA-FDFF-4C21-AE92-D4583DC18DED | 1 |
| 66F406B4-0D9B-40B8-9A23-119EE74B00B7 | 00000000-0000-0000-0000-000000000000 | 66F406B4-0D9B-40B8-9A23-119EE74B00B7 | 204B8570-CEBA-4C72-9B72-8B9B14AF625E | 2 |
| D0168CE3-479E-439E-967C-4FF0D701291A | 00000000-0000-0000-0000-000000000000 | D0168CE3-479E-439E-967C-4FF0D701291A | 204B8570-CEBA-4C72-9B72-8B9B14AF625E | 2 |
| 57E5F6E5-0A8A-4E54-B793-2F6493DC1EA3 | 00000000-0000-0000-0000-000000000000 | 57E5F6E5-0A8A-4E54-B793-2F6493DC1EA3 | 893F9FD2-43C9-4355-AEFC-08A62BF2B066 | 3 |
+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+--------+
It is sorted by ascending counts.
I would like to update the pricexxxxids that are all 00000000-0000-0000-0000-000000000000 with their corresponding pricelevelid.
For example for accountid = 36B077D4-E765-4C70-BE18-2ECA871420D3 I would like the pricexxxxid to be F43C47CE-28C6-42E2-8399-92C58ED4BA9D.
After that is done, I would like all the records FOLLOWING this one where accountid = 36B077D4-E765-4C70-BE18-2ECA871420D3 to be deleted.
Another words in result I will end up with a distinct list of accountids with pricexxxxid to be assigned with the corresponding value from pricelevelid.
Thank you so much for your guidance.
for your first case do !
update table
set pricexxxxids=pricelevelid.
if i understand your second case correctly :(delete duplicates/select distinct)?
delete from
(
select *,rn=row_number()over(partition by accountid order by accountid) from table
)x
where rn>1
--select distinct * from table
edited
select * from
(
select *,rn=row_number()over(partition by accountid order by accountid) from table
)x
where x.rn=1
updated
SELECT accountid,pricelevelid FROM
(
(SELECT *,
Row_number() OVER ( partition BY accountid ORDER BY counts, pricelevelid ) AS Recency
FROM table
)x
WHERE x.Recency = 1
這篇關于我是否需要遞歸 CTE 來更新依賴于自身的表?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！