SqlServer数据库中coalesce函数用法:在SqlServer2005中有了新的函数,它非常的实用,它就是coalesce函数,此函数可以返回参数中的第一个非空表达式,当你要在N个字段中选取第一个非空值. 举例说明: select Coalesce( null,'2009-09-01','2009-09-11','2009-09-21','2007-09-11') GO 结果是:'2009-09-01' select Coalesce(null,1000,12,13,null) GO
语法: COALESCE(T v1, T v2, …) 返回参数中的第一个非空值:如果所有值都为NULL,那么返回NULL 以emp表为例: desc emp; empno int None ename string None job string None mgr int None hiredate string None sal double None comm double None deptno int None select * from emp; SMITH CLERK ALLEN
merge集插入,更新,删除于一体,如果要对一个表同时进行插入,更新,删除2个或3三个操作.效率要高于单个操作. merge into tableb b --被操作表using (select id,servicepromotor,servicecmclient from tablea where servicepromotor>0) a--参照表 支持子查询on a.id=b.client --关联关系when matched --a,b表均匹配到做updatethen update set
1.并集(UNION/UNION ALL) Oracle&SQLServer中用法一致 UNION 去重 UNION ALL 不去重 -- 去重 select * from tablea union select * from tableb -- 不去重 select * from tablea union all select * from tableb 2.交集(INTERSECT/EXISTS) Oracle&SQLServer中用法一致 INTERSECT 去重 EXISTS 不去
在mybatis中可能会用到的方法 1.模糊查询 <select id="showByIdName" parameterType="User" resultMap="resultmap"> SELECT r.*,u.id,u.age,u.`password`,u.username FROM role r LEFT JOIN user u ON r.role_id=u.role_id <where> <if test=
知识关键词:DATE_FORMAT select DATE_FORMAT(create_time,'%Y%u') weeks,count(caseid) count from tc_case group by weeks; select DATE_FORMAT(create_time,'%Y%m%d') days,count(caseid) count from tc_case group by days; select DATE_FORMAT(create_time,'%Y%m') month
操作符 <=> NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL. NULL安全的相等比较操作符.这个操作符和'='操作符一样用来执行相等的比较,但返回1而不是NULL如果
自己做过MySQL按天,按周,按月,按时间段统计,但是不怎么满意,后来找到这位大神的博客,转载一下,谢谢这位博主的分享 知识点:DATE_FORMAT 使用示例 select DATE_FORMAT(create_time,'%Y%m%d') days,count(caseid) count from tc_case group by days; select DATE_FORMAT(create_time,'%Y%u') weeks,count(caseid) count from tc_ca