我如何使用 SQL 来编写这个？

发布于 2025-01-16 20:48:14 字数 709 浏览 2 评论 0原文

我需要在sql中编写一段代码，当存在重复的Id_numbers（例如：234566）但Phone_number中的一个值以A开头而另一个值以B开头时，在“Adjustment_name”列中写入“del_row”在这种情况下，它将在“Phone_number”列中的值以“B”开头的行中写入“del_row”。想象一下，我有两个重复的 id_numbers，其中一个的 Phone_number 以 A 开头，另一行以“C”开头。在最后的情况下，我不想写任何东西。

Id_number	Phone_number	adjustment_name
234566	A5258528564
675467	A1147887422
675534	P1554515315
234566	B4141415882	del_row
234566	C5346656665

非常感谢！

原文

I need to write a code in sql that writes "del_row" in the column "Adjustment_name" when there are duplicated Id_numbers (e.g:234566) but just when one of the values in Phone_number start with A and other one start with B and in that case, it will write "del_row" just in the row in which the value in column "Phone_number" starts with "B". Imagine that I have two duplicated id_numbers and in one of them, the Phone_number starts with A and in the other row starts with "C". In this last situation, I don't want to write anything.

Id_number	Phone_number	Adjustment_name
234566	A5258528564
675467	A1147887422
675534	P1554515315
234566	B4141415882	del_row
234566	C5346656665

Many thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

满栀 2025-01-23 20:48:14

一种方法

SELECT t.id_number, t.Phone_number, 
  CASE WHEN a.id_number IS NOT NULL THEN 'del_row' ELSE '' END as Adjustment_name
FROM mytable t
LEFT JOIN 
(SELECT id_number from mytable
 WHERE SUBSTRING(Phone_number FROM 1 FOR 1)='A') a
/* List of IDs that have a phone number starting with A */
ON a.id_number = t.id_number
AND SUBSTRING(t.Phone_number FROM 1 FOR 1)='B'
/* Only check for matching ID with A if this number starts with B */

One approach

SELECT t.id_number, t.Phone_number, 
  CASE WHEN a.id_number IS NOT NULL THEN 'del_row' ELSE '' END as Adjustment_name
FROM mytable t
LEFT JOIN 
(SELECT id_number from mytable
 WHERE SUBSTRING(Phone_number FROM 1 FOR 1)='A') a
/* List of IDs that have a phone number starting with A */
ON a.id_number = t.id_number
AND SUBSTRING(t.Phone_number FROM 1 FOR 1)='B'
/* Only check for matching ID with A if this number starts with B */

回复收藏 0 原文

李白 2025-01-23 20:48:14

一个相当粗略的方法如下
（假设您的手机排名为 Axxx、Bxxx、Cxxx、Dxxx）。如果您的电话编号逻辑不同 - 这与您的要求不太清楚 - 您可以进行相应调整。

create table temp_table_1 as (
  select id_number, phone_number
   , case 
       when dense_rank() over(partition by id_number order by phone_number)>1 
       and phone_number like 'B%'
       then 'del_row'
     end adjustment_name
  from your_table_name
) with data;

drop table your_table_name;
rename table temp_table_1 to your_table_name;

A rather crude approach would be as below
(assuming your phones rank Axxx, Bxxx, Cxxx, Dxxx). If your phone numbering logic is different - which is not very clear from your req - you can adjust accordingly.

create table temp_table_1 as (
  select id_number, phone_number
   , case 
       when dense_rank() over(partition by id_number order by phone_number)>1 
       and phone_number like 'B%'
       then 'del_row'
     end adjustment_name
  from your_table_name
) with data;

drop table your_table_name;
rename table temp_table_1 to your_table_name;

回复收藏 0 原文

~没有更多了~