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

模块管理常规功能自定义系统的设计与实现(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的执行效率和速度我都忽略不计。

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




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



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

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