MySQL的SQL语句写法
来源:互联网 发布:数据库题库和答案2016 编辑:程序博客网 时间:2024/05/19 18:43
好记性不如烂笔头,这话说的太有道理了,一段时间不写它,还真容易忘记。大家不要纠结这些SQL语句包含的业务或是其它问题,本文只是一篇笔记而已。
将数据从T1表导入到T2表
使用T2表的NAME来更新T1表的NAME
两表的关联更新
(
SELECT
ID
FROM
T_USER
WHERE
DEPARTID IN (
SELECT
ID
FROM
T_DEPART
WHERE
LENGTH(ORG_CODE) = 9
)
) AS B
SET A.ROLEID = '123456'
WHERE
A.USERID = B.ID
自己和自己关联更新
(
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
两表关联删除,将删除两表中有关联ID并且T2表NAME为空的两表记录
将统计结果插入到表
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
'2012-06-09' 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, '%Y-%m-%d') = '2012-06-09'
GROUP BY PARENT_ORG_ID
) M
三表关联更新
(
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, '%Y-%m-%d') = '2012-06-09'
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, '%Y-%m-%d') = '2012-06-09'
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, '%Y-%m-%d') = '2012-06-09'
带条件的关联更新
(
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, '%Y-%m-%d') = '2012-06-11'
) 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, '%Y-%m-%d') = '2012-06-11'
加索引
ADD INDEX INDEX_PROJECT_STATUS (PROJECT_STATUS);
删除列
DROP COLUMN EXPECT_RETURN,DROP COLUMN CURRENCY;
增加列
ADD COLUMN DICT_ID INT DEFAULT NULL COMMENT 'xxx' AFTER PROJECT_SITE,
ADD COLUMN INTRODUCE TEXT DEFAULT NULL COMMENT 'xx' AFTER DICT_ID,
ADD COLUMN STAGE INT DEFAULT NULL COMMENT 'xx' AFTER ID,
ADD COLUMN ATTACH_URI VARCHAR(8) DEFAULT NULL COMMENT 'xxx' AFTER INTRODUCE;
修改列,一般用MODIFY修改数据类型,CHANGE修改列名
MODIFY PROJECT_STATUS TINYINT NOT NULL COMMENT 'xxx';
搞清楚业务关系后,就可以对报表进行分解,看看到底要准备什么数据,按照这些数据加上对应的业务关系来写SQL语句,一步一步做下来,就可以实现所需要的功能了。大家应该看到下面两个例子都是查询语句,但是如果我们在前面加上INSERT INTO TABLE (COLUMN1, COLUMN2,COLUMN3,...)语句,我们就可以将数据插入到统计结果表中,这样每次查询的时候,就可以只查一张表就行了,这样效率是不是提高了很多呢?
查询项目数据与销售统计
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, '%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
项目数据月度环比
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
'全国' 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
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, ( SELECT ID FROM T_USER WHERE DEPARTID IN ( SELECT ID FROM T_DEPART WHERE LENGTH(ORG_CODE) = 9 )) AS BSET A.ROLEID = '123456'WHERE A.USERID = B.ID
4.自己和自己关联更新
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 BSET A.PARENT_DEPART_ID = B.IDWHERE SUBSTRING(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, ( SELECT ID FROM T_USER WHERE DEPARTID IN ( SELECT ID FROM T_DEPART WHERE LENGTH(ORG_CODE) = 9 )) AS BSET A.ROLEID = '123456'WHERE A.USERID = B.ID
4.自己和自己关联更新
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 BSET A.PARENT_DEPART_ID = B.IDWHERE SUBSTRING(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, ( SELECT ID FROM T_USER WHERE DEPARTID IN ( SELECT ID FROM T_DEPART WHERE LENGTH(ORG_CODE) = 9 )) AS BSET A.ROLEID = '123456'WHERE A.USERID = B.ID
4.自己和自己关联更新
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 BSET A.PARENT_DEPART_ID = B.IDWHERE SUBSTRING(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, ( SELECT ID FROM T_USER WHERE DEPARTID IN ( SELECT ID FROM T_DEPART WHERE LENGTH(ORG_CODE) = 9 )) AS BSET A.ROLEID = '123456'WHERE A.USERID = B.ID
4.自己和自己关联更新
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 BSET A.PARENT_DEPART_ID = B.IDWHERE SUBSTRING(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, ( SELECT ID FROM T_USER WHERE DEPARTID IN ( SELECT ID FROM T_DEPART WHERE LENGTH(ORG_CODE) = 9 )) AS BSET A.ROLEID = '123456'WHERE A.USERID = B.ID
4.自己和自己关联更新
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 BSET A.PARENT_DEPART_ID = B.IDWHERE SUBSTRING(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语句写法示例--mysql
- mysql更新字段中部分内容的sql语句写法
- sql语句的多种写法
- 标准Sql语句的写法
- SQL循环语句 的写法
- sql 语句 的一些写法
- Mysql一个sql查询语句写法
- mysql 嵌套语句的写法
- Java的SQL语句的写法小结
- Oracle里的交叉SQL语句写法
- Oracle里的交叉SQL语句写法
- 动态SQL语句的一些常见写法
- 动态SQL语句的一些常见写法
- 投票类型相关sql语句的写法
- 三表左连接查询的sql语句写法
- 动态sql语句游标的写法
- T-SQL语句的几种写法
- 物理渲染-基于物理的光照模型
- JVM内存模型
- “朝阳群众”APP上线!解读怎么做一款深入群众的应用
- 同用的网站登陆界面
- sql 查询时异常为明确定义的列
- MySQL的SQL语句写法
- 原生js轮播图
- 如何在星巴克点一杯好喝的咖啡
- Java随机数
- 关于stack 以及相关的寄存器的知识(x86 结构)
- 《嵌入式Linux应用程序开发详解》PDF完整版及源代码
- 反射、反射加壳、反射脱壳、反射注册机
- 开发细节,持续更新...
- URI模板