问一个数据库设计\查询的典型问题。

现在有一个表，假设为Users表，还有一个Location表。

Users存储用户的基本信息，Location表存储用户的位置。假设Location表有4个字段，LocationID , Province,City,Area。

其中Users里有个locationID的属性。和Location表里的LcaotionID是主外键关联。

  
但是呢 现在还有一些users可能是国外的或者因为其他原因，并不分省-市-区,也就是LocationID为空，

所以在Users里新加一个字段为Location. 存储用户自定义的位置。

换句话说，Location和LocationID  只能有一个并且 必须有一个为空值null.


那么，现在搜索所有用户的时候就有个问题，假如我们让Users表和Location表Inner Join起来的话，就会漏掉那些LocationID为空，但是Location不为空的用户。 而且我们想达到的目的就是 如果LocationID不为空，就直接在Lcaotion表里搜索出来它的省-市-区,然后用"xx-yy-zz"这样的形式在GridView表格中显示，如果LocationID为空，则直接显示Location.这样的SQL该如何写，而且高效呢？

33 个解决方案

#1

坐等高手。

#2

其中Users里有个locationID的属性。和Location表里的LcaotionID是主外键关联。

但是呢 现在还有一些users可能是国外的或者因为其他原因，并不分省-市-区,也就是LocationID为空，


你这意思是指如果是国外的，那直接把位置信息插入到user表中的location字段里吗？

#3

引用 2 楼 tashiwoweiyi 的回复:

你这意思是指如果是国外的，那直接把位置信息插入到user表中的location字段里吗？

是的， 用户可以选择 省-市-区 这样的下拉列表，也可以自己输入自己的位置。

#4

到时候如何得到所有的用户列表（不想分两次分开查询）

#5

10万大型项目？

#6

引用 5 楼 karascanvas 的回复:

10万大型项目？

我晕，你别老讽刺哥行不，咱们讨论点实际问题。

#7

啥都不用改，如果是国内的位置，保存前先把省市区查出来，存到Location字段，连表查询都省了。

#8

引用 3 楼 indusl 的回复:

引用 2 楼 tashiwoweiyi 的回复:


你这意思是指如果是国外的，那直接把位置信息插入到user表中的location字段里吗？


是的， 用户可以选择 省-市-区 这样的下拉列表，也可以自己输入自己的位置。

那好，我写个例子给你。

#9

引用 7 楼 karascanvas 的回复:

啥都不用改，如果是国内的位置，保存前先把省市区查出来，存到Location字段，连表查询都省了。

这个方法想过，但是有个一点麻烦就是假如将来省市区要更改了，还要修改这里面的。还有这只是一个例子。实际的项目中求必须填写locationID

#10

create table Location
(
	LocationID int primary key,
	City varchar(100) ,
	Area varchar(100) 
)
go
insert into Location  values (1001,'广东','广州')
insert into Location  values (1002,'广东','深圳')
insert into Location  values (1003,'浙江','杭州')

go

create table Users
(
	userid int primary key ,
	username varchar(100),
	LocationID int ,
	Location varchar(300)
)
go

insert into Users (userid,username,LocationID)  values (1001,'张三',1001)
insert into Users (userid,username,LocationID)  values (1002,'李四',1002)
insert into Users (userid,username,LocationID)  values (1003,'王五',1003)

insert into Users (userid,username,Location)  values (1004,'李七','加拿大')
insert into Users (userid,username,Location)  values (1005,'王八','美国')
go

select * from users
select * from Location


go
----------------------要查询的结果---------------------
select a.userid,a.username,(b.city+'-'+b.area) as Location  
from Users a,Location b where
a.LocationID = b.LocationID
union all
select a.userid,a.username , a.Location from users a where isnull(LocationID,'')=''

#11

自定义位置时，LcaotionID 设置未-1，Lcaotion存自定义位置。
程序里救判断LcaotionID是否-1，就可以判断是否自定义位置了。

#12

引用 11 楼 jy02349551 的回复:

自定义位置时，LcaotionID 设置未-1，Lcaotion存自定义位置。
程序里救判断LcaotionID是否-1，就可以判断是否自定义位置了。

关键是如何显示出所有的用户列表。

#13

引用 10 楼 tashiwoweiyi 的回复:

SQL code



create table Location
(
    LocationID int primary key,
    City varchar(100) ,
    Area varchar(100) 
)
go
insert into Location  values (1001,'广东','广州')
insert into Location  values (10……

好牛比 学习了。

#14

这跟显示用户列表又有什么联系了。

#15

union all 的效率很低的，而且如何分页？

#16

引用 14 楼 karascanvas 的回复:

这跟显示用户列表又有什么联系了。

有联系阿。 显示用户列表的时候，假如LocationID不为空的话，不是还要得到详细的位置信息嘛，难道还要再去Location表里寻找吗？

#17

引用 14 楼 karascanvas 的回复:

这跟显示用户列表又有什么联系了。

union all 无法分页？

#18

引用 14 楼 karascanvas 的回复:

这跟显示用户列表又有什么联系了。

那如果多个表都引用了location的话，每修改一次location表里的city，我都要把各个表里的数据都改一下，很是麻烦啊。

#19

其实关键问题就是你必须要用 一句 sql来完成吧。

#20

引用 19 楼 karascanvas 的回复:

其实关键问题就是你必须要用 一句 sql来完成吧。

UNION  all 这个我看可以，但效率真的很低吗，还无法分页？我用Gridview自带的分页不行吗？

#21

很简单
select Users.*,Location.Province,Location.City,Location.Area 
From Users
left outer join Location on Users.LocationID=Location.LocationID

#22

引用 15 楼 karascanvas 的回复:

union all 的效率很低的，而且如何分页？

我第一思路就是想到union all，

你说不能分页？？？不会吧。。。你在外面嵌套一个查询不就够了。。。

效率高低，这到是没考虑，

#23

引用 21 楼 liuchaolin 的回复:

很简单
select Users.*,Location.Province,Location.City,Location.Area 
From Users
left outer join Location on Users.LocationID=Location.LocationID

好牛比。

#24

哥们用这条语句吧，，效率可能高很多。

select  a.userid,a.username,(case isnull(location,'')  when '' then (b.city+'-'+b.area) else location end) as LocationID  from Users a left join  Location b on a.LocationID = b.LocationID

#25

引用 24 楼 tashiwoweiyi 的回复:

哥们用这条语句吧，，效率可能高很多。

SQL code

select  a.userid,a.username,(case isnull(location,'')  when '' then (b.city+'-'+b.area) else location end) as LocationID  from Users a left join  Location b on a.Loca……

其实这里可以改一下

case when location is null then ...

#26

好的，我都试试

#27

引用 25 楼 karascanvas 的回复:

其实这里可以改一下

case when location is null then ...

那如果数据库里location 字段的值=''而不是null呢？

那样就查不出来了。。

#28

都好牛比，好厉害。

#29

#30

SELECT u.*
  ,CASE WHEN ISNULL(u.Location,'')=''
THEN (l.City+'-'+l.Area)
ELSE u.Location 
       END  AS   Location 
FROM Users u(NOLOCK)
LEFT JOIN Location l(NOLOCK) ON l.LocationID = u.LocationID

#31

tashiwoweiyi !

#32

好好看书，我不知道你用什么数据库，只知道MSSQL里有一个ISNULL，其它的你自己处理吧

#33

location表不大的话，最快的办法是把location表缓存，碰到Users.locationid非空就去缓存查。
纯语句的话，oracle直接用nvl+子查询最简单了。
select nvl(location,(select Province||City||Area from location b where b.locationid=a.locationid))  from users a
不过子查询效率比较慢，分页以后再用。