随笔-67  评论-522  文章-0  trackbacks-0
    上一篇讲了一些MySQL比较常用的SQL语句写法,这篇再记录下,平时可能会用到的复杂点的查询语句的写法。
    复杂查询语句一般是在报表查询中比较常见,大象之前在“使用jxl生成复杂报表的分析与设计(二)”中就讲过,处理复杂报表,首先需要搞清楚它的业务关系,这个非常关键,如果你连这些业务都没弄明白就开始动手做,最后肯定是会有问题的。
    搞清楚业务关系后,就可以对报表进行分解,看看到底要准备什么数据,按照这些数据加上对应的业务关系来写SQL语句,一步一步做下来,就可以实现所需要的功能了。大家应该看到下面两个例子都是查询语句,但是如果我们在前面加上INSERT INTO TABLE (COLUMN1, COLUMN2, COLUMN3,...)语句,我们就可以将数据插入到统计结果表中,这样每次查询的时候,就可以只查一张表就行了,这样效率是不是提高了很多呢?

    查询项目数据与销售统计
SELECT
    a.*, f.ORG_NAME DEPT_NAME,
    IFNULL(d.CONT_COUNT, 0) SIGN_CONT_COUNT,
    IFNULL(d.TOTAL_PRICE, 0) SIGN_CONT_MONEY,
    IFNULL(c.CONT_COUNT, 0) SIGN_ARRI_CONT_COUNT,
    IFNULL(c.TOTAL_PRICE, 0) SIGN_ARRI_CONT_MONEY,
    IFNULL(b.CONT_COUNT, 0) TOTAL_ARRI_CONT_COUNT,
    IFNULL(b.TOTAL_PRICE, 0) TOTAL_ARRI_MONEY,
    0 PUBLISH_TOTAL_COUNT,
    0 PROJECT_COUNT,
    0 COMMON_COUNT,
    0 STOCK_COUNT,
    0 MERGER_COUNT,
    0 INDUSTRY_COUNT,
    0 BRAND_COUNT
FROM
    (
        -- 查询包含客户经理,部门主管,公司主管三种类型所有分公司的人员
        SELECT
            u.USER_ID,
            u.REAL_NAME,
            u.ORG_PARENT_ID,
            o.ORG_NAME,
            u.ORG_ID
        FROM
            SE_USER u
        INNER JOIN SE_ORGANIZ o ON u.ORG_PARENT_ID = o.ORG_ID
        WHERE
            u.`STATUS` = 1
        AND u.`LEVELIN (123)
        AND o.PARENT_ID <> 0
    ) a 
-- 查询部门名称
LEFT JOIN SE_ORGANIZ f ON a.ORG_ID = f.ORG_ID 
-- 签约合同数与合同金额
LEFT JOIN (
    SELECT
        CUST_MGR_ID,
        COUNT(CONT_ID) CONT_COUNT,
        SUM(TOTAL_PRICE) TOTAL_PRICE
    FROM
        SE_CONTRACT
    WHERE
        DATE_FORMAT(CREATE_TIME, '%Y-%m-%d'= '2012-06-08'
    GROUP BY
        CUST_MGR_ID
) d ON a.USER_ID = d.CUST_MGR_ID 
-- 签约并回款合同数与回款金额
LEFT JOIN (
    SELECT
        CUST_MGR_ID,
        COUNT(CONT_ID) CONT_COUNT,
        SUM(TOTAL_PRICE) TOTAL_PRICE
    FROM
        SE_CONTRACT
    WHERE
        (STATUS = 6 OR STATUS = 10)
    AND DATE_FORMAT(CREATE_TIME, '%Y-%m-%d'= '2012-06-08'
    GROUP BY
        CUST_MGR_ID
) c ON a.USER_ID = c.CUST_MGR_ID 
-- 总回款合同数与总回款金额
LEFT JOIN (
    SELECT
        c.CUST_MGR_ID,
        COUNT(c.CONT_ID) CONT_COUNT,
        SUM(c.TOTAL_PRICE) TOTAL_PRICE
    FROM
        SE_CONTRACT c
    INNER JOIN SE_CONT_AUDIT a ON c.CONT_ID = a.CONT_ID
    WHERE
        (c. STATUS = 6 OR c. STATUS = 10)
    AND a.IS_PASS = 1
    AND DATE_FORMAT(a.AUDIT_TIME, '%Y-%m-%d'= '2012-06-08'
    GROUP BY
        c.CUST_MGR_ID
) b ON a.USER_ID = b.CUST_MGR_ID
ORDER BY
    a.ORG_PARENT_ID,
    a.USER_ID

    项目数据月度环比
SELECT
    k.*,
IF (
    k.LAST_PUBLISH_TOTAL_COUNT > 0,
    ROUND((k.RISE_PUBLISH_TOTAL_COUNT / k.LAST_PUBLISH_TOTAL_COUNT) * 1002),
    0
) RELATIVE_PUBLISH_RATIO,
IF (
    k.LAST_PROJECT_COUNT > 0,
    ROUND((k.RISE_PROJECT_COUNT / k.LAST_PROJECT_COUNT) * 1002),
    0
) RELATIVE_PROJECT_RATIO,
IF (
    k.LAST_COMMON_COUNT > 0,
    ROUND((k.RISE_COMMON_COUNT / k.LAST_COMMON_COUNT) * 1002),
    0
) RELATIVE_COMMON_RATIO
FROM
    (
        SELECT
            m.ORG_NAME,
            IFNULL(n.LAST_PUBLISH_TOTAL_COUNT, 0) LAST_PUBLISH_TOTAL_COUNT,
            IFNULL(n.LAST_PROJECT_COUNT, 0) LAST_PROJECT_COUNT,
            IFNULL(n.LAST_COMMON_COUNT, 0) LAST_COMMON_COUNT,
            m.PUBLISH_TOTAL_COUNT,
            m.PROJECT_COUNT,
            m.COMMON_COUNT,
            IFNULL(m.PUBLISH_TOTAL_COUNT - n.LAST_PUBLISH_TOTAL_COUNT, 0) RISE_PUBLISH_TOTAL_COUNT,
            IFNULL(m.PROJECT_COUNT - n.LAST_PROJECT_COUNT, 0) RISE_PROJECT_COUNT,
            IFNULL(m.COMMON_COUNT - n.LAST_COMMON_COUNT, 0) RISE_COMMON_COUNT
        FROM
            (
                SELECT
                    '全国' AS ORG_NAME,
                    SUM(PUBLISH_TOTAL_COUNT) AS PUBLISH_TOTAL_COUNT,
                    SUM(PROJECT_COUNT) AS PROJECT_COUNT,
                    SUM(COMMON_COUNT) AS COMMON_COUNT
                FROM
                    SE_STAT_ORG
                WHERE
                    DATE_FORMAT(RECORD_DATE, '%Y-%m'= '2012-07'
            ) m
        LEFT JOIN (
            SELECT
                '全国' AS ORG_NAME,
                SUM(PUBLISH_TOTAL_COUNT) AS LAST_PUBLISH_TOTAL_COUNT,
                SUM(PROJECT_COUNT) AS LAST_PROJECT_COUNT,
                SUM(COMMON_COUNT) AS LAST_COMMON_COUNT
            FROM
                SE_STAT_ORG
            WHERE
                DATE_FORMAT(RECORD_DATE, '%Y-%m'= '2012-06'
        ) n ON m.ORG_NAME = n.ORG_NAME
        UNION
            SELECT
                a.ORG_NAME,
                IFNULL(b.LAST_PUBLISH_TOTAL_COUNT, 0) LAST_PUBLISH_TOTAL_COUNT,
                IFNULL(b.LAST_PROJECT_COUNT, 0) LAST_PROJECT_COUNT,
                IFNULL(b.LAST_COMMON_COUNT, 0) LAST_COMMON_COUNT,
                a.PUBLISH_TOTAL_COUNT,
                a.PROJECT_COUNT,
                a.COMMON_COUNT,
                IFNULL(a.PUBLISH_TOTAL_COUNT - b.LAST_PUBLISH_TOTAL_COUNT, 0) RISE_PUBLISH_TOTAL_COUNT,
                IFNULL(a.PROJECT_COUNT - b.LAST_PROJECT_COUNT, 0) RISE_PROJECT_COUNT,
                IFNULL(a.COMMON_COUNT - b.LAST_COMMON_COUNT, 0) RISE_COMMON_COUNT
            FROM
                (
                    SELECT
                        ORG_ID,
                        ORG_NAME,
                        SUM(PUBLISH_TOTAL_COUNT) AS PUBLISH_TOTAL_COUNT,
                        SUM(PROJECT_COUNT) AS PROJECT_COUNT,
                        SUM(COMMON_COUNT) AS COMMON_COUNT
                    FROM
                        SE_STAT_ORG
                    WHERE
                        DATE_FORMAT(RECORD_DATE, '%Y-%m'= '2012-07'
                    GROUP BY
                        ORG_ID
                ) a
            LEFT JOIN (
                SELECT
                    ORG_ID,
                    SUM(PUBLISH_TOTAL_COUNT) AS LAST_PUBLISH_TOTAL_COUNT,
                    SUM(PROJECT_COUNT) AS LAST_PROJECT_COUNT,
                    SUM(COMMON_COUNT) AS LAST_COMMON_COUNT
                FROM
                    SE_STAT_ORG
                WHERE
                    DATE_FORMAT(RECORD_DATE, '%Y-%m'= '2012-06'
                GROUP BY
                    ORG_ID
            ) b ON a.ORG_ID = b.ORG_ID
    ) k

    本文为菠萝大象原创,如要转载请注明出处。http://www.blogjava.net/bolo
posted on 2015-02-02 10:04 菠萝大象 阅读(6115) 评论(1)  编辑  收藏 所属分类: Database

评论:
# re: 常用的MySQL查询语句写法 2015-02-02 10:25 | 京山游侠
mark。  回复  更多评论
  

只有注册用户登录后才能发表评论。


网站导航: