What is the usage of sql block in mysql-1024programmer

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. Calculate the ratio sql [b. num / a. sum * 100].

The usage of sql block in mysql:

1. Use non-default sorting, for example, use sorting like 213

The following method can be used

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_models
 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 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
 mobile phone IN (
 & # 39; 18121299262 & # 39;,
 & # 39; 18121299247 & # 39;,
 '18121299206',
 & # 39; 18121299209 & # 39;
 )
 ) AS a
 )

Replace the 2129 string in the mobilephone field with 0000

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

3. The sql for the ratio

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 calculate the total and average respectively. Then divide the two numbers and use round to find the precision

More related free learning recommendations:Mysql Tutorial(Video)

The above is the detailed content of the usage of the sql block in mysql, please pay attention to other related articles on 1024programmer.com for more information!