Mysql中sql的几种常见用法

分手后的思念是犯贱 2023-06-24 15:27 34阅读 0赞

## 如何使用非默认的排序。例如使用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

原文链接：https://blog.csdn.net/skytalemcc/article/details/5883728

## 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求精度