SELECT DISTINCT
g.`name` OrgName,
production.`Name` ProductionName,
contract.`STATUS`,
kvStatus.v statusName,
contract.ContractNo,
u.name receiveUserName,
contract.receiveUserId,
applicationUser.name ApplicationUserName,
o.customerId customerId,
o.CustomerName customerName,
o.money money,
DATE_FORMAT(o.PayTime, '%Y-%m-%d') payTime,
c.`salesmanName` salesmanName,
contract.signedStatus,
kvSignStatus.v signedStatusName
FROM
sale_contract contract
left JOIN crm_production production on production.state=0 and contract.ProductionId=production.id
left join crm_salemangroup g on g.state=0 and g.id=contract.OrgId
LEFT JOIN system_user u on u.state=0 and u.id=contract.receiveUserId
LEFT JOIN sale_contractapplication ca on ca.id=contract.ApplicationId
LEFT JOIN system_user applicationUser on applicationUser.state=0 and applicationUser.id=ca.ApplicationUserId
left join crm_order o on o.state=0 and o.contractNo=contract.ContractNo
left join view_salesman_customer_distribution c on o.CustomerId=c.customerId
left join system_kv kvSignStatus on kvSignStatus.GroupName='Contract_Signed_Status' and kvSignStatus.k=contract.signedStatus
LEFT JOIN system_kv kvStatus on kvStatus.GroupName='Contract_Status' and kvStatus.k=contract.`STATUS`
WHERE
1 = 1
AND contract.state = 0
order by contract.operateTime DESC
uj5u.com熱心網友回復：
1、首先要看該陳述句查詢需要多長時間，資料量有多大2、從陳述句上看去重distinct 會讓陳述句變慢：
要查看所有左連接的表和主表的關聯條件是否唯一，不唯一的話需要處理成唯一值；
主表是否需要去重
3、如果有篩選條件，適當的可以建立索引。
uj5u.com熱心網友回復：
SELECT DISTINCT
g.`name` OrgName,
production.`Name` ProductionName,
contract.`STATUS`,
kvStatus.v statusName,
contract.ContractNo,
u.NAME receiveUserName,
contract.receiveUserId,
applicationUser.NAME ApplicationUserName,
o.customerId customerId,
o.CustomerName customerName,
o.money money,
DATE_FORMAT( o.PayTime, '%Y-%m-%d' ) payTime,
c.`salesmanName` salesmanName,
contract.signedStatus,
kvSignStatus.v signedStatusName
FROM
sale_contract contract
LEFT JOIN crm_production production ON production.state = 0
AND contract.ProductionId = production.id
LEFT JOIN crm_salemangroup g ON g.state = 0
AND g.id = contract.OrgId
LEFT JOIN system_user u ON u.state = 0
AND u.id = contract.receiveUserId
LEFT JOIN sale_contractapplication ca ON ca.id = contract.ApplicationId
LEFT JOIN system_user applicationUser ON applicationUser.state = 0
AND applicationUser.id = ca.ApplicationUserId
LEFT JOIN crm_order o ON o.state = 0
AND o.contractNo = contract.ContractNo
LEFT JOIN (
SELECT DISTINCT
`s`.`NAME` AS `salesmanName`,
`c`.`id` AS `customerId`
FROM
(
`crm_customerpersonal` `c`
LEFT 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`
FROM
(
( `system_user` `u` LEFT JOIN `crm_saleman_salemangroup` `sg` ON ( ( `sg`.`saleManId` = `u`.`Id` ) ) )
LEFT 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`
FROM
(
`crm_customerinstitution` `c`
LEFT 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`
FROM
(
( `system_user` `u` LEFT JOIN `crm_saleman_salemangroup` `sg` ON ( ( `sg`.`saleManId` = `u`.`Id` ) ) )
LEFT 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.CustomerId = c.customerId
LEFT JOIN system_kv kvSignStatus ON kvSignStatus.GroupName = 'Contract_Signed_Status'
AND kvSignStatus.k = contract.signedStatus
LEFT JOIN system_kv kvStatus ON kvStatus.GroupName = 'Contract_Status'
AND kvStatus.k = contract.`STATUS`
WHERE
1 = 1
AND contract.state = 0
ORDER BY
contract.operateTime DESC
uj5u.com熱心網友回復：
左連接的表還有子查詢，如果資料量大的話，這樣會很慢的。如果可以，做個存盤程序，把左連接的表處理好做成臨時表，然后再去和主表連接。
uj5u.com熱心網友回復：
資料量只有4500多條，索引這些都有，但是我把distinct去掉反而時間增加了
uj5u.com熱心網友回復：
distinct 去掉，如果條數也增加了，說明左連接的表關聯欄位不唯一，出現了1對多或者多對多的關系。如果條數每增加，理論上時間不會增加。
uj5u.com熱心網友回復：
要得，我試試
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/24339.html
標籤：疑難問題
下一篇：關于MySQL