Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to use the sql block in mysql, which has certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

The usage of sql block in mysql: 1, sort by non-default; 2, update and replace a specific string in a field [SELECT a.id FROM (..)]; 3, find the proportional sql [b.num / a.sum * 100].

The use of the sql block in mysql:

1. Use a non-default sort, such as 213

You can use the following methods

SELECT DISTINCTpg.part_grp_id,pg.part_grp_name,pg.equip_category_idFROMcost_part_grp pg,cost_part_kit pk,cost_part_event peWHEREpe.mdl_ver_id IN (SELECTs.mdl_ver_idFROMcost_fleet_model sWHEREs.fleet_id = 1002) AND pe.part_kit_id = pk.part_kit_idAND pk.part_grp_id = pg.part_grp_idORDER BYCASE pg.equip_category_idWHEN 2 THEN1WHEN 1 THEN2WHEN 4 THEN3ELSE4END

2. Mysql update replaces a specific string in a field

UPDATE t_bss_employeesSET mobilephone = REPLACE (mobilephone, "2129", "0000") WHEREid IN (SELECTa.idFROM (SELECTidFROMt_bss_employeesWHEREmobilephone IN ('18121299262)) AS a)

Replace the 2129 string in the mobilephone field with 0000

Updates the current table for the condition with the current table. You need to add another () to the condition to build a virtual table

3. Find the sql of proportion

Select a.style, ROUND (b.num / a.sum * 100,2) as styleRate from (SELECT count (qspc.id) sum Qq.style stylefrom t_qc_security_plan_comment qspc inner join t_qc_question qqon qq.id = qspc.question_idLEFT JOIN t_qc_address qa ON qa.id = qspc.address_idLEFT JOIN t_system_organ so on qa.hospital = so.idWHERE qq.scr_level is not NULLand qq.type = 3 and so.tenant_code = 'zzyy'GROUP BY style) a left join (SELECT count (qspc.id) num Qq.style stylefrom t_qc_security_plan_comment qspc inner join t_qc_question qqon qq.id = qspc.question_idLEFT JOIN t_qc_address qa ON qa.id = qspc.address_idLEFT JOIN t_system_organ so on qa.hospital = so.idWHERE qq.scr_level is not NULLand qq.type = 3 and so.tenant_code = 'zzyy'and qspc.is_bad = 0GROUP BY style) b on a.style = b.style

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

Thank you for reading this article carefully. I hope it will be helpful for everyone to share how to use the sql block in mysql. At the same time, I also hope you can support us, pay attention to the industry information channel, and find out if you have any problems. Detailed solutions are waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.