模块管理常规功能自定义系统的设计与实现(40--终级阶段 综合查询[7])

综合查询(7)--多个模块的查询

        前几节讲的查询都是二个模块之间的，现在看看多模块的查询。其实原理是一样的，先选择字段，设计一个查询方案，把和订单相关的所有模块都选进去。

        我又设置了二个条件，设置好后的查询主界面如下：

        现在选择基准模块的菜单下有七个模块可供选择。也就是说上面的查询一共选择了七个表的数据。

        现在一个个看看结果吧。先选择“订单”作为基准模块。

        以客户单位作为基准模块：

       再选择客户等级作为基准模块：

        这个查询贴一下自动生成的sql 语句：

    select        top 5 '' as _total_ ,        100 as _level_ ,        0 as _count_ ,        _t7014.tf_rateId as tf_rateId ,        _t7014.tf_name as tf_name ,        ( select            sum(_t6050.tf_number)          from            OrdersDetail _t6050         left outer join            Orders _t6040                 on _t6040.tf_ordersId = _t6050.tf_ordersId          left outer join            Customer _t6010                 on _t6010.tf_customerId = _t6040.tf_customerId          left outer join            City _t7012                 on _t7012.tf_cityId = _t6010.tf_cityId          left outer join            Province _t7010                 on _t7010.tf_provinceId = _t7012.tf_provinceId          left outer join            Trade _t7016                 on _t7016.tf_tradeId = _t6010.tf_tradeId          left outer join            Rate _child_t7014                 on _child_t7014.tf_rateId = _t6010.tf_rateId          left outer join            Salesman _t6020                 on _t6020.tf_salesmanId = _t6040.tf_salesmanId          left outer join            _Department _t9011                 on _t9011.tf_departmentId = _t6020.tf_departmentId          left outer join            _DepartmentScope _t9010                 on _t9010.tf_scopeId = _t9011.tf_scopeId          left outer join            Product _t6030                 on _t6030.tf_productId = _t6050.tf_productId          left outer join            ProductClass _t7018                 on _t7018.tf_productClassId = _t6030.tf_productClassId          where            _t6030.tf_productId in (                '1','2','3','4','5','6','7'            )             and _t6010.tf_customerId in (                '1','2','3','4','5','6','7','8','9'            )             and _child_t7014.tf_rateId = _t7014.tf_rateId ) as S__t6050___tf_number ,        ( select            sum(_t6050.tf_subtotalPrice)          from            OrdersDetail _t6050         left outer join            Orders _t6040                 on _t6040.tf_ordersId = _t6050.tf_ordersId          left outer join            Customer _t6010                 on _t6010.tf_customerId = _t6040.tf_customerId          left outer join            City _t7012                 on _t7012.tf_cityId = _t6010.tf_cityId          left outer join            Province _t7010                 on _t7010.tf_provinceId = _t7012.tf_provinceId          left outer join            Trade _t7016                 on _t7016.tf_tradeId = _t6010.tf_tradeId          left outer join            Rate _child_t7014                 on _child_t7014.tf_rateId = _t6010.tf_rateId          left outer join            Salesman _t6020                 on _t6020.tf_salesmanId = _t6040.tf_salesmanId          left outer join            _Department _t9011                 on _t9011.tf_departmentId = _t6020.tf_departmentId          left outer join            _DepartmentScope _t9010                 on _t9010.tf_scopeId = _t9011.tf_scopeId          left outer join            Product _t6030                 on _t6030.tf_productId = _t6050.tf_productId          left outer join            ProductClass _t7018                 on _t7018.tf_productClassId = _t6030.tf_productClassId          where            _t6030.tf_productId in (                '1','2','3','4','5','6','7'            )             and _t6010.tf_customerId in (                '1','2','3','4','5','6','7','8','9'            )             and _child_t7014.tf_rateId = _t7014.tf_rateId ) as S__t6050___tf_subtotalPrice ,        ( select            max(_t6050.tf_subtotalPrice)          from            OrdersDetail _t6050         left outer join            Orders _t6040                 on _t6040.tf_ordersId = _t6050.tf_ordersId          left outer join            Customer _t6010                 on _t6010.tf_customerId = _t6040.tf_customerId          left outer join            City _t7012                 on _t7012.tf_cityId = _t6010.tf_cityId          left outer join            Province _t7010                 on _t7010.tf_provinceId = _t7012.tf_provinceId          left outer join            Trade _t7016                 on _t7016.tf_tradeId = _t6010.tf_tradeId          left outer join            Rate _child_t7014                 on _child_t7014.tf_rateId = _t6010.tf_rateId          left outer join            Salesman _t6020                 on _t6020.tf_salesmanId = _t6040.tf_salesmanId          left outer join            _Department _t9011                 on _t9011.tf_departmentId = _t6020.tf_departmentId          left outer join            _DepartmentScope _t9010                 on _t9010.tf_scopeId = _t9011.tf_scopeId          left outer join            Product _t6030                 on _t6030.tf_productId = _t6050.tf_productId          left outer join            ProductClass _t7018                 on _t7018.tf_productClassId = _t6030.tf_productClassId          where            _t6030.tf_productId in (                '1','2','3','4','5','6','7'            )             and _t6010.tf_customerId in (                '1','2','3','4','5','6','7','8','9'            )             and _child_t7014.tf_rateId = _t7014.tf_rateId ) as X__t6050___tf_subtotalPrice ,        ( select            min(_t6050.tf_subtotalPrice)          from            OrdersDetail _t6050         left outer join            Orders _t6040                 on _t6040.tf_ordersId = _t6050.tf_ordersId          left outer join            Customer _t6010                 on _t6010.tf_customerId = _t6040.tf_customerId          left outer join            City _t7012                 on _t7012.tf_cityId = _t6010.tf_cityId          left outer join            Province _t7010                 on _t7010.tf_provinceId = _t7012.tf_provinceId          left outer join            Trade _t7016                 on _t7016.tf_tradeId = _t6010.tf_tradeId          left outer join            Rate _child_t7014                 on _child_t7014.tf_rateId = _t6010.tf_rateId          left outer join            Salesman _t6020                 on _t6020.tf_salesmanId = _t6040.tf_salesmanId          left outer join            _Department _t9011                 on _t9011.tf_departmentId = _t6020.tf_departmentId          left outer join            _DepartmentScope _t9010                 on _t9010.tf_scopeId = _t9011.tf_scopeId          left outer join            Product _t6030                 on _t6030.tf_productId = _t6050.tf_productId          left outer join            ProductClass _t7018                 on _t7018.tf_productClassId = _t6030.tf_productClassId          where            _t6030.tf_productId in (                '1','2','3','4','5','6','7'            )             and _t6010.tf_customerId in (                '1','2','3','4','5','6','7','8','9'            )             and _child_t7014.tf_rateId = _t7014.tf_rateId ) as N__t6050___tf_subtotalPrice       from        Rate _t7014

        这个语句很长，如果字段多的话会更加长，至于表多得会不会超过数据库的限制，或者是执行效率太低，这个就不是我考虑的事了，这是数据库公司去做的活了。现在都是云计算了，对于中小型的管理应用，sql的执行效率和速度我都忽略不计。

        下面选择以省份为基准模块：

        下面是选择了市以后的图：选择了市以后，会加入“省”，因为省是市的父模块。

        还有几个模块不贴了，大同小异。以上即为多模块查询方案的简单例子。在实际使用过程中如果觉得不能满足需求，那你可以先行建立一个视图，然后配置视图的信息，作为模块放入到综合查询里面，直接联合使用。

        还有一个非常重要内容的这里先提一下，如果操作员对某个模块有记录查看限制，比如说只能看“销售二部”的数据，那么在综合查询的所有的涉及到部门的子模块的查询，包括聚合查询时，都会加入对于“销售二部”的限制。你所有设置的对操作员的记录查看限制，都会自动的加到所查询的表上。这样综合查询的权限问题得以根本解决，查询的数据不会超出权限的范围。