SQL GROUP BY 分组

--  统计总数使用了分组
SELECT 
  al.value AS factors_value,
  ROUND(al.pv / maxpv.total * 100, 1) AS rate 
FROM
  (SELECT 
    IF(
      INSTR(factors_value, '电信'),
      '电信',
      IF(
        INSTR(factors_value, '移动'),
        '移动',
        IF(
          INSTR(factors_value, '联通'),
          '联通',
          IF(
            INSTR(factors_value, '铁通'),
            '铁通',
            IF(
              INSTR(factors_value, '长城宽带'),
              '长城宽带',
              factors_value
            )
          )
        )
      )
    ) AS VALUE,
    SUM(pv) AS pv 
  FROM
    stats_buyer 
  WHERE web_id = 'xxxxxxx' 
    AND factors_id = '0' 
    AND factors_value REGEXP '电信|移动|联通|铁通|长城宽带' 
  GROUP BY VALUE) al 
  INNER JOIN 
    (SELECT 
      MAX(a.pv) AS pv,
      SUM(a.pv) AS total 
    FROM
      (SELECT 
        IF(
          INSTR(factors_value, '电信'),
          '电信',
          IF(
            INSTR(factors_value, '移动'),
            '移动',
            IF(
              INSTR(factors_value, '联通'),
              '联通',
              IF(
                INSTR(factors_value, '铁通'),
                '铁通',
                IF(
                  INSTR(factors_value, '长城宽带'),
                  '长城宽带',
                  factors_value
                )
              )
            )
          )
        ) AS VALUE,
        SUM(pv) AS pv 
      FROM
        stats_buyer 
      WHERE web_id = 'xxxxxxx' 
        AND factors_id = '0' 
        AND factors_value REGEXP '电信|移动|联通|铁通|长城宽带' 
      GROUP BY VALUE) a) maxpv 
    ON al.pv = maxpv.pv 
    
    
    --  统计总数使用了分组测试

    SELECT INSTR(factors_value,'电信'),factors_value, SUM(pv) AS pv  FROM stats_buyer
    GROUP BY INSTR(factors_value,'电信')
    
    -- 自己写的和上面写的区别
    SELECT  tbl_sum.factors_value,tbl_sum.pv/tbl_total.pv_total,tbl_total.pv_total,tbl_sum.pv
    FROM 
    (
    SELECT factors_value,SUM(pv) AS pv
    FROM stats_buyer 
    WHERE web_id='xxxxx' AND factors_id='3'
    GROUP BY factors_value
    ORDER BY  pv DESC LIMIT 1
    ) AS tbl_sum
    LEFT JOIN 
    (  
	SELECT SUM(all_pv) AS pv_total 
	FROM
	(
	SELECT factors_value,SUM(pv) AS all_pv
	FROM stats_buyer 
	WHERE web_id='xxxxxx' AND factors_id='3'
	GROUP BY factors_value 
	) 
	AS table_total
    ) AS tbl_total 
    ON 1=1