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

最新推荐文章于 2024-10-12 15:15:36 发布

Loser100

最新推荐文章于 2024-10-12 15:15:36 发布

阅读量3.1k

点赞数 1

分类专栏： Mysql+SQL

本文链接：https://blog.csdn.net/Loser100/article/details/78491741

版权

Mysql+SQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

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

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

2.获取系统当前时间（具体时间形式请参阅：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