mysql多表连接查询，统计求和，分组，时间条件，经典语句，ECstore

最新推荐文章于 2024-05-18 14:06:20 发布

chineselogin

最新推荐文章于 2024-05-18 14:06:20 发布

阅读量601

点赞数 1

分类专栏： mysql 文章标签： mysql优化 mysql sql

本文链接：https://blog.csdn.net/chineselogin/article/details/112858910

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

这个复杂的SQL查询联合了两个数据子集，分别针对 Yingdan 和订单数据，以统计每年每月的销售员、会员ID、商品类别、数量、金额等关键指标。查询结果按年、月、销售员和会员ID排序，并包含了地区、品牌、标签等附加信息。通过此查询，可以全面了解销售和订单的分布情况及趋势。

摘要由CSDN通过智能技术生成

$sql ='SELECT A.year, A.month, A.sale_member_id, B.name as sales_man, 0 as yd_sum, 0 as order_sum,
                    A.member_id, "" as name, E.region_id as area, F.brand_name, GROUP_CONCAT(DISTINCT H.tag_name) as tag, C.cat_name,
                    SUM(A.quantity) as quantity, SUM(A.money) as money, MAX(A.ordertime) as ordertime,
                    GROUP_CONCAT(DISTINCT A.location) as location, SUM(A.nums) as nums, SUM(A.amount) as amount,
                    MAX(A.createtime) as createtime, GROUP_CONCAT(DISTINCT A.yddesc) as yddesc,
                    D.login_account, E.name as username, E.reg_com_name
                FROM (
                    (
                        SELECT YEAR(yb.ordertime) as year, MONTH(yb.ordertime) as month,y.member_id, m.sale_member_id, yb.cat_id, 
                        SUM(yb.money) as money, SUM(yb.quantity) as quantity, MAX(yb.ordertime) as ordertime,
                        GROUP_CONCAT(DISTINCT yb.location) as location, GROUP_CONCAT(DISTINCT yb.desc) as yddesc,
                        0 as amount, 0 as nums, 0 as createtime
                        FROM sdb_b2c_member_customer_yingdan_body yb 
                        LEFT JOIN sdb_b2c_member_customer_yingdan y ON y.yingdan_id=yb.yingdan_id 
                        LEFT JOIN sdb_b2c_members m on y.member_id = m.member_id
                        WHERE yb.is_del="0" AND m.sale_member_id>0 AND y.is_del="0" GROUP BY year,month,sale_member_id,member_id,cat_id
                    )
                    UNION ALL
                    (
                        SELECT  
                            YEAR(FROM_UNIXTIME(o.first_pay_time)) as year, 
                            MONTH(FROM_UNIXTIME(o.first_pay_time)) as month, 
                            o.member_id,o.sale_member_id,
                            if(
                                LENGTH(c.cat_path)-LENGTH(REPLACE(c.cat_path,",",""))=4,
                                (SELECT cat_id FROM sdb_b2c_goods_cat WHERE cat_id=c.parent_id),
                                c.cat_id
                            ) as cat_id,
                            0 as money, 0 as quantity, 0 as ordertime,"" as location,"" as yddesc,
                            SUM(i.amount) as amount, SUM(i.nums) as nums, MAX(o.first_pay_time) as createtime
                        FROM sdb_b2c_order_items i 
                        LEFT JOIN sdb_b2c_goods_cat c ON i.cat_id = c.cat_id 
                        LEFT JOIN sdb_b2c_orders o ON i.order_id = o.order_id 
                        WHERE o.pay_status in (\'1\',\'2\',\'3\',\'4\') AND o.sale_member_id>0 AND o.first_pay_time>0 
                        GROUP BY year,month,sale_member_id,member_id,cat_id
                    )
                ) AS A
                LEFT JOIN sdb_b2c_member_salesman B ON A.sale_member_id = B.sap_id 
                LEFT JOIN sdb_b2c_goods_cat C ON A.cat_id = C.cat_id                
                LEFT JOIN sdb_pam_members D ON A.member_id = D.member_id
                LEFT JOIN sdb_b2c_members E ON A.member_id = E.member_id
                LEFT JOIN sdb_b2c_brand F ON E.brand_id = F.brand_id                
                LEFT JOIN sdb_desktop_tag_rel G ON G.tag_type=\'members\' AND G.rel_id=A.member_id
                LEFT JOIN sdb_desktop_tag H ON H.tag_id = G.tag_id 
                WHERE 1 '.$this->_filter($filter).' 
                GROUP BY year,month,sale_member_id,member_id,cat_name 
                ORDER BY (year+0) DESC,(month+0) DESC,A.sale_member_id,A.member_id DESC,A.cat_id';