表1:userId regIp
表2:userId userName city
表3:city startIp10 endIp10 (将regIp转成数字能与startIp10和endIp10比较,若在范围中,得出城市)现在需求是:要查询出一个多行多列的结果集,如下形式:
userName city
数据 数据
数据 数据
数据 数据
数据 数据若表2中city字段不为空,查出其值,若为空,则关联表1,表3查出city这个怎么弄啊。,。忘赐教。。
表2:userId userName city
表3:city startIp10 endIp10 (将regIp转成数字能与startIp10和endIp10比较,若在范围中,得出城市)现在需求是:要查询出一个多行多列的结果集,如下形式:
userName city
数据 数据
数据 数据
数据 数据
数据 数据若表2中city字段不为空,查出其值,若为空,则关联表1,表3查出city这个怎么弄啊。,。忘赐教。。
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
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
(
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', nullcreate 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 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
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