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)
