What is the usage of sql block in mysql

Usage of sql block in mysql: 1. Perform non-default sorting; 2. Update and replace a specific string in a field [SELECT a.id FROM(..)]; 3. Find the proportion sql【b.num / a.sum * 100】.

Usage of sql block in mysql:

1. Use non-default sorting , for example, using sorting such as 213

You can use the following method

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
END

2. Mysql update and replace a specific string in a certain 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
)

Replace the 2129 string in the mobilephone field with 0000

Use the current table as the condition to update the current table. You need to add another () to the condition to build a virtual table

3. The 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

The calculation logic is to find the total and average respectively. Then divide the two numbers and use round to find the accuracy

More related free learning recommendations:mysql tutorial( video)

The above is the detailed content of What is the usage of sql block in mysql. For more information, please follow other related articles on the PHP Chinese website!