sql 视图创建

来源：互联网发布：gps航迹编辑软件编辑：程序博客网时间：2024/06/10 20:12

SELECT o.userid,o.username FROM partner_salesman O,partner_user PU WHERE O.partnerid=PU.partnerid AND PU.userid='2017021700000001'union allSELECT o.userid,o.username  FROM partner_salesman O WHERE O.partnerid IN ( SELECT PB.lowerpartnerid FROM partner_belong PB,partner_user PU WHERE PB.partnerid=PU.partnerid AND PU.userid='2017021700000001' and  PB.lowerpartnerid<>PU.partnerid)

报错
The used SELECT statements have a different number of columns

解决方案：
1.保证两个语句查询字段一致

SELECT o.userid,o.username FROM partner_salesman O,partner_user PU WHERE O.partnerid=PU.partnerid AND PU.userid='2017021700000001'union allSELECT o.userid,o.username  FROM partner_salesman O WHERE O.partnerid IN ( SELECT PB.lowerpartnerid FROM partner_belong PB,partner_user PU WHERE PB.partnerid=PU.partnerid AND PU.userid='2017021700000001' and  PB.lowerpartnerid<>PU.partnerid)

2.创建视图（一张虚拟的表）

查询这个视图(虚拟的表)

create view myview asSELECT o.userid,o.username FROM partner_salesman O,partner_user PU WHERE O.partnerid=PU.partnerid AND PU.userid='2017021700000001'union allSELECT o.userid,o.username  FROM partner_salesman O WHERE O.partnerid IN ( SELECT PB.lowerpartnerid FROM partner_belong PB,partner_user PU WHERE PB.partnerid=PU.partnerid AND PU.userid='2017021700000001' and  PB.lowerpartnerid<>PU.partnerid)

查询视图

select * from myview;

1 0