同一台服务器跨数据库查询 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=1table:t_year_quarter 没有 table:tbl_magazine里面的islock字段，我们可以给select t_year_quarter 语句里默认给islock字段的值为2