mysql 视图不支持子查询的解决办法

今天领导让我写一个视图，需要把三张没有任何关联关系的表数据整合到一张视图上，

我按照最初的方式先把SQL整理出来后，结果mysql建视图时报错了：

下面是最初的方案，

CREATE OR REPLACE VIEW view_systeminfo_detail 
AS 
SELECT
	f.filesystemSize,
	f.used,
	f.unUsed,
	c.totalContentInfo,
	s.totalTask,
	s2.totalActualTask
FROM
	tab_FileSystem f join (
		select count(id) totalContentInfo from tab_ContentInfo
	) c join (
		select count(v2vID) totalTask from tab_StationSgInfo 
	) s join (
		select count(v2vID) totalActualTask from tab_StationSgInfo where v2vState = 2
	) s2

 报错信息如下：

Error : View's SELECT contains a subquery in the FROM clause

 网上查找了下，说是mysql视图不支持子查询，然后就网上查资料呗，大多数都说，需要把视图进行拆分，既然网上都这么说，那就拆吧：

create or replace view view_temp_filesystem as
select f.filesystemSize, f.used, f.unUsed, count(c.id) totalContentInfo from tab_FileSystem f, tab_ContentInfo c group by f.filesystemDir;

create or replace view view_temp_actualtask as
select count(s.v2vID) totalActualTask from tab_StationSgInfo s where s.v2vState = 2;

create or replace view view_temp_totaltask as
select count(s.v2vID) totalActualTask from tab_StationSgInfo s ;

create or replace view view_systeminfo_detail as 
select f.*, s.*, a.* from view_temp_filesystem f, view_temp_actualtask a, view_temp_totaltask s ;

 结果就一个查询，创建了4个视图，感觉目的终于达到了，先高兴一番，然后让领导查看了下，结果领导来了句，没必要这么麻烦，有更简单的方式：

CREATE OR REPLACE VIEW view_systeminfo_detail as
SELECT
	f.filesystemSize,
	f.used,
	f.unUsed,
	(select count(distinct c.id ) from tab_ContentInfo c) totalContentInfo,
	(select count(distinct s1.v2vID) totalTask from tab_StationSgInfo s1 ) totalTask,
	(select count(distinct s2.v2vID) totalActualTask from tab_StationSgInfo s2 where s2.v2vState = 2) totalActualTask,
	(select count(distinct u1.userId) totalUser from tab_User u1) totalUser,
	(select count(distinct u2.userId) totalUser from tab_User u2 where u2.userState = 1) onlineUser
FROM
	tab_FileSystem f

 

看完SQL之后，感觉眼前瞬间一亮啊，哇塞，SQL居然还可以这么玩，感觉好高大上啊。

 

不知道别人还有没有更好的解决方案，不管怎么着，先记录一下总是好的。

 

 

又发现了点新用法，再次记录一下：

CREATE OR REPLACE VIEW view_systeminfo_detail as
SELECT
	f.filesystemSize,
	f.used,
	f.unUsed,
	(select count(distinct c.id ) from tab_ContentInfo c) totalContentInfo,
	(select count(distinct s1.v2vID) totalTask from tab_StationSgInfo s1 ) totalTask,
	(select count(distinct s2.v2vID) totalActualTask from tab_StationSgInfo s2 where s2.v2vState = 2) totalActualTask,
	(select count(distinct u1.userId) totalUser from tab_User u1) totalUser,
	(select count(distinct u2.userId) totalUser from tab_User u2 where u2.userState = 1) onlineUser,
	round(((select totalActualTask) / (select totalTask) * 0.8 + f.used / f.filesystemSize * 0.2) * 100, 0) capability
FROM
	tab_FileSystem f

 注意select中的最后一列，居然可以这么用，好兴奋，又get了一个技能。。。。。。