34,575
社区成员
发帖
与我相关
我的任务
分享
--SQL
select userName,city from tb2 where ISNULL(city, '') <> ''
union all
select b.userName, c.city from
(select userId, userName from tb2 where ISNULL(city, '') = '') b
inner join tb1 a
on a.userID = b.userId
inner join tb3 c
on c.startIp10 - CAST(a.regIp as int) <= 0 and c.endIp10 - CAST(a.regIp as int) >= 0
create table tb1
(
userID varchar(100),
regIp varchar(100)
)
insert tb1
select 'test1', '5'
create table tb2
(
userId varchar(100),
userName varchar(100),
city varchar(100)
)
insert tb2
select 'test1', 'user1', null
create table tb3
(
city varchar(100),
startIp10 int,
endIp10 int
)
insert tb3
select 'xian', 4, 10 union all
select 'guangzhou', 5, 6
--SQL
select
userName,
city = case when (ISNULL(city, '')) <> '' then city else
(select top(1) c.city from tb1 a, tb3 c
where a.userID = b.userID and c.startIp10 - CAST(a.regIp as int) <= 0 and c.endIp10 - CAST(a.regIp as int) >= 0)
end
from tb2 b
select b.username,a.city from tb3 a
left join tb2 b on a.city=b.city
left join tb1 c on b.userid=c.userid
where cast(c.regip as int) between a.startIp10 and a.endIp10
select tb2.username,a.city from tb2 a
left join tb3 b on a.city=b.city
left join tb1 c on b.userid=c.userid
where cast(c.regip as int) between a.startIp10 and a.endIp10