在日常工作中,常常用很多sql查询,除了比较常见的order by
、sum
、count
等等,实际上的开发场景要比想象中复杂的多,以下是我在工作中遇到比较常用又不了解的函数
查询当前时间(now)
mysql提供比较多的查询时间函数,总的来说都是查询当前时间,但是往细了说还是有区别的,具体可以上网查查,有很多文章比较
select NOW(),CURRENT_DATE(),SYSDATE()
查询结果
查询子节点所有的父节点
select * from base_projectcode project where 'J01QJ01T01QC01QR21' like CONCAT(Code,'%');
查询结果
查询结果为null置0
select COALESCE(SUM(1+null),0) as sum1;
select IFNULL(SUM(1+NULL),0) as sum2
查询结果
union
SELECT
codeTable.*,
count(QualityProblemRecord.Id) as ProblemContentCount
FROM
(
SELECT
investigation.TreeCode AS ProblemCode,
investigation.Content as ProblemContent
FROM
SAF_BaseDangerousInvestigationTree investigation
WHERE
investigation.TreeParentCode = '0005' UNION ALL
SELECT
DangerCode AS ProblemCode,
QPContent as ProblemContent
FROM
SAF_BaseDangerousInvestigationInfo
WHERE
DangerTreeCode IN ( SELECT investigation.TreeCode AS `code` FROM SAF_BaseDangerousInvestigationTree investigation WHERE investigation.TreeParentCode = '0005' )
) AS codeTable
LEFT JOIN QCM_QualityProblemRecord AS QualityProblemRecord ON codeTable.ProblemCode = QualityProblemRecord.ProblemCode and QualityProblemRecord.OrgCode like '0%'
GROUP BY
codeTable.ProblemCode;
查询结果
find_in_set
在C#中,in
操作符后面跟的必须是stirng+',‘拼接(“1”,“2”,“3”,“4”,“5”),或者number+’,'拼接(1,2,3,4,5),不可以是一个字符串中间用逗号拼接(“1,2,3,4,5,6”)
因为在C#中,没有这样的类型,故or查询时,可以使用find_in_set
MySQL手册中find_in_set
函数的语法解释:
FIND_IN_SET(str,strlist)
str 要查询的字符串
strlist 字段名 参数以”,”分隔 如 (“1,2,3,4,5,6”)
DATE_FORMAT
date_format
主要用于日期格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')
查询结果
升序序号赋值
在QCM_BoxManage
中需要给SortNo
字段赋值,由小到大依次升序
set @rank= 0;update QCM_BoxManage t set t.SortNo = @rank:=@rank + 1
where t.OrgCode = '001003001' order by t.CreatedTime asc;
REPLACE
REPLACE:替换字符
UPDATE
qcm_tablelanguagecontent a
JOIN qcm_tablelanguagetype b ON a.LanguageTypeId = [b.Id](http://b.id/)
AND b.TypeCode = '2'
SET a.Content = REPLACE ( a.Content, '符合', '符合□/不符合□' )
WHERE
a.Content LIKE '%符合%';
CONCAT
CONCAT:字符拼接
update QCM_ProjectTableInfo set AppPageUrl=CONCAT("/ZJJW/Qcm/FillTable/",CatalogCode,"/",TableNo,".cpt") where CatalogCode="CS100"