MySql常用查询语句中函数的使用

向右看齐 2022-06-06 13:08 228阅读 0赞

1.判断表内字段是否为空，若空则自定义：

select IFNULL(name,'user') credit from student

2.获取系统当前时间（具体时间形式请参阅：[http://www.w3school.com.cn/sql/sql\_dates.asp][http_www.w3school.com.cn_sql_sql_dates.asp]）

select CURDATE() from student

3.将表内时间替换为指定格式时间：

select DATE_FORMAT(bdi.activedate,'%Y-%m-%d') from base_dealer_info bdi

4.做简单的判断统计（分别统计出表内各status的数量以及reporttype数量）:

select count(*) as num,sum(case when bdi.status = 4 then 1 else 0 end) as status1 , sum(case when bdi.status = 4 then 1 else 0 end) as status2 , sum(case when bdi.status = 4 then 1 else 0 end) as status3 , sum(case when bdi.status = 4 then 1 else 0 end) as status4 , sum(case when bdi.reporttype = 1 then 1 else 0 end) as reporttype1 , sum(case when bdi.reporttype = 2 then 1 else 0 end) as reporttype2 , sum(case when bdi.reporttype = 3 then 1 else 0 end) as reporttype3 from base_dealer_info bdi inner join base_org_dealer_ref bodr on bdi.enterpriseid=bodr.dealerid where orgcode='welink' and projectcode='pro001'

5.向下递归函数的编写

BEGIN DECLARE str varchar(2000);  
    DECLARE cid varchar(100); 
    DECLARE s BOOLEAN;
    SET s = true;  
    SET str = '$';   
    SET cid = rootId;   
    WHILE cid is not null DO IF s THEN SET str = cid;
                SET s = false;
            ELSE
                SET str = concat(str, ',', cid);   
            END IF;
        SELECT group_concat(enterpriseid) INTO cid FROM base_enterprise_ref where FIND_IN_SET(parentid, cid) > 0;   
    END WHILE;   
    RETURN str;   
    END

![表结构][SouthEast]

![参数定义][SouthEast 1]

[http_www.w3school.com.cn_sql_sql_dates.asp]: http://www.w3school.com.cn/sql/sql_dates.asp
[SouthEast]: /images/20220606/bf8b66a5bad24fabbbb2f11a1a3be2e9.png
[SouthEast 1]: /images/20220606/1a959702ce1c4d02813b03a2daebc288.png