mysql 相同姓名但是不同人的排序号

最新推荐文章于 2024-05-21 21:52:19 发布

weixin_30715523

最新推荐文章于 2024-05-21 21:52:19 发布

阅读量380

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/cathyxiao/p/11434866.html

版权

本文介绍了一个复杂的SQL查询案例，该查询使用CASE WHEN语句、find_in_set函数和变量来处理银行账户信息，包括账户名、生日和手机号码的匹配与分组。通过对这些字段的精细操作，实现了数据的有效筛选与整理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select
CASE when @bankAccountName=a.bankAccountName and (
(find_in_set(a.birthday,@allbirthday)>0 and find_in_set(a.mobile,@allmobile)>0)
or (a.birthday='0001-01-01' and find_in_set(a.mobile,@allmobile)>0)
)
then -1 else 0 end as delete_row
,find_in_set(a.birthday,@allbirthday),find_in_set(a.mobile,@allmobile)
,@group_row:=case
when @bankAccountName=a.bankAccountName and find_in_set(a.birthday,@allbirthday)=0 and a.birthday!='0001-01-01' then @group_row+1
when @bankAccountName=a.bankAccountName and (find_in_set(a.birthday,@allbirthday)>0 or (a.birthday='0001-01-01' and find_in_set(a.mobile,@allmobile)=0)) then @group_row
else 1 end as groupRow
,@allbirthday:=case when @bankAccountName=a.bankAccountName then concat(@allbirthday,',',a.birthday) else a.birthday end as allbirthday
,@allmobile:=case when @bankAccountName=a.bankAccountName then concat(@allmobile,',',a.mobile) else a.mobile end as allmobile
,@bankAccountName:=a.bankAccountName as bankAccountName
,a.*
from t11 a ,(select @group_row:=1, @bankAccountName:='',@allbirthday:='',@allmobile:='') as b
ORDER BY a.bankAccountName,a.birthday desc,a.mobile
;