SQL Server数据库 多表union的问题

Question

有一个表叫做fold 里面的属性有
fname nvarchar(50) Unchecked
creid nchar(10) Unchecked
cretime datetime Unchecked
lamoid nchar(10) Checked
lamotime datetime Checked
mostate bit Unchecked
openor bit Unchecked
还有一个表
uinfo
如下：
uid nchar(10) Unchecked
uname nchar(10) Unchecked
email nvarchar(50) Checked
room int Unchecked
phone nvarchar(50) Checked
sex bit Unchecked
age int Checked
depid int Checked
现在需要把fold与uinfo做一个join得到creid=uid对应的uname还要再做一个join得到lamoid=uid对应的uname
怎么写呢？
错了，是多表join的问题…………不是union不好意思啊……

Answer 1

你的需求并不是一般所说的多表查询，一般讲多表查询通常是指多表连接查询，或者是嵌套查询。其实你的需求只要将各个查询结果进行联合就可以了。
select * from A where name like '%a%'
union all
select * from B where name like '%a%'
union all
select * from C where name like '%a%'
union all
select * from D where name like '%a%'

追问

要在一条记录里面，和fname一起显示

Answer 2

果然想的都一样！同意楼上的想法。
select u1.uname from fold f1 left join uinfo u1 on f1.creid=u1.uid
union
select u2.uname from fold f2 left join uinfo u2 on f2.lamoid=u2.uid

追问

如果是要在一条记录解决怎么办……就是说lamoname和crename都要显示在一行里面

追答

lamoname和crename 这两个字段是哪里的？
显示为一行就是 select lamoname,crename from 表名 where。。。。。。

Answer 3

join两次uinfo:
select c.*, d.uname as lamoname from
(select a.*, b.uname as crename from fold as a left join uinfo as b on a.creid = b.uid) as c
left join uinfo as d on c.lamoid = d.uid

Answer 4

Select * from fold
left join uinfo on uid=cid and and lamoid=uid

Answer 5

SELECT uname FROM fold LEFT JOIN uinfo ON creid=uid
UNION
SELECT uname FROM fold LEFT JOIN uinfo ON lamoid=uid

追问

如果是要在一条记录解决怎么办……就是说lamoname和crename都要显示在一行里面

追答

select case when sing ="A" then uname end as lamoname ,case when sing='B' then uname end as crename from (
SELECT uname,"A" as sing FROM fold LEFT JOIN uinfo ON creid=uid
UNION
SELECT uname,"B" as sing FROM fold LEFT JOIN uinfo ON lamoid=uid
) tab1