----统计不同部门，不同时间发送短信量（注：没条短信内容大于70字算两条短信，短信状态-3（发送成功）短信状态-3 ----（发送成功）短信状态-4（接收成功））、 短信状态-4（接收失败） ----这是为了统计总共花费的钱数
SELECT E.*, NVL(F.COUNT_5, 0) AS COUNT_5
FROM (SELECT C.*, NVL(D.COUNT_4, 0) AS COUNT_4
FROM (select A.ORG_NO, A.COUNT, B.COUNT_3
from ((SELECT ORG_NO, SUM(COUNT_NUM) AS COUNT
FROM (SELECT T.ORG_NO, COUNT(*) AS COUNT_NUM
FROM SMS_MESSAGE T
WHERE T.SMS_STATE IN ('3', '4', '5')
AND T.ORG_NO = '1357'
AND LENGTH(T.CONTENT) > 0
AND LENGTH(T.CONTENT) <= 70
AND T.SENDTIME > '2010-03-26'
AND T.SENDTIME <= '2012-04-26'
GROUP BY T.ORG_NO
UNION ALL
SELECT T.ORG_NO, COUNT(*) * 2 AS COUNT_NUM
FROM SMS_MESSAGE T
WHERE T.SMS_STATE IN ('3', '4', '5')
AND T.ORG_NO = '1357'
AND LENGTH(T.CONTENT) > 70
AND LENGTH(T.CONTENT) <= 140
AND T.SENDTIME > '2010-03-26'
AND T.SENDTIME <= '2012-04-26'
GROUP BY T.ORG_NO
UNION ALL
SELECT T.ORG_NO, COUNT(*) * 3 AS COUNT_NUM
FROM SMS_MESSAGE T
WHERE T.SMS_STATE IN ('3', '4', '5')
AND T.ORG_NO = '1357'
AND LENGTH(T.CONTENT) > 140
AND LENGTH(T.CONTENT) <= 210
AND T.SENDTIME > '2010-03-26'
AND T.SENDTIME <= '2012-04-26'
GROUP BY T.ORG_NO
UNION ALL
SELECT T.ORG_NO, COUNT(*) * 4 AS COUNT_NUM
FROM SMS_MESSAGE T
WHERE T.SMS_STATE IN ('3', '4', '5')
AND T.ORG_NO = '1357'
AND LENGTH(T.CONTENT) > 210
AND LENGTH(T.CONTENT) <= 280
AND T.SENDTIME > '2010-03-26'
AND T.SENDTIME <= '2012-04-26'
GROUP BY T.ORG_NO)
GROUP BY ORG_NO
ORDER BY ORG_NO) A LEFT JOIN
(SELECT ORG_NO, SUM(COUNT_NUM) AS COUNT_3
FROM (SELECT T.ORG_NO, COUNT(*) AS COUNT_NUM
FROM SMS_MESSAGE T
WHERE T.SMS_STATE = '3'
AND T.ORG_NO = '1357'
AND LENGTH(T.CONTENT) > 0
AND LENGTH(T.CONTENT) <= 70
AND T.SENDTIME > '2010-03-26'
AND T.SENDTIME <= '2012-04-26'
GROUP BY T.ORG_NO
UNION ALL
SELECT T.ORG_NO, COUNT(*) * 2 AS COUNT_NUM
FROM SMS_MESSAGE T
WHERE T.SMS_STATE = '3'
AND T.ORG_NO = '1357'
AND LENGTH(T.CONTENT) > 70
AND LENGTH(T.CONTENT) <= 140
AND T.SENDTIME > '2010-03-26'
AND T.SENDTIME <= '2012-04-26'
GROUP BY T.ORG_NO
UNION ALL
SELECT T.ORG_NO, COUNT(*) * 3 AS COUNT_NUM
FROM SMS_MESSAGE T
WHERE T.SMS_STATE = '3'
AND T.ORG_NO = '1357'
AND LENGTH(T.CONTENT) > 140
AND LENGTH(T.CONTENT) <= 210
AND T.SENDTIME > '2010-03-26'
AND T.SENDTIME <= '2012-04-26'
GROUP BY T.ORG_NO
UNION ALL
SELECT T.ORG_NO, COUNT(*) * 4 AS COUNT_NUM
FROM SMS_MESSAGE T
WHERE T.SMS_STATE = '3'
AND T.ORG_NO = '1357'
AND LENGTH(T.CONTENT) > 210
AND LENGTH(T.CONTENT) <= 280
AND T.SENDTIME > '2010-03-26'
AND T.SENDTIME <= '2012-04-26'
GROUP BY T.ORG_NO)
GROUP BY ORG_NO
ORDER BY ORG_NO) B ON A.ORG_NO = B.ORG_NO)) C
LEFT JOIN (SELECT ORG_NO, SUM(COUNT_NUM) AS COUNT_4
FROM (SELECT T.ORG_NO, COUNT(*) AS COUNT_NUM
FROM SMS_MESSAGE T
WHERE T.SMS_STATE = '4'
AND T.ORG_NO = '1357'
AND LENGTH(T.CONTENT) > 0
AND LENGTH(T.CONTENT) <= 70
AND T.SENDTIME > '2010-03-26'
AND T.SENDTIME <= '2012-04-26'
GROUP BY T.ORG_NO
UNION ALL
SELECT T.ORG_NO, COUNT(*) * 2 AS COUNT_NUM
FROM SMS_MESSAGE T
WHERE T.SMS_STATE = '4'
AND T.ORG_NO = '1357'
AND LENGTH(T.CONTENT) > 70
AND LENGTH(T.CONTENT) <= 140
AND T.SENDTIME > '2010-03-26'
AND T.SENDTIME <= '2012-04-26'
GROUP BY T.ORG_NO
UNION ALL
SELECT T.ORG_NO, COUNT(*) * 3 AS COUNT_NUM
FROM SMS_MESSAGE T
WHERE T.SMS_STATE = '4'
AND T.ORG_NO = '1357'
AND LENGTH(T.CONTENT) > 140
AND LENGTH(T.CONTENT) <= 210
AND T.SENDTIME > '2010-03-26'
AND T.SENDTIME <= '2012-04-26'
GROUP BY T.ORG_NO
UNION ALL
SELECT T.ORG_NO, COUNT(*) * 4 AS COUNT_NUM
FROM SMS_MESSAGE T
WHERE T.SMS_STATE = '4'
AND T.ORG_NO = '1357'
AND LENGTH(T.CONTENT) > 210
AND LENGTH(T.CONTENT) <= 280
AND T.SENDTIME > '2010-03-26'
AND T.SENDTIME <= '2012-04-26'
GROUP BY T.ORG_NO)
GROUP BY ORG_NO
ORDER BY ORG_NO) D ON C.ORG_NO = D.ORG_NO) E
LEFT JOIN (SELECT ORG_NO, SUM(COUNT_NUM) AS COUNT_5
FROM (SELECT T.ORG_NO, COUNT(*) AS COUNT_NUM
FROM SMS_MESSAGE T
WHERE T.SMS_STATE = '5'
AND T.ORG_NO = '1357'
AND LENGTH(T.CONTENT) > 0
AND LENGTH(T.CONTENT) <= 70
AND T.SENDTIME > '2010-03-26'
AND T.SENDTIME <= '2012-04-26'
GROUP BY T.ORG_NO
UNION ALL
SELECT T.ORG_NO, COUNT(*) * 2 AS COUNT_NUM
FROM SMS_MESSAGE T
WHERE T.SMS_STATE = '5'
AND T.ORG_NO = '1357'
AND LENGTH(T.CONTENT) > 70
AND LENGTH(T.CONTENT) <= 140
AND T.SENDTIME > '2010-03-26'
AND T.SENDTIME <= '2012-04-26'
GROUP BY T.ORG_NO
UNION ALL
SELECT T.ORG_NO, COUNT(*) * 3 AS COUNT_NUM
FROM SMS_MESSAGE T
WHERE T.SMS_STATE = '5'
AND T.ORG_NO = '1357'
AND LENGTH(T.CONTENT) > 140
AND LENGTH(T.CONTENT) <= 210
AND T.SENDTIME > '2010-03-26'
AND T.SENDTIME <= '2012-04-26'
GROUP BY T.ORG_NO
UNION ALL
SELECT T.ORG_NO, COUNT(*) * 4 AS COUNT_NUM
FROM SMS_MESSAGE T
WHERE T.SMS_STATE = '5'
AND T.ORG_NO = '1357'
AND LENGTH(T.CONTENT) > 210
AND LENGTH(T.CONTENT) <= 280
AND T.SENDTIME > '2010-03-26'
AND T.SENDTIME <= '2012-04-26'
GROUP BY T.ORG_NO)
GROUP BY ORG_NO
ORDER BY ORG_NO) F ON E.ORG_NO = F.ORG_NO
分享到：
相关推荐
结合使用`LEFT JOIN`和`UNION ALL`，我们可以构造一个复杂的查询，首先使用`LEFT JOIN`从文章表中获取数据，并关联到分类表以获取对应的分类名称，然后通过`UNION ALL`将不同条件下的查询结果整合在一起。...
SQL Left Join SQL Right Join SQL Full Join SQL Union SQL Select Into SQL Create DB SQL Create Table SQL Constraints SQL Not Null SQL Unique SQL Primary Key SQL Foreign Key SQL Check SQL ...
当然，优化SQL查询不仅仅是使用`JOIN ALL`策略，还有其他方法可以考虑，如创建更高效的索引、使用`UNION ALL`替代`OR`（如果条件互斥）、考虑数据模型的调整等。同时，理解查询计划和使用查询分析器也是优化查询的...
2. LEFT JOIN：返回table1的所有行，即使在table2中没有匹配的行：`SELECT column_name FROM table1 LEFT JOIN table2 ON table1.column = table2.column` 3. RIGHT JOIN：返回table2的所有行，即使在table1中没有...
- **LEFT JOIN (或 LEFT OUTER JOIN)**：返回左表的所有行，即使右表没有匹配的行。 - **RIGHT JOIN (或 RIGHT OUTER JOIN)**：返回右表的所有行，即使左表没有匹配的行。 - **FULL JOIN (或 FULL OUTER JOIN)**...
- **组合查询**：通过UNION或UNION ALL组合多个查询的结果集，或者使用子查询进行更复杂的查询操作。 - **视图**：创建视图作为虚拟表，可以简化复杂查询，并提供安全层面的访问控制。 4. **数据控制、导入导出、...
2. **子查询与联接**：讲解如何使用子查询进行嵌套查询，以及各种类型的联接操作，如内连接（INNER JOIN）、外连接（LEFT JOIN、RIGHT JOIN、FULL JOIN）和交叉连接（CROSS JOIN）。 3. **集合操作**：可能涉及到...
在PL/SQL（Oracle数据库的编程语言）中，JOIN语法尤其重要，因为它是处理复杂查询的基础。以下是PL/SQL中不同类型的JOIN及其特点的详细分析： 1. **左连接（LEFT JOIN）**： 左连接返回左表（Aa）的所有行，即使在...
常见的类型有内连接（`INNER JOIN`）、左连接（`LEFT JOIN`）、右连接（`RIGHT JOIN`）和全外连接（`FULL OUTER JOIN`）。 8. **分页查询**：通过`OFFSET`和`FETCH NEXT`关键字实现分页，例如`SELECT * FROM 表名 ...
MySQL不直接支持`FULL OUTER JOIN`，但可以通过结合`UNION ALL`和`LEFT JOIN`或`RIGHT JOIN`来实现。例如： ``` SELECT A.*, B.* FROM A FULL OUTER JOIN B ON A.ID = B.ID ``` 在MySQL中，这可能需要写成： ``...
UNION和INTERSECT用于合并多个SELECT语句的结果，UNION ALL保留重复行，INTERSECT返回两个查询的交集。 ```sql SELECT column_name FROM table1 UNION SELECT column_name FROM table2; ``` 六、在Java中的应用 在...
这一主题涵盖了SQL中的JOIN操作、模糊查询（LIKE语句）以及ORDER BY排序，是数据库管理与数据分析领域的重要技能。 ### SQL联合查询（UNION） 在SQL中，联合查询（UNION）允许我们合并两个或更多SELECT语句的结果...
`select_statement UNION [ALL] selectstatement`将两个或更多查询的结果集合并。UNION ALL包含所有行，而UNION去除重复行。所有查询的列数和数据类型必须匹配，列标题由第一个查询决定。排序需基于第一个查询的列名...
通过上面的示例，我们可以看到JOIN操作是基于条件将两个表的数据融合在一起，而UNION操作是将两个独立的查询结果合并成一个单一的结果集，消除重复（除非使用UNION ALL）。在处理多表数据时，JOIN更适合于分析和关联...
6. 连接查询：使用INNER JOIN、LEFT JOIN、RIGHT JOIN等关键字来联合两个或多个表的数据。 7. 复杂查询：利用UNION、EXCEPT等操作符合并或比较不同查询的结果集。 8. 视图操作：通过创建和查询视图来简化复杂查询或...
- `UNION`, `UNION ALL`, `INTERSECT`, `MINUS`用于合并或对比多个查询的结果。 10. **索引与优化** - 索引可加快查询速度，`CREATE INDEX index_name ON table_name(column)`创建索引。 - 使用`EXPLAIN PLAN`...
根据提供的标题、描述以及部分示例内容，我们可以深入探讨高级SQL查询技术，特别是聚合函数查询、组合查询（如UNION）以及其他复杂查询等主题。 ### SQL聚合函数查询 聚合函数在SQL中主要用于对数据进行汇总操作，...
- 使用 `JOIN` 语句来连接多张表，可以使用 `INNER JOIN`, `LEFT JOIN`, `RIGHT JOIN` 等。 - 示例：`SELECT u.username, c.cityid FROM usertable u JOIN citytable c ON u.cityid = c.cityid;` 6. **子查询：**...
如`SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;` 10. **INNER JOIN**：仅返回两个表中匹配的记录，如`SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;` ...
- `LEFT JOIN`（或`LEFT OUTER JOIN`）：返回所有左表的行，即使在右表中没有匹配，如`SELECT * FROM table1 LEFT JOIN table2 ON table1.key = table2.key;` - `RIGHT JOIN`（或`RIGHT OUTER JOIN`）：返回所有右...