SQLserver 同一台服务器 跨库建立视图 UNION 表字段不统一

同一台服务器跨数据库查询
SELECT * FROM dbnaem.dbo.tablename  where 条件

视图代码
SELECT id, pic, addtime, title, short, clicks, type, keyword, isindex,author
FROM dbo.tbl_mag_news
WHERE publish = 1
UNION
SELECT id, pic, addtime, titles, short, clicks, type, keyword, isindex,author
FROM dbo.tbl_news
WHERE publish = 1
UNION
SELECT id, pic, addtime, titles, short, clicks, type, keyword, isindex,author
FROM dbo.tbl_continuing
WHERE publish = 1
UNION
SELECT id, pic, addtime, titles, short, clicks, type, keyword, isindex,NULL
FROM dbo.tbl_train
WHERE publish = 1
UNION
SELECT id, pic, addtime, titles, short, clicks, type, keyword, isindex,author
FROM dbo.tbl_development
WHERE publish = 1
UNION
SELECT NULL,convert(varchar(500),file_no),publish_time,convert(varchar(255),title),convert(VARCHAR(500),abstract),NULL,0,Null,Null,
convert(VARCHAR(250),user_real_name) FROM shyy_journal.dbo.t_publish_article
WHERE 1=1

简要说明:

建立视图union起来,对应的字段类型必须相同,最后一个select语句里面的

convert(varchar(500),file_no)

因为跨数据库的table里面的字段存在ntext,nvarchar类型,select不能直接查询所以需要转换类型,

查询字段里面如果没有相应的字段可以用Null顶替,当前值也就是Null,

我们也可以给没有的字段根据自己需求的赋给默认值

例如:

SELECT id, spic,title,pic,islock FROM dbo.tbl_magazine
WHERE publish = 1
UNION
SELECT Null,Null,convert(varchar(100),quarter_name),convert(varchar(50),quarter_id),2 FROM shyy_journal.dbo.t_year_quarter
WHERE 1=1

table:t_year_quarter 没有 table:tbl_magazine里面的islock字段，我们可以给select t_year_quarter 语句里默认给islock字段的值为2