mysql convertgrp_Mysql中几种sql的常见用法

如何使用非默认的排序。例如使用213之类的排序

可以使用如下方法

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

mysql如何更新替换某一个字段中的特定字符串

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

)

将mobilephone字段中的2129字符串替换为0000使用当前表为条件更新当前表。需要在条件处再添加一个()构建一个虚拟表

求比例的sql

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

计算逻辑是分别求总数和平均数。然后两数相除再用round求精度

本文由博客一文多发平台 OpenWrite 发布！