有四个表
z_User (User_ID,User_Card,Class_ID,User_Name)
z_Class(ClassID,Class_Name)
z_Door (Door_ID,Door_Name)
z_Record (User_Card,Door_ID,Record_Event)我想查询中Record_Event,Class_Name,Door_Name,User_Name记录信息
如果z_User中不包含该 User_Card时 User_Name 和Class_Name名称均为空 如果z_Door 表中Door_ID不存在 则Door_Name也为空请问如何来设置这个sql语句呢!
z_User (User_ID,User_Card,Class_ID,User_Name)
z_Class(ClassID,Class_Name)
z_Door (Door_ID,Door_Name)
z_Record (User_Card,Door_ID,Record_Event)我想查询中Record_Event,Class_Name,Door_Name,User_Name记录信息
如果z_User中不包含该 User_Card时 User_Name 和Class_Name名称均为空 如果z_Door 表中Door_ID不存在 则Door_Name也为空请问如何来设置这个sql语句呢!
from z_Record R left join z_Door D on r.Door_ID=d.Door_ID
left join z_User U on r.User_Card=u.User_Card
left join z_Class C on U.ClassID=c.ClassID
用外连接就OK了
select record_event,class_name,door_name,user_name from z_record as d left join z_user as a on a.user_card = d.user_card left join z_door as c on d.door_id = c.door_id join z_class as b on a.class_id = b.classid
应该以它为主表去联之后的几张表
select U.User_Name, R.Record_Event,C.Class_Name,D.Door_Name
from z_User u
(((left join z_Class C on u.Class_ID = C.Class_ID)
left join z_Record R on U.R.User_Card = R.User_Card)
left join z_Door D on R.Door_ID = D.Door_ID)
这是SQLSERVER的语法,好长时间没写了,大概这意思,你看看吧
另外给你介绍下联结
例表a
aid adate
1 a1
2 a2
3 a3
表b
bid bdate
1 b1
2 b2
4 b4
两个表a,b相连接,要取出id相同的字段
select * from a inner join b on a.aid = b.bid这是仅取出匹配的数据.
此时的取出的是:
1 a1 b1
2 a2 b2
那么left join 指:
select * from a left join b on a.aid = b.bid
首先取出a表中所有数据,然后再加上与a,b匹配的的数据
此时的取出的是:
1 a1 b1
2 a2 b2
3 a3 空字符
同样的也有right join
指的是首先取出b表中所有数据,然后再加上与a,b匹配的的数据
此时的取出的是:
1 a1 b1
2 a2 b2
4 空字符 b4
插入SQL 代码
select U.User_Name, R.Record_Event,
case U.User_Card when is null then NULL
when is not null then U.User_Name end as username,
case U.User_Card when is null then NULL
when is not null then C.Class_Name end as classname, D.Door_Name
from z_User as u
(((left join z_Record as R on U.User_Card = R.User_Card)
left join z_Class as C on u.Class_ID = C.Class_ID )
left join z_Door as D on R.Door_ID = D.Door_ID)