Some common uses of SQL in Mysql

How to use non-default sort. For example, use a sort like 213

You can use the following methods

SELECT DISTINCT
    pg.part_grp_id,
    pg.part_grp_name,
    pg.equip_category_id
FROM
    cost_part_grp pg,
    cost_part_kit pk,
    cost_part_event pe
WHERE
    pe.mdl_ver_id IN (
        SELECT
            s.mdl_ver_id
        FROM
            cost_fleet_model s
        WHERE
            s.fleet_id = 1002
    )
AND pe.part_kit_id = pk.part_kit_id
AND pk.part_grp_id = pg.part_grp_id
ORDER BY
    CASE pg.equip_category_id
WHEN 2 THEN
    1
WHEN 1 THEN
    2
WHEN 4 THEN
    3
ELSE
    4
ENDCopy the code

The original link: https://blog.csdn.net/skytalemcc/article/details/5883728

How does mysql update and replace a particular string in a particular field

UPDATE t_bss_employees
SET mobilephone = REPLACE (mobilephone, "2129", "0000")
WHERE
    id IN (
        SELECT
            a.id
        FROM
            (
                SELECT
                    id
                FROM
                    t_bss_employees
                WHERE
                    mobilephone IN (
                        '18121299262',
                        '18121299247',
                        '18121299206',
                        '18121299209'
                    )
            ) AS a
    )Copy the code

Replace the string 2129 in the Mobilephone field with 0000 to update the current table conditional with the current table. You need to add another () to the condition to build a virtual table

SQL to find the proportion

    select a.style, ROUND(
            b.num / a.sum * 100,
            2
        ) as styleRate from (SELECT 
    count(qspc.id) sum 
    ,qq.style  style
    from  t_qc_security_plan_comment  qspc 
        inner join t_qc_question qq
        on qq.id = qspc.question_id
        LEFT JOIN t_qc_address qa ON qa.id = qspc.address_id
        LEFT JOIN t_system_organ  so on qa.hospital =  so.id
        WHERE qq.scr_level is not NULL
        and qq.type = 3  and so.tenant_code = 'zzyy'
        GROUP BY style) a left join (SELECT 
    count(qspc.id) num 
    ,qq.style  style
    from  t_qc_security_plan_comment  qspc 
        inner join t_qc_question qq
        on qq.id = qspc.question_id
        LEFT JOIN t_qc_address qa ON qa.id = qspc.address_id
        LEFT JOIN t_system_organ  so on qa.hospital =  so.id
        WHERE qq.scr_level is not NULL
        and qq.type = 3  and so.tenant_code = 'zzyy'
        and qspc.is_bad =0
        GROUP BY style) b on a.style = b.style    Copy the code

The calculation logic is to count and average separately. And then divide the two numbers and use round to find the accuracy

This article is published by OpenWrite!