五张没有任何关系的表,能否用一条语句列出这五张表里的记录?我用视图来设计是可以的,但还是会丢失一些信息,也就是说部门记录不会出来。说明:
当初本想建一个表的,好方便以后查询操作,但后来项目有变动,所录信息一共有五种模式,所以干脆就建了五张表,分别来记录这些信息。后来发现查询是个问题。
当初本想建一个表的,好方便以后查询操作,但后来项目有变动,所录信息一共有五种模式,所以干脆就建了五张表,分别来记录这些信息。后来发现查询是个问题。
as
select * from tb1
union
select * from tb2
union
select * from tb3
union
select * from tb4
union
select * from tb5
go
select * from v_test会丢失什么?
dbo.RO_Content_04.ID AS Expr2, dbo.RO_Content_01.ID AS Expr3,
dbo.RO_Content_03.sxbh AS Expr5, dbo.RO_Content_02.jgmc AS Expr6,
dbo.RO_Content_01.xxmc AS Expr7, dbo.RO_Content_04.xzzfmc AS Expr8,
dbo.RO_Content_05.ID AS Expr9, dbo.RO_Content_05.swmc AS Expr10,
dbo.RO_Content_03.*, dbo.RO_Content_04.*, dbo.RO_Content_05.*,
dbo.RO_Content_02.*, dbo.RO_Content_01.*
FROM dbo.RO_Content_01 FULL OUTER JOIN
dbo.RO_Content_02 ON
dbo.RO_Content_01.ID = dbo.RO_Content_02.ID FULL OUTER JOIN
dbo.RO_Content_03 ON
dbo.RO_Content_02.ID = dbo.RO_Content_03.ID FULL OUTER JOIN
dbo.RO_Content_04 ON
dbo.RO_Content_03.ID = dbo.RO_Content_04.ID FULL OUTER JOIN
dbo.RO_Content_05 ON dbo.RO_Content_04.ID = dbo.RO_Content_05.ID
as
select *,cast(null as 数据类型) as a1,cast(null as 数据类型) as a2,... from tb1 --这里的a1,a2代表tb1没有其他表有的字段名
union
select ... from tb2 --这里的...代表已上面一样的次序的字段组合,属于tb2的写字段名,否则用常量null
union
select ... from tb3 --这里的...代表已上面一样的次序的字段组合,属于tb2的写字段名,否则用常量null
union
select ... from tb4 --这里的...代表已上面一样的次序的字段组合,属于tb2的写字段名,否则用常量null
union
select ... from tb5 --这里的...代表已上面一样的次序的字段组合,属于tb2的写字段名,否则用常量null
goselect * from v_test
union all
select ID from RO_Content_02
union all
select ID from RO_Content_03
union all
select ID from RO_Content_04
union all
select ID from RO_Content_05这样似乎是可以查出来的
select @sql=''+quotename(name)+',' from syscolumns where id=object_id('表1') or id=object_id('表2') or id=object_id('表3')--取得所有字段
set @sql= stuff(@sql,len(@sql),1,'')
set @sql='select '+@sql+' from 表1,表2,表3'
exec (@sql)
union all
select ID from RO_Content_02
union all
select ID from RO_Content_03
union all
select ID from RO_Content_04
union all
select ID from RO_Content_05 这样似乎是可以查出来的这个行吗? 你试试?我这不行