mysql 的一点点记录

以后再来整理。

-- 查询一个学校的报修单数
SELECT
  s.id AS schoolId , -- 学校ID
    COUNT(i.id) as cntId,  -- 报修单数
  IFNULL(t1.nohandlecnt,0) AS nohandlecnt,
   IFNULL(t2.overcnt,0) AS overcnt,
   ROUND(overcnt * 100 /COUNT(i.id),1)  AS okRate
FROM
    `t_issue` i
LEFT JOIN (
    -- 完成报修单数
    SELECT
        s.id AS schoolId , -- 学校ID
        IFNULL(COUNT(i.id),0) as overcnt  -- 完成的报修单数
    FROM
        `t_issue` i
    INNER JOIN t_school  s ON  i.school_id = s.id
    WHERE  i.completed_on IS NOT NULL  and
         i.acknowledged_on IS NOT NULL  
    GROUP BY 
    i.school_id     
) t2 ON t2.schoolId = i.school_id
LEFT JOIN (
    -- 未处理数
    SELECT
        s.id AS schoolId , -- 学校ID
        IFNULL(COUNT(i.id),0) as nohandlecnt  -- 未处理报修单数

    FROM
        `t_issue` i
    INNER JOIN t_school  s ON  i.school_id = s.id
    WHERE 
 i.acknowledged_on IS  NULL  AND
 i.completed_on IS NULL  

    GROUP BY 
    i.school_id 
) t1 ON t1.schoolId = i.school_id
INNER JOIN t_school  s ON  i.school_id = s.id
GROUP BY 
i.school_id 

 另一种保留小数的方法。

SELECT 
        TRUNCATE (avg(attitude), 1) AS attitude,
        TRUNCATE (avg(timeliness), 1) AS timeliness,
        TRUNCATE (avg(professionality), 1) AS professionality,
        TRUNCATE (avg(overall), 1) AS overall
    FROM (
        SELECT 
             school_id,s.sc_name,
             TRUNCATE (avg(attitude),1) as attitude  ,
             TRUNCATE (avg(timeliness), 1) as timeliness,
             TRUNCATE (avg(professionality), 1) as professionality,
             TRUNCATE (avg(overall), 1) as overall
             FROM (
            SELECT 
             t_repairmen.school_id ,
            TRUNCATE (avg(attitude),1) AS attitude,
            TRUNCATE (avg(timeliness),1) AS timeliness,
            TRUNCATE (avg(professionality),1) AS professionality ,
                TRUNCATE (
                        (
                            avg(attitude) + avg(timeliness) + avg(professionality)
                        ) / 3,
                        1
                    ) AS overall
             FROM `t_issue_rating` 
            INNER JOIN t_repairmen ON t_repairmen.id = t_issue_rating.repairmen_id
            WHERE `is_dummy` = '0' GROUP BY
                    repairmen_id,t_repairmen.school_id  ) t 
            INNER JOIN t_school  s ON  t.school_id = s.id
            WHERE
            1 = 1 
            <if test="conditions.schools!=null">
                AND school_id in (${conditions.schools})
            </if>
            GROUP BY school_id
            ORDER BY
            s.id DESC    
            ) y 

 当遇到count 和group by 在一起，统计出的结果不正确时。

    SELECT COUNT(DISTINCT id) FROM `xx` WHERE 1 = 1  group by id  

 两个表的count值相加

select
(select count(*) from bumen)+(select count(*) from mrs) as sum_count 

 查询用户所在的排名

SELECT
    concat(
        (
            @dddd_row_num :=@dddd_row_num + 1
        ) +0, ''
    ) AS row_id
FROM
    history,
    (SELECT @dddd_row_num := 0) AS foo
WHERE
    1 = 1
order by  mp_send_time DESC
limit 10 offset 0

 各种统计count：

可以在统计count 的时候，将不符合条件的数排除在外

    count(if( e.ext5 is NOT NULL ,true,null)) erijCount,

SELECT
    c.classes_id AS examId,
    i.eng_item_name AS examSubject,
    c.classes_name AS examName,
    eng_apply_start AS registStart,
    eng_apply_end AS registEnd,
    classes_status AS examStatus,
    classes_fee AS examFee,
    (
        CASE
        WHEN i.ext2 IS NOT NULL THEN
            i.ext2
        ELSE
            0
        END
    ) AS examFee1,
    A.realityApplyCount * classes_fee AS shouldApplyFee,
    A.realityApplyFee,
    A.realityApplyCount
FROM
    eng_classes c
INNER JOIN eng_item i ON i.eng_id = c.eng_id
LEFT JOIN exam_examinee ON c.classes_id = examinee_exam_id
AND (
    examinee_exam_status != '4'
    OR ISNULL(examinee_exam_status)
)
AND examinee_apply_type = 'english'
LEFT JOIN (
    SELECT
        classes_id AS Id,
        count(

            IF (
                examinee_pay_status = "1",
                TRUE,
                NULL
            )
        ) * classes_fee AS realityApplyFee,
        count(DISTINCT examinee_id) realityApplyCount
    FROM
        eng_classes
    INNER JOIN exam_examinee ON classes_id = examinee_exam_id
    AND (
        examinee_exam_status != '4'
        OR ISNULL(examinee_exam_status)
    )
    AND examinee_apply_type = 'english'
    GROUP BY
        classes_id
) AS A ON classes_id = A.Id
WHERE
    1 = 1
GROUP BY
    classes_id

 还有这样一种，sum去叠加，中间加判断的。

SELECT 
            pro_apply_id AS examId ,
            pro_apply_name AS examSubject,
            pro_apply_start AS registStart ,
            pro_apply_end AS registEnd ,
            pro_apply_fee AS examFee ,
            A.realityApplyCount * pro_apply_fee as shouldApplyFee,
            A.realityApplyFee,
            A.realityApplyCount
        FROM  pro_apply 
        LEFT JOIN exam_examinee ee ON pro_apply_id = ee.exam_item_id 
            AND examinee_apply_type = 'profession'
        LEFT JOIN (
            SELECT 
                apply_id AS Id ,
                SUM(if(examinee_pay_status = "1",item_fee,0)) AS realityApplyFee ,
                count(DISTINCT examinee_id) realityApplyCount
            FROM
                pro_item 
            INNER JOIN  exam_examinee ee ON item_id = examinee_exam_id
                                        AND (examinee_exam_status != '4' OR ISNULL(examinee_exam_status))
                                        AND examinee_apply_type = 'profession'
             GROUP BY apply_id
        )  AS A ON  A.Id LIKE CONCAT('%' ,pro_apply_id, '%')