SELECT DISTINCT
G. ` name ` OrgName,
Production. ` Name ` ProductionName,
Contract. ` STATUS `,
KvStatus. V statusName,
Contract. ContractNo,
U.n ame receiveUserName,
Contract. ReceiveUserId,
ApplicationUser. Name ApplicationUserName,
O. chua ustomerId customerId,
O. chua ustomerName customerName,
O.m oney money,
DATE_FORMAT (o.P ayTime, 'Y - m - % d % %) payTime,
C. ` salesmanName ` salesmanName,
Contract. SignedStatus,
KvSignStatus. V signedStatusName
The FROM
Sale_contract contract
Left the JOIN crm_production production on production. The state=0 and contract. ProductionId=production. Id
Left the join crm_salemangroup g on g.s Tate=0 and g.id=contract.Org Id
LEFT the JOIN system_user u on u.s Tate=0 and u.i d=contract. ReceiveUserId
LEFT the JOIN sale_contractapplication ca on ca. Id=contract, ApplicationId
LEFT the JOIN system_user applicationUser on applicationUser. State=0 and applicationUser. Id=ca. ApplicationUserId
Left the join crm_order o on grown Tate=0 and o. chua ontractNo=contract. ContractNo
Left the join view_salesman_customer_distribution c on o. chua ustomerId=Arthur c. ustomerId
Left the join system_kv kvSignStatus on kvSignStatus. GroupName='Contract_Signed_Status' and kvSignStatus. K=contract. SignedStatus
LEFT the JOIN system_kv kvStatus on kvStatus. GroupName='Contract_Status' and kvStatus. K=contract. ` STATUS `
WHERE
1=1
AND contract. The state=0
The order by contract. OperateTime DESC
CodePudding user response:
1, the first to look at this statement how long it takes to query data quantity how much2, looked from the statement distinct may slow down make statements:
To view all left connection table and associated conditions of the main table is the only, not only need to be processed into unique value;
Whether you need the main table to heavy
3, if there is a filter condition, appropriate can be indexed,
CodePudding user response:
SELECT DISTINCT
G. ` name ` OrgName,
Production. ` Name ` ProductionName,
Contract. ` STATUS `,
KvStatus. V statusName,
Contract. ContractNo,
U.N AME receiveUserName,
Contract. ReceiveUserId,
ApplicationUser. NAME ApplicationUserName,
O. chua ustomerId customerId,
O. chua ustomerName customerName,
O.m oney money,
DATE_FORMAT (o.P ayTime, 'Y - m - % d % %) payTime,
C. ` salesmanName ` salesmanName,
Contract. SignedStatus,
KvSignStatus. V signedStatusName
The FROM
Sale_contract contract
LEFT the JOIN crm_production production ON production. The state=0
AND contract. ProductionId=production. Id
ON the LEFT JOIN crm_salemangroup g g.s Tate=0
AND g.i d=contract.Org Id
LEFT the JOIN system_user u ON u.s Tate=0
AND u.i d=contract. ReceiveUserId
LEFT the JOIN sale_contractapplication ca ON ca. Id=contract, ApplicationId
LEFT the JOIN system_user applicationUser ON applicationUser. State=0
AND applicationUser. Id=ca. ApplicationUserId
LEFT the JOIN crm_order o ON grown Tate=0
AND o. chua ontractNo=contract. ContractNo
LEFT the JOIN (
SELECT DISTINCT
` s `. ` NAME ` AS ` salesmanName `,
` c `. ` id ` AS ` customerId `
The FROM
(
` crm_customerpersonal ` ` ` c
LEFT the JOIN (
SELECT
` g `. ` Name ` AS ` GroupName `,
` sg `. ` defaultGroup ` AS ` defaultGroup `,
` u `. ` Id ` AS ` Id `,
` u `. ` name ` AS ` name `,
` u `. ` mobile ` AS ` mobile `,
` u `. ` staffCode ` AS ` staffCode `,
Concat (' S ', ` u `. ` staffCode `) AS ` referralCode `,
` u `. ` password ` AS ` password `
The FROM
(
(` system_user ` ` u ` LEFT JOIN ` crm_saleman_salemangroup ` ` sg ` ON ((` sg `. ` saleManId `=` u `. ` Id `)))
LEFT the JOIN ` crm_salemangroup ` ` g ` ON (((` g `. ` State `=0) AND (` g `. ` Id `=` sg `. ` saleManGroupId `)))
)
WHERE
((` u `. ` state `=0) AND (` sg `. ` defaultGroup `=1) AND (` u `. ` Status `='9662'))
) ` s ` ON ((` s `. ` ` id=` c `. ` salesmanId `))
)
WHERE
((1=1) AND (c ` `. ` state `=0) AND (` s `. ` ` id=` c `. ` salesmanId `)) UNION ALL
SELECT DISTINCT
` s `. ` NAME ` AS ` salesmanName `,
` c `. ` id ` AS ` customerId `
The FROM
(
` crm_customerinstitution ` ` ` c
LEFT the JOIN (
SELECT
` g `. ` Name ` AS ` GroupName `,
` sg `. ` defaultGroup ` AS ` defaultGroup `,
` u `. ` Id ` AS ` Id `,
` u `. ` name ` AS ` name `,
` u `. ` mobile ` AS ` mobile `,
` u `. ` staffCode ` AS ` staffCode `,
Concat (' S ', ` u `. ` staffCode `) AS ` referralCode `,
` u `. ` password ` AS ` password `
The FROM
(
(` system_user ` ` u ` LEFT JOIN ` crm_saleman_salemangroup ` ` sg ` ON ((` sg `. ` saleManId `=` u `. ` Id `)))
LEFT the JOIN ` crm_salemangroup ` ` g ` ON (((` g `. ` State `=0) AND (` g `. ` Id `=` sg `. ` saleManGroupId `)))
)
WHERE
((` u `. ` state `=0) AND (` sg `. ` defaultGroup `=1) AND (` u `. ` Status `='9662'))
) ` s ` ON ((` s `. ` ` id=` c `. ` salesmanId `))
)
WHERE
((1=1) AND (c ` `. ` state `=0) AND (` s `. ` ` id=` c `. ` salesmanId `))
C) ON o. chua ustomerId=Arthur c. ustomerId
LEFT the JOIN system_kv kvSignStatus ON kvSignStatus. GroupName='Contract_Signed_Status'
AND kvSignStatus. K=contract. SignedStatus
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull