MySQL的SQL语句写法

INSERT INTO T2 (C1,C2) SELECT C1,C2 FROM T1 [WHERE C1 = XX AND C2 = XX ORDER BY C1]

UPDATE T1 AS A, T2 AS B SET A.NAME = B.NAME WHERE A.TID = B.ID

UPDATE T_ROLE_USER AS A,
 (
    SELECT
        ID
    FROM
        T_USER
    WHERE
        DEPARTID IN (
            SELECT
                ID
            FROM
                T_DEPART
            WHERE
                LENGTH(ORG_CODE) = 9
        )
) AS B
SET A.ROLEID = &＃39;123456&＃39;
WHERE
    A.USERID = B.ID

UPDATE T_DEPART AS A,
 (
    SELECT
        ID,
        SUBSTRING(ORG_CODE, 1, 6) ORG_CODE
    FROM
        T_DEPART
    WHERE
        LENGTH(ORG_CODE) = 8
    AND PARENT_DEPART_ID IS NOT NULL
) AS B
SET A.PARENT_DEPART_ID = B.ID
WHERE
    SUBSTRING(A.ORG_CODE, 1, 6) = B.ORG_CODE

DELETE A,B FROM T1 AS A LEFT JOIN T2 AS B ON A.TID = B.ID WHERE B.NAME IS NULL

INSERT INTO SE_STAT_ORG (
    RECORD_DATE,
    ORG_ID,
    ORG_NAME,
    SIGN_CONT_COUNT,
    SIGN_ARRI_CONT_COUNT,
    SIGN_CONT_MONEY,
    SIGN_ARRI_CONT_MONEY,
    TOTAL_ARRI_CONT_COUNT,
    TOTAL_ARRI_MONEY,
    PUBLISH_TOTAL_COUNT,
    PROJECT_COUNT
) SELECT
    *
FROM
    (
        SELECT
            &＃39;2012-06-09&＃39; RECORD_DATE,
            PARENT_ORG_ID,
            PARENT_ORG_NAME,
            SUM(SIGN_CONT_COUNT) SIGN_CONT_COUNT,
            SUM(SIGN_ARRI_CONT_COUNT) SIGN_ARRI_CONT_COUNT,
            SUM(SIGN_CONT_MONEY) SIGN_CONT_MONEY,
            SUM(SIGN_ARRI_CONT_MONEY) SIGN_ARRI_CONT_MONEY,
            SUM(TOTAL_ARRI_CONT_COUNT) TOTAL_ARRI_CONT_COUNT,
            SUM(TOTAL_ARRI_MONEY) TOTAL_ARRI_MONEY,
            SUM(PUBLISH_TOTAL_COUNT) PUBLISH_TOTAL_COUNT,
            SUM(PROJECT_COUNT) PROJECT_COUNT,
        FROM SE_STAT_USER
        WHERE DATE_FORMAT(RECORD_DATE, &＃39;%Y-%m-%d&＃39;) = &＃39;2012-06-09&＃39;
        GROUP BY PARENT_ORG_ID
    ) M

UPDATE SE_STAT_USER A,
 (
    SELECT
        USER_ID,
        SUM(INVEST_ORG_COUNT + FINANCIAL_ORG_COUNT + INTERMEDIARY_ORG_COUNT + ENTERPRISE_COUNT) AS COMMON_COUNT
    FROM SE_STAT_USER
    WHERE DATE_FORMAT(RECORD_DATE, &＃39;%Y-%m-%d&＃39;) = &＃39;2012-06-09&＃39;
    GROUP BY USER_ID
) B,
 (
    SELECT
        USER_ID,
        SUM(ESTABLISH_COUNT + STOCK_COUNT + MERGER_COUNT + ACHIEVE_COUNT) AS PROJECT_COUNT
    FROM SE_STAT_USER
    WHERE DATE_FORMAT(RECORD_DATE, &＃39;%Y-%m-%d&＃39;) = &＃39;2012-06-09&＃39;
    GROUP BY USER_ID
) C
SET A.COMMON_COUNT = B.COMMON_COUNT, A.PROJECT_COUNT = C.PROJECT_COUNT
WHERE A.USER_ID = B.USER_ID
AND A.USER_ID = C.USER_ID
AND DATE_FORMAT(A.RECORD_DATE, &＃39;%Y-%m-%d&＃39;) = &＃39;2012-06-09&＃39;

UPDATE SE_STAT_USER A,
 (
    SELECT
        P.CHANNEL,
        COUNT(P.CONT_ID) AS CONT_COUNT,
        C.CUST_MGR_ID
    FROM
        (
            SELECT
                CHANNEL,
                CONT_ID
            FROM SK_PROJECT
            WHERE PROJECT_STATUS = 6
            AND DATE_FORMAT(AUDIT_TIME, &＃39;%Y-%m-%d&＃39;) = &＃39;2012-06-11&＃39;
        ) p
    INNER JOIN SE_CONTRACT C ON P.CONT_ID = C.CONT_ID
    GROUP BY P.CHANNEL, C.CUST_MGR_ID
) B
SET
    A.STOCK_COUNT = CASE WHEN B.CHANNEL = 2 THEN B.CONT_COUNT ELSE 0 END,
    A.ESTABLISH_COUNT = CASE WHEN B.CHANNEL = 3 THEN B.CONT_COUNT ELSE 0 END,
    A.ACHIEVE_COUNT = CASE WHEN B.CHANNEL = 4 THEN B.CONT_COUNT ELSE 0 END, 
    A.BRAND_COUNT = CASE WHEN B.CHANNEL = 5 THEN B.CONT_COUNT ELSE 0 END,
    A.MERGER_COUNT = CASE WHEN B.CHANNEL = 6 THEN B.CONT_COUNT ELSE 0 END
WHERE
    A.USER_ID = B.CUST_MGR_ID
AND DATE_FORMAT(A.RECORD_DATE, &＃39;%Y-%m-%d&＃39;) = &＃39;2012-06-11&＃39;

ALTER TABLE PROJECT ADD INDEX INDEX_USER_ID (USER_ID),
        ADD INDEX INDEX_PROJECT_STATUS (PROJECT_STATUS);

ALTER TABLE PROJECT DROP COLUMN PROJECT_STATUS,
        DROP COLUMN EXPECT_RETURN,DROP COLUMN CURRENCY;

ALTER TABLE PROJECT 
        ADD COLUMN DICT_ID INT DEFAULT NULL COMMENT &＃39;xxx&＃39; AFTER PROJECT_SITE,
        ADD COLUMN INTRODUCE TEXT DEFAULT NULL COMMENT &＃39;xx&＃39; AFTER DICT_ID,
        ADD COLUMN STAGE INT DEFAULT NULL COMMENT &＃39;xx&＃39; AFTER ID,
        ADD COLUMN ATTACH_URI VARCHAR(8) DEFAULT NULL COMMENT &＃39;xxx&＃39; AFTER INTRODUCE;

ALTER TABLE PROJECT CHANGE DICT_ID DICT_ID1 INT NOT NULL,
        MODIFY PROJECT_STATUS TINYINT NOT NULL COMMENT &＃39;xxx&＃39;;

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.`LEVEL` IN (1, 2, 3)
        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, &＃39;%Y-%m-%d&＃39;) = &＃39;2012-06-08&＃39;
    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, &＃39;%Y-%m-%d&＃39;) = &＃39;2012-06-08&＃39;
    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, &＃39;%Y-%m-%d&＃39;) = &＃39;2012-06-08&＃39;
    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) * 100, 2),
    0
) RELATIVE_PUBLISH_RATIO,
IF (
    k.LAST_PROJECT_COUNT > 0,
    ROUND((k.RISE_PROJECT_COUNT / k.LAST_PROJECT_COUNT) * 100, 2),
    0
) RELATIVE_PROJECT_RATIO,
IF (
    k.LAST_COMMON_COUNT > 0,
    ROUND((k.RISE_COMMON_COUNT / k.LAST_COMMON_COUNT) * 100, 2),
    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
                    &＃39;全国&＃39; 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, &＃39;%Y-%m&＃39;) = &＃39;2012-07&＃39;
            ) m
        LEFT JOIN (
            SELECT
                &＃39;全国&＃39; 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, &＃39;%Y-%m&＃39;) = &＃39;2012-06&＃39;
        ) 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, &＃39;%Y-%m&＃39;) = &＃39;2012-07&＃39;
                    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, &＃39;%Y-%m&＃39;) = &＃39;2012-06&＃39;
                GROUP BY
                    ORG_ID
            ) b ON a.ORG_ID = b.ORG_ID
    ) k

MySQL的SQL语句写法，除了那些基本的之外，还有一些也算比较常用的，这里记录下来，以便以后查找。
好记性不如烂笔头，这话说的太有道理了，一段时间不写它，还真容易忘记。大家不要纠结这些SQL语句包含的业务或是其它问题，本文只是一篇笔记而已。

1.将数据从T1表导入到T2表

INSERT INTO T2 (C1,C2) SELECT C1,C2 FROM T1 [WHERE C1 = XX AND C2 = XX ORDER BY C1]

2.使用T2表的NAME来更新T1表的NAME

UPDATE T1 AS A, T2 AS B SET A.NAME = B.NAME WHERE A.TID = B.ID

3.两表的关联更新

UPDATE T_ROLE_USER AS A,(SELECTIDFROMT_USERWHEREDEPARTID IN (SELECTIDFROMT_DEPARTWHERELENGTH(ORG_CODE) = 9)
) AS B
SET A.ROLEID = &＃39;123456&＃39;
WHEREA.USERID = B.ID

4.自己和自己关联更新

UPDATE T_DEPART AS A,(SELECTID,SUBSTRING(ORG_CODE, 1, 6) ORG_CODEFROMT_DEPARTWHERELENGTH(ORG_CODE) = 8AND PARENT_DEPART_ID IS NOT NULL
) AS B
SET A.PARENT_DEPART_ID = B.ID
WHERESUBSTRING(A.ORG_CODE, 1, 6) = B.ORG_CODE

5.两表关联删除，将删除两表中有关联ID并且T2表NAME为空的两表记录

DELETE A,B FROM T1 AS A LEFT JOIN T2 AS B ON A.TID = B.ID WHERE B.NAME IS NULL

6.将统计结果插入到表

作者： IT程序狮
链接：http://www.imooc.com/article/1339?from=itblog
来源：慕课网

MySQL的SQL语句写法，除了那些基本的之外，还有一些也算比较常用的，这里记录下来，以便以后查找。
好记性不如烂笔头，这话说的太有道理了，一段时间不写它，还真容易忘记。大家不要纠结这些SQL语句包含的业务或是其它问题，本文只是一篇笔记而已。

1.将数据从T1表导入到T2表

INSERT INTO T2 (C1,C2) SELECT C1,C2 FROM T1 [WHERE C1 = XX AND C2 = XX ORDER BY C1]

2.使用T2表的NAME来更新T1表的NAME

UPDATE T1 AS A, T2 AS B SET A.NAME = B.NAME WHERE A.TID = B.ID

3.两表的关联更新

UPDATE T_ROLE_USER AS A,(SELECTIDFROMT_USERWHEREDEPARTID IN (SELECTIDFROMT_DEPARTWHERELENGTH(ORG_CODE) = 9)
) AS B
SET A.ROLEID = &＃39;123456&＃39;
WHEREA.USERID = B.ID

4.自己和自己关联更新

UPDATE T_DEPART AS A,(SELECTID,SUBSTRING(ORG_CODE, 1, 6) ORG_CODEFROMT_DEPARTWHERELENGTH(ORG_CODE) = 8AND PARENT_DEPART_ID IS NOT NULL
) AS B
SET A.PARENT_DEPART_ID = B.ID
WHERESUBSTRING(A.ORG_CODE, 1, 6) = B.ORG_CODE

5.两表关联删除，将删除两表中有关联ID并且T2表NAME为空的两表记录

DELETE A,B FROM T1 AS A LEFT JOIN T2 AS B ON A.TID = B.ID WHERE B.NAME IS NULL

6.将统计结果插入到表

作者： IT程序狮
链接：http://www.imooc.com/article/1339?from=itblog
来源：慕课网

MySQL的SQL语句写法，除了那些基本的之外，还有一些也算比较常用的，这里记录下来，以便以后查找。
好记性不如烂笔头，这话说的太有道理了，一段时间不写它，还真容易忘记。大家不要纠结这些SQL语句包含的业务或是其它问题，本文只是一篇笔记而已。

1.将数据从T1表导入到T2表

INSERT INTO T2 (C1,C2) SELECT C1,C2 FROM T1 [WHERE C1 = XX AND C2 = XX ORDER BY C1]

2.使用T2表的NAME来更新T1表的NAME

UPDATE T1 AS A, T2 AS B SET A.NAME = B.NAME WHERE A.TID = B.ID

3.两表的关联更新

UPDATE T_ROLE_USER AS A,(SELECTIDFROMT_USERWHEREDEPARTID IN (SELECTIDFROMT_DEPARTWHERELENGTH(ORG_CODE) = 9)
) AS B
SET A.ROLEID = &＃39;123456&＃39;
WHEREA.USERID = B.ID

4.自己和自己关联更新

UPDATE T_DEPART AS A,(SELECTID,SUBSTRING(ORG_CODE, 1, 6) ORG_CODEFROMT_DEPARTWHERELENGTH(ORG_CODE) = 8AND PARENT_DEPART_ID IS NOT NULL
) AS B
SET A.PARENT_DEPART_ID = B.ID
WHERESUBSTRING(A.ORG_CODE, 1, 6) = B.ORG_CODE

5.两表关联删除，将删除两表中有关联ID并且T2表NAME为空的两表记录

DELETE A,B FROM T1 AS A LEFT JOIN T2 AS B ON A.TID = B.ID WHERE B.NAME IS NULL

6.将统计结果插入到表

作者： IT程序狮
链接：http://www.imooc.com/article/1339?from=itblog
来源：慕课网

MySQL的SQL语句写法，除了那些基本的之外，还有一些也算比较常用的，这里记录下来，以便以后查找。
好记性不如烂笔头，这话说的太有道理了，一段时间不写它，还真容易忘记。大家不要纠结这些SQL语句包含的业务或是其它问题，本文只是一篇笔记而已。

1.将数据从T1表导入到T2表

INSERT INTO T2 (C1,C2) SELECT C1,C2 FROM T1 [WHERE C1 = XX AND C2 = XX ORDER BY C1]

2.使用T2表的NAME来更新T1表的NAME

UPDATE T1 AS A, T2 AS B SET A.NAME = B.NAME WHERE A.TID = B.ID

3.两表的关联更新

UPDATE T_ROLE_USER AS A,(SELECTIDFROMT_USERWHEREDEPARTID IN (SELECTIDFROMT_DEPARTWHERELENGTH(ORG_CODE) = 9)
) AS B
SET A.ROLEID = &＃39;123456&＃39;
WHEREA.USERID = B.ID

4.自己和自己关联更新

UPDATE T_DEPART AS A,(SELECTID,SUBSTRING(ORG_CODE, 1, 6) ORG_CODEFROMT_DEPARTWHERELENGTH(ORG_CODE) = 8AND PARENT_DEPART_ID IS NOT NULL
) AS B
SET A.PARENT_DEPART_ID = B.ID
WHERESUBSTRING(A.ORG_CODE, 1, 6) = B.ORG_CODE

5.两表关联删除，将删除两表中有关联ID并且T2表NAME为空的两表记录

DELETE A,B FROM T1 AS A LEFT JOIN T2 AS B ON A.TID = B.ID WHERE B.NAME IS NULL

6.将统计结果插入到表

作者： IT程序狮
链接：http://www.imooc.com/article/1339?from=itblog
来源

MySQL的SQL语句写法，除了那些基本的之外，还有一些也算比较常用的，这里记录下来，以便以后查找。
好记性不如烂笔头，这话说的太有道理了，一段时间不写它，还真容易忘记。大家不要纠结这些SQL语句包含的业务或是其它问题，本文只是一篇笔记而已。

1.将数据从T1表导入到T2表

INSERT INTO T2 (C1,C2) SELECT C1,C2 FROM T1 [WHERE C1 = XX AND C2 = XX ORDER BY C1]

2.使用T2表的NAME来更新T1表的NAME

UPDATE T1 AS A, T2 AS B SET A.NAME = B.NAME WHERE A.TID = B.ID

3.两表的关联更新

UPDATE T_ROLE_USER AS A,(SELECTIDFROMT_USERWHEREDEPARTID IN (SELECTIDFROMT_DEPARTWHERELENGTH(ORG_CODE) = 9)
) AS B
SET A.ROLEID = &＃39;123456&＃39;
WHEREA.USERID = B.ID

4.自己和自己关联更新

UPDATE T_DEPART AS A,(SELECTID,SUBSTRING(ORG_CODE, 1, 6) ORG_CODEFROMT_DEPARTWHERELENGTH(ORG_CODE) = 8AND PARENT_DEPART_ID IS NOT NULL
) AS B
SET A.PARENT_DEPART_ID = B.ID
WHERESUBSTRING(A.ORG_CODE, 1, 6) = B.ORG_CODE

5.两表关联删除，将删除两表中有关联ID并且T2表NAME为空的两表记录

DELETE A,B FROM T1 AS A LEFT JOIN T2 AS B ON A.TID = B.ID WHERE B.NAME IS NULL

6.将统计结果插入到表

作者： IT程序狮
链接：http://www.imooc.com/article/1339?from=itblog
来源：慕课网