表关联:A表含有B表的ID,B表含有C表的ID,C表含有D表的ID,按照这个关联关系做一个全字段值的查询,在此关系中除了A表字段以外,B、C、D表除了主键和关联外键列名不会改变以外,其他列名都会存在增加。这样一个多表查询,应该怎么来做所有列的数据查询比较好?
如果select * from A,B where A.Bid=B.id 在这里我要怎么把B表做成一个组合表与A表关联 如果(select * from B,C where B.Cid=C.id) as newB 的话存在重复列B.Cid和C.id 现在要怎么办?
如果select * from A,B where A.Bid=B.id 在这里我要怎么把B表做成一个组合表与A表关联 如果(select * from B,C where B.Cid=C.id) as newB 的话存在重复列B.Cid和C.id 现在要怎么办?
select * from a,b,c,d where a.id = b.id and b.id = c.id and c.id = d.id??
(
select
A.ID as AID,
A.Name as AName,
B.Name as BName,
B.CID as CID
--....
from B inner join C on B.Cid=C.id
),
Cte_NewC as
(
select
Cte_NewB.*,
C.Name as CName
--....这里可以不选C.CID,因为CID已经在cte_NewB 中有了
from Cte_NewB
inner join C on Cte_NewB.CID=C.CID
)
select * from Cte_NewC
from A
inner join,B on A.Bid=B.id
inner join C on B.Cid = C.id
inner join D on C.Did = D.id但A.Bid 和B.id等确实会重复出现.
from A
inner join,B on A.Bid=B.id
inner join C on B.Cid = C.id
inner join D on C.Did = D.id
where not exists (select 1 from B where id = B.id and 日期 > B.日期)
要么如3楼所言,
通过从syscolumns中查询到表的所有字段,再使用动态语句动拼接
Tbl A
id value bid
a1 a b1
a2 aa b2
a3 aaa b3Tbl B
id value cid
b1 b c1
b2 bb c2
b3 bbb c3Tbl C
id value did
c1 b d1
c2 bb d2
c3 bbb d3Tbl D
id value
d1 b
d2 bb
d3 bbb现在要根据A表来查询所有数据 用left join 连接所有表的话在AS的时候会出现重复列,如果单独提出某些列的名称来的话,除了A表其他表的列都是会有变动的。