根据我的条件查询最接近的用户

[jlcool]

详细见这里：

http://topic.csdn.net/u/20090501/20/b701b11f-ef8a-44ed-b35f-7e32c71003e4.html

[陌上花花]

UP

[jlcool]

我自己顶

[sxmonsy]

我帮你顶

[pt1314917]

a表字段
年龄 身高 学历

b表字段
年龄1 年龄2 身高1 身高2 学历
--------------------------

select a.* from a left join b
where 年龄 between b.年龄1 and b.年龄2 or 身高 between b.身高1 and b.身高2 or b.学历=a.学历)
order by 
(case when (年龄 between b.年龄1 and b.年龄2) then 1 else 0 end)+
(case when (身高 between b.身高1 and b.身高2) then 1 else 0 end)+
(case when a.学历=b.学历 then 1 else 0 end)
desc

[pt1314917]

--符号错了。
select a.* from a,b
where 年龄 between b.年龄1 and b.年龄2 or 身高 between b.身高1 and b.身高2 or b.学历=a.学历
order by 
(case when (年龄 between b.年龄1 and b.年龄2) then 1 else 0 end)+
(case when (身高 between b.身高1 and b.身高2) then 1 else 0 end)+
(case when a.学历=b.学历 then 1 else 0 end)
desc

[pt1314917]

--> 测试数据: @a
declare @a table (年龄 int,身高 int,学历 varchar(1))
insert into @a
select 12,170,'A' union all
select 11,150,'B' union all
select 10,160,'C'
--> 测试数据: @b
declare @b table (年龄1 int,年龄2 int,身高1 int,身高2 int,学历 varchar(1))
insert into @b
select 12,13,140,145,'B' union all
select 11,13,170,180,'a' union all
select 12,13,170,180,'A'

select a.* from @A a
where exists(select 1 from @b b where (年龄 between b.年龄1 and b.年龄2) or (身高 between b.身高1 and b.身高2) or b.学历=a.学历)
order by 
(case when exists(select 1 from @b b where 年龄 between b.年龄1 and b.年龄2) then 1 else 0 end)+
(case when exists(select 1 from @b b where 身高 between b.身高1 and b.身高2) then 1 else 0 end)+
(case when exists(select 1 from @b b where b.学历=a.学历) then 1 else 0 end)
desc

[yagebu1983]

楼上的方法不错。。。

[ZJ159]

顶顶顶

[gongsun]

B表设计 是什么需求？

如果要划分 年龄、身高的话，年龄、身高就的分类。

[teerhu]

帮顶