连接 join的sql语句优化

1，笛卡尔积（缩小结果集）

2，获取的列的数目少----列少偏移量小，效率明显变高（尤其笛卡尔积大的情况下）

3，GROUP BY r.ORDERNO用于左连接时不增加左边的重复记录，用户统计部分

sum()里面如果加的是列的条件--统计的是符合条件的行数之和

     里面直接是列统计的这一列的所有值纸盒

SELECT

TEM_ID AS temId,

(

SELECT

t.TEAM_NAME

FROM

financial_sales_team t

WHERE

t.ID = o.TEM_ID

) AS teamName,

count(1) AS count,

sum(o.STATUS = '1') AS countUnDialed,

sum(o.DIAL_RESULT = '6') AS countDialedPurpose,

sum(

o.DIAL_RESULT IS NOT NULL

AND o.DIAL_RESULT != ''

AND o.DIAL_RESULT != '6'

) AS countDialedOther,

sum(o.DIAL_RESULT = '10') AS countInvested,

SUM(o.DAIL_COUNT IS NOT NULL and o.DAIL_COUNT !='') as dailCount,

SUM(cc.CALL_LENGTH IS NOT NULL and cc.CALL_LENGTH !='') as calleth,

sum(

o.DIAL_RESULT IS NOT NULL

AND o.DIAL_RESULT != ''

AND o.DIAL_RESULT in ('6','7','8','10')

) AS avlbCustom

FROM

financial_sales_order o

LEFT JOIN (SELECT  r.ID ,r.ORDERNO from financial_sales_operator_record r where r.OPERATOR_TYPE='5' GROUP BY r.ORDERNO) rr ON o.ORDERNO = rr.ORDERNO 

LEFT JOIN (SELECT c.CALL_LENGTH,c.OPERATOR_RECORE_ID FROM financial_sales_operator_call_detail c ) cc ON rr.ID = cc.OPERATOR_RECORE_ID

GROUP BY

o.TEM_ID

用count(*)还是count(列名) || Mysql中的count()与sum()区别