双向匹配的T-SQL
在一个很普通的晚上
群里的一位大哥提到 类似双向择偶的东东咋做...俺就意淫了一下 ~
例如我在交友 网站上找女人,自已的基本资料符合别人的条件,别人的基本资料也符合自己的条件,恩 下面的T-SQL就这样出来了,抛砖引玉~
if object_id('temp11') is not null drop table temp11
go
if object_id('tempdb..#t1') is not null drop table #t1
go
create table temp11 (name1 varchar(10) ,sex varchar(5),age int,age1 int,salary float,salary1 float)
create table #t1(name1 varchar(50),mate varchar(50))
insert into temp11
select 'Bill.G','男',20,30,100000,0
union select 'Gerrard','男',28,25,5000,1500
union select 'Ronald','男',29,25,5000,0
union select 'eva','女',22,24,2000,5000
union select 'sala','女',24,25,2500,3000
union select 'Ivan','女',23,29,1800,4500
go
select name1 as 姓名,sex as 性别,age as 年龄,age1 as '期望对方年龄(小于)',salary as 月薪,salary1 as 对对方的月薪的期望 from temp11
;with temp_sex as
(select a.name1 as name1,b.name1 mate from temp11 a,temp11 b
where a.name1<>b.name1 and a.sex<>b.sex and a.age1>b.age and a.age<b.age1 and a.salary>b.salary1 and a.salary1<b.salary)
insert into #t1 select * from temp_sex
select b.name1,b.mate,a.[count] from
(select name1,count(*) as [count] from #t1 group by name1) a,
(SELECT *
FROM(
SELECT DISTINCT
name1
FROM #t1
)A
OUTER APPLY(
SELECT
mate= STUFF(REPLACE(REPLACE(
(
SELECT mate FROM #t1 N
WHERE name1 = A.name1
FOR XML AUTO
), '<N mate="', ','), '"/>', ''), 1, 1, '')
)N
) b
where a.name1=b.name1
go
if object_id('tempdb..#t1') is not null drop table #t1
go
create table temp11 (name1 varchar(10) ,sex varchar(5),age int,age1 int,salary float,salary1 float)
create table #t1(name1 varchar(50),mate varchar(50))
insert into temp11
select 'Bill.G','男',20,30,100000,0
union select 'Gerrard','男',28,25,5000,1500
union select 'Ronald','男',29,25,5000,0
union select 'eva','女',22,24,2000,5000
union select 'sala','女',24,25,2500,3000
union select 'Ivan','女',23,29,1800,4500
go
select name1 as 姓名,sex as 性别,age as 年龄,age1 as '期望对方年龄(小于)',salary as 月薪,salary1 as 对对方的月薪的期望 from temp11
;with temp_sex as
(select a.name1 as name1,b.name1 mate from temp11 a,temp11 b
where a.name1<>b.name1 and a.sex<>b.sex and a.age1>b.age and a.age<b.age1 and a.salary>b.salary1 and a.salary1<b.salary)
insert into #t1 select * from temp_sex
select b.name1,b.mate,a.[count] from
(select name1,count(*) as [count] from #t1 group by name1) a,
(SELECT *
FROM(
SELECT DISTINCT
name1
FROM #t1
)A
OUTER APPLY(
SELECT
mate= STUFF(REPLACE(REPLACE(
(
SELECT mate FROM #t1 N
WHERE name1 = A.name1
FOR XML AUTO
), '<N mate="', ','), '"/>', ''), 1, 1, '')
)N
) b
where a.name1=b.name1
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步