Common MySQL Complex query statement writing-Non-original (original author: Pineapple Elephant)

Source: Internet
Author: User

The previous article on some of the MySQL more commonly used SQL statement writing, this document, the usual may use the complex point of the query statement.
Complex query statements are commonly found in report queries, elephants before the "use of JXL to generate complex reports analysis and Design (ii)" in the said, to deal with complex reports, the first need to understand its business relations, this is very important, if you do not understand the business to start to do, and finally certainly there will be problems.
After the business relationship is clear, the report can be decomposed to see exactly what data to prepare, according to the data and corresponding business relations to write SQL statements, step by step, you can achieve the required functionality. You should see that the following two examples are query statements, but if we precede insert into TABLE (COLUMN1, Column2,column3,...) Statement, we can insert data into the statistical results table, so that every time you query, you can only check a table on the line, so efficiency is not improved a lot?

Querying project data and sales statistics

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
(
--Query includes Account Manager, department head, company leader three types of all branches of the staff
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
--Query Department name
Left JOIN Se_organiz f on a.org_id = f.org_id
-Contract number and contract amount
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
--signing and payment of the contract and the amount of the return
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
--Total return contract number and Total return payment amount
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


Project Data Mom

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
' National ' 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
' National ' 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
) KThis is the original pineapple elephant, if you want to reprint please indicate the source. Http://www.blogjava.net/bolo This address: http://www.blogjava.net/bolo/archive/2015/02/02/422649.html

Common MySQL complex query sentence writing-non-original (original pineapple elephant)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.