表ONU: ID,Name,ModelID,MgrHostID,VoiceHostID,UpDevID,Note
表Host: ID,Name
表Model:ID,Name我要查询表ONU,生成以下内容
ONU.ID, ONU.Name, Model.Name AS ModelName, Host.Name AS MgrHostName, Host.Name AS VocieHostName, ONU.Name AS UpDevNameONU表中的MgrHostID和VoiceHostID是HOST表中的记录ID,这两个值可能相同也可能不同,UpDevID是本表的另外一条记录的ID我会写简单的联合查询语句,如
SELECT ONU.*, Model.Name AS ModelName, Host.Name AS MgrHostName FROM ONU,HOST,Model Where ONU.ModelID=Model.ID AND ONU.MgrHostID=Host.ID
但这个语句有三个问题没能解决,1是结果集中有两个字段MgrHostName和VocieHostName来自同一张表的同一字段,2是UpDevName来自本表的另外一条记录,3是表ONU中的记录除了ID是自动生成的,Name为必填字段外,其它字段可能是空的,这样用以上语句就可能查不出记录。我期望的结果如下:
ONU.*, Model.Name AS ModeName Where ONU.ModelID=Model.ID, Host.Name AS MgrHostName WHERE ONU.MgrHostID=Host.ID, Host.Name AS VoiceHostName WHERE ONU.VoiceHostID=Host.ID, ONU.Name AS UpDevName WHERE ONU.UpDevID=ONU.ID
请大虾赐教
select
ONU.*,Model.Name,Host.Name AS MgrHostName,ONU.Name AS UpDevName
from ONU
left join Model on ONU.ModelID=Model.ID
left join Host on ONU.MgrHostID=Host.ID
where ONU.UpDevID=ONU.ID
--这样就是ONU的所有记录 和 Model.Name,Host.Name
select
ONU.*,Model.Name,Host.Name AS MgrHostName,ONU.Name AS UpDevName
from ONU
left join Model on ONU.ModelID=Model.ID
left join Host on ONU.MgrHostID=Host.ID--如果不想显示多余的id
select
ONU.ID,--VoiceHostID,UpDevID 这两个id对应name在哪个表里,在left join 后面继续写就ok了?
ONU.Name AS UpDevName,Model.Name as ModeName,Host.Name AS MgrHostName,Note
from ONU
left join Model on ONU.ModelID=Model.ID
left join Host on ONU.MgrHostID=Host.ID
ONU.ID,
ONU.Name AS UpDevName,
Model.Name as ModeName,
Host.Name AS MgrHostName,
a.Name as VoiceHostName,Note
from ONU
left join Model on ONU.ModelID=Model.ID
left join Host on ONU.MgrHostID=Host.ID
left join Host a on ONU.MgrHostID=a.ID
--修正一下
select
ONU.ID,
ONU.Name AS UpDevName,
Model.Name as ModeName,
Host.Name AS MgrHostName,
a.Name as VoiceHostName,Note
from ONU
left join Model on ONU.ModelID=Model.ID
left join Host on ONU.MgrHostID=Host.ID
left join Host a on ONU.VoiceHostID=a.ID --这个字段名修正一下
declare @out table (id int,hid int,did int,mid int)
insert into @out
select 1,1,3,1 union all
select 2,1,2,4 union all
select 3,1,2,3 union all
select 4,null,null,null union all
select 5,3,3,2select * from @out
/*
id hid did mid
----------- ----------- ----------- -----------
1 1 3 1
2 1 2 4
3 1 2 3
4 NULL NULL NULL
5 3 4 2
*/declare @hd table (id int,hname varchar(1))
insert into @hd
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d'select * from @hd
/*
id hname
----------- -----
1 a
2 b
3 c
4 d
*/declare @m table (m int,name varchar(1))
insert into @m
select 1,'h' union all
select 2,'i' union all
select 3,'j' union all
select 4,'k'select * from @m
/*
m name
----------- ----
1 h
2 i
3 j
4 k
*/select
a.id,b.hname,c.hname as dname,d.name from @out a
left join @hd b on a.hid=b.id
left join @hd c on a.did=c.id
left join @m d on a.mid=d.m/*
id hname dname name
----------- ----- ----- ----
1 a c h
2 a b k
3 a b j
4 NULL NULL NULL
5 c c i
*/