mysql-语法-函数-distinct-排序-if-FIND_IN_SET-null-with

原创已于 2025-12-19 16:05:46 修改 · 470 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #android #数据库

于 2020-01-15 16:54:45 首次发布

mysql 专栏收录该内容

6 篇文章

订阅专栏

本文详细介绍了SQL中使用DISTINCT关键字进行数据去重的方法，包括单列和多列去重，以及如何运用LIMIT、多排序、CONCAT和去除字符串空格等功能提升数据查询效率。

一函数

JSON_EXTRACT

link

-- 获取json中的某个属性值
select attribute_value->>'$.serial_mode' from  fluxshare.flux_conf_virtual_data where table_name = 'ffn_serial_no_rule' and JSON_VALID(attribute_value)

concat

select

SELECT concat('你好啊',name,'327今天心情怎么样') FROM student WHERE id = '1' ;

update

UPDATE loan_collection_dispatch_info
SET group_operator_id = CONCAT(operator_group_id, '-', operator_id);

UPDATE `order`
SET `more` = CONCAT('[{"status":"2","money":', `name`, '}]')
WHERE `more` LIKE '%"status":"2"%';

distinct

distinct 表示区分，意指检索出来的行是唯一（去重），其放在列的最前面；如果使用了关键字distinct，其作用于后面的所有列
eg：

select distinct expression[,expression...] from tables [where conditions]

如果distinct关键字后面有多个字段时，则会对多个字段进行组合去重，只有多个字段组合起来的值是相等的才会被去重
参考：click

if

IF( expr1 , expr2 , expr3 )

expr1 的值为 TRUE，则返回值为 expr2
expr1 的值为FALSE，则返回值为 expr3

ifnull

MySQL的IFNULL()函数用于检查第一个参数是否为NULL，如果是，返回第二个参数，否则返回第一个参数。该函数可以在MySQL 4.1及更高版本中使用。

举例： ifnull(m.perms,‘’) as perms,

select distinct m.menu_id, m.parent_id, m.menu_name, m.path, m.component, m.`query`, m.visible, m.status, ifnull(m.perms,'') as perms, m.is_frame, m.is_cache, m.menu_type, m.icon, m.order_num, m.create_time
		from sys_menu m where m.menu_type in ('M', 'C') and m.status = 0
		order by m.parent_id, m.order_num;

FIND_IN_SET

FIND_IN_SET 是 MySQL 中的一个字符串函数，主要用于查找某个字符串在由逗号分隔的字符串列表中的位置。它返回的是该字符串在列表中的索引位置（从 1 开始计数），如果找不到则返回 0。

FIND_IN_SET(str, strlist)

AND FIND_IN_SET(all_s.stage, ac.contains_stages) 含义
检查 all_s.stage 的值是否存在于 ac.contains_stages 字段所包含的逗号分隔的字符串列表中。

count

count()与count(1）差别不大，count(1)稍微高效一点。count(字段)时，如果字段是主键，那么count(字段)更快，否则count(),count(1)更快一些

group by

1 可以去重复
2 可以结合 count（*）看到重复多少条
3 后面可以接多个字段

SELECT id,approval_id,equip_id,out_number FROM sd_adjust_price_approval_relation WHERE approval_id IN (14,16) GROUP BY approval_id,out_number;

with


 WITH tmp AS (
  -- 提取符合条件的 member_id
  SELECT DISTINCT member_id 
  FROM `quark_loan_business_platform_prod`.`adjust_resolve_callback` 
  WHERE `event_name` = 'contact_submit' 
    AND `app_id` = 8000 
    AND create_time >= '2024-12-01 00:00:00'
    AND member_id NOT IN (
      SELECT DISTINCT member_id 
      FROM `quark_loan_business_platform_prod`.`adjust_resolve_callback` 
      WHERE `event_name` = 'withdraw_submit' 
        AND `app_id` = 8000 
        AND create_time >= '2024-12-01 00:00:00'
    )
),
tmp1 AS (
  -- 获取电话号码和用户 ID
  SELECT c.telephone_no AS phone_number, c.id AS member_id 
  FROM member_account AS c 
  INNER JOIN tmp AS t ON c.id = t.member_id
)
SELECT 
  DISTINCT t.phone_number AS `phone_number`, 
  c.`name` AS `用户姓名`, 
  opr.marketing_name AS `员工姓名`,
  CASE
    WHEN LOWER(m.source_channel) LIKE 'h5%' OR LOWER(m.source_channel) LIKE 'offline%' THEN 'h5'
    WHEN LOWER(m.source_channel) LIKE 'app%' THEN 'ios'
    WHEN LOWER(m.source_channel) LIKE 'apk%' OR LOWER(m.source_channel) LIKE 'google%' THEN 'android'
    ELSE 'h5'
  END AS `来源`
FROM tmp1 AS t
LEFT JOIN opr_user_fundation AS opr ON t.phone_number = opr.telephone_no
LEFT JOIN member_certificate_info AS c ON c.member_id = t.member_id
LEFT JOIN member_account AS m ON t.phone_number = m.telephone_no;

二语法

null


    <select id="getOrderDetail33" resultType="com.gzzbjkj.modules.ad.dao.tj.OrderTj">
        SELECT
        o.p_orderid as orderId,
               CASE
        WHEN o.p_zujie = 3 THEN '正常归还'
        ELSE '超时未归还'
        END AS `status`, o.p_userid as userId, o.p_batteryid as batteryId, daili.p_user as dailiName,
        TIMESTAMPDIFF(SECOND, o.p_borrowtime, o.p_ghtime) /60.0 AS useTime, o.back_money as backMoney, o.coupon_pawn as
        couponPawn, o.p_jiesuan as jieSuan, ROUND(o.p_jiesuan - o.coupon_pawn,2) AS shiji, o.p_currency as currency,
        shop.p_name as shopName FROM cdb_order as o INNER JOIN cdb_shop as shop ON o.p_shopid=shop.p_newid INNER JOIN
        cdb_daili as daili ON shop.p_sfid=daili.p_open_id WHERE o.p_zujie in (3,5)
        and p_ghtime <![CDATA[>=]]> #{start} and p_ghtime<![CDATA[<=]]> #{end} and shop.P_CURRENCY = #{currency} and  o.p_cabinetID not in
        <foreach item="item" index="index" collection="cabinetIDs" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

这里的ROUND(o.p_jiesuan - o.coupon_pawn,2) 因为coupon_pawn为null,导致计算为null
加上ifnull函数作为优化

UPDATE refund_order set more = '1' WHERE more IS NULL

多排序

SELECT name,age FROM student ORDER BY age DESC, name ASC;

## sql去空格

```sql
SELECT  RTRIM('      哥，huanying        ') FROM student WHERE id = '1' ;