Mysql
数据表自连接查询
UPDATE user_accounts a
INNER JOIN user_accounts b ON a.id = b.id
SET a.status = 0
WHERE b.update_at <= DATE_ADD(NOW(), INTERVAL -2 DAY)
触发器before执行
DELIMITER $
create trigger tri_check_index before update on message -- 在更新之前(before)触发
for each row
begin
if(old.status != 1 && new.status = 1) then
set @cur_check_index = (select max(check_index) from message) + 1;
set new.check_index = @cur_check_index; -- 设置新(new)数据的值
end if;
end;
$
创建或更新
匹配主键或唯一约束键,如果存在则执行Update
,否则Insert
INSERT INTO `Table_name` (`id`, `type`, `create_at`, `update_at`)
VALUES (3, 2, '2017-05-18 11:06:17', '2017-05-18 11:06:17')
ON DUPLICATE KEY UPDATE `id` = VALUES(`id`), `type` = VALUES(`type`), `update_at` = VALUES(`update_at`);
SqlServer
选择合适的IGNORE_DUP_KEY选项
当你创建唯一索引时,你可以指定IGNORE_DUP_KEY选项,因此本文最开始创建唯一索引的选项可以是:
CREATE UNIQUE NONCLUSTERED INDEX AK_Product_Name ON Production.Product ( [Name]
) WITH ( IGNORE_DUP_KEY = OFF );
IGNORE_DUP_KEY这个名字容易让人误会。唯一索引存在时重复的值永远不会被忽略。更准确的说,唯一索引中永远不允许存在重复键。这个选项的作用仅仅是在多列插入时有用。
比如,你有两个表,表A和表B,有着完全相同的结构。你可能提交如下语句给SQL Server。
INSERT INTO TableA SELECT * FROM TableB;
SQL Server会尝试将所有表B中的数据插入表A。但如果因为唯一索引拒绝表B中含有和表A相同的数据插入A怎么办?你是希望仅仅重复数据插入不成功,还是整个INSERT语句不成功?
这个取决于你设定的IGNORE_DUP_KEY参数,当你创建唯一索引时,通过设置设个参数可以设定当插入不成功时怎么办,设置IGNORE_DUP_KEY的两种参数解释如下:
IGNORE_DUP_KEY=OFF
整个INSERT语句都不会成功并弹出错误提示,这也是默认设置。
IGNORE_DUP_KEY=OFF
只有那些具有重复键的行不成功,其它所有的行会成功。并弹出警告信息。
IGNORE_DUP_KEY 选项仅仅影响插入语句。而不会被UPDATE,CREATE INDEX,ALTER INDEX所影响。这个选项也可以在设置主键和唯一约束时进行设置。
Oracle
HAVING过滤器使用COUNT导致查询结果错误
-- 有驾驶员基本信息表和驾驶员违章记录表,想要查询违章驾驶员的手机号码。
-- 为了便于说明问题,仅列出表中的关键字段。
-- drivername drivertel
-- 蔡某某 13xxxxxxxx
-- 陈某某 13xxxxxxxx
-- 戴某某 15xxxxxxxx
-- 邓某某 (NULL)
-- 韩某某 13xxxxxxxx
-- 革某某 (NULL)
-- 王琪琪 15xxxxxxxx
-- 陆某某 15xxxxxxxx
-- 小埋 18xxxxxxxx
-- lm某某 18xxxxxxxx
-- driverName violationName
-- 蔡某某 闯红灯
-- 蔡某某 超速
-- 戴某某 闯红灯
-- 蔡某某 闯红灯
-- 蔡某某 闯红灯
-- 王琪琪 不知道名称是啥
-- 韩某某 逆行
-- 陆某某 ~~~~(>_<)~~~~
-- 最初的写法:
SELECT a.drivername, a.drivertel, b.violationName FROM t_driverinfo a LEFT JOIN t_vehicleviolationrecord b
ON a.drivername=b.driverName GROUP BY a.drivername HAVING COUNT(*)>=1;
-- 查询结果:
-- drivername drivertel violationName
-- lm某某 18129382910 (NULL)
-- 小埋 18706073384 (NULL)
-- 戴某某 15502938902 闯红灯
-- 王琪琪 15502938902 不知道名称是啥
-- 蔡某某 13587984934 闯红灯
-- 邓某某 (NULL) (NULL)
-- 陆某某 15502938902 ~~~~(>_<)~~~~
-- 陈某某 13598898998 (NULL)
-- 革某某 (NULL) (NULL)
-- 韩某某 13298702987 逆行
-- 出现错误的原因:在这个分组中不能使用COUNT(*)或COUNT(1),因为这会把通过OUTER JOIN添加的行统计入内(非保留表的空值数据),
-- 导致最终查询结果与预期结果不同。
-- 改进写法:
SELECT a.drivername, a.drivertel, b.violationName FROM t_driverinfo a LEFT JOIN t_vehicleviolationrecord b
ON a.drivername=b.driverName GROUP BY a.driverName HAVING COUNT(violationName)>=1;
-- 或
SELECT a.drivername, a.drivertel, b.violationName FROM t_driverinfo a INNER JOIN t_vehicleviolationrecord b
ON a.drivername=b.driverName GROUP BY a.driverName HAVING COUNT(*)>=1;
-- 查询结果:
-- drivername drivertel violationName
-- 戴某某 15502938902 闯红灯
-- 王琪琪 15502938902 不知道名称是啥
-- 蔡某某 13587984934 闯红灯
-- 陆某某 15502938902 ~~~~(>_<)~~~~
-- 韩某某 13298702987 逆行
-- 问题的引出:C语言中,NULL==NULL的比较返回的是1
SELECT 1=NULL;
-- 结果为NULL,而不是0
SELECT NULL=NULL;
-- 结果为NULL,而不是1
-- 总结:对于比较返回值为NULL的情况,可将其视为UNKOWN,即表示未知的。
-- 我们通过司机考勤,建立驾驶员和车辆的对应关系,在现实中,车队可能不会每天都进行考勤。
-- 一般情况下驾驶员不会频繁更换车辆,因此,这就要求车队至少在驾驶员更换车辆时进行考勤。
-- 在查询驾驶员和车辆对应关系时,提取某驾驶员的最后一条考勤信息即可。
SELECT vehicleNo, deviceId, driverName, driverId, lineDeptId, signInTime
FROM
(SELECT
t1.vehicleNo,
t1.deviceId,
t1.driverName,
t1.driverId,
t1.lineDeptId,
t1.signInTime,
IF(@vno <> t1.vehicleNo, @r := 1, @r := @r + 1) AS r,
@vno := t1.vehicleNo
FROM
(SELECT * FROM driver_attendance ORDER BY vehicleNo, signInTime DESC) t1
INNER JOIN
(SELECT @r := 0, @vno := '') t2 -- 初始化用户变量
ON 1 = 1) tt
WHERE r = 1 ;
-- 创建表并插入数据
create table t(a int primary key);
insert into t values(1);
insert into t values(2);
insert into t values(3);
insert into t values(100);
insert into t values(101);
insert into t values(103);
insert into t values(104);
insert into t values(105);
-- 问题一:求数据的连续范围
-- 1、给数据增加行号
SET @a:=0;
SELECT a, @a:=@a+1 AS rn FROM t;
-- 或
SELECT a, @a:=@a+1 rn FROM t, (SELECT @a:=0) AS b;
-- 或
SELECT a, @a:=@a+1 rn FROM t, (SELECT @a:=0) AS a;
-- 2、求数据与行号的差值
SELECT a, rn, a-rn AS diff FROM(
SELECT a, @a:=@a+1 rn FROM t, (SELECT @a:=0) AS a
) AS b;
-- 3、分组统计得到结果
SELECT MIN(a) start_range, MAX(a) end_range FROM(
SELECT a, rn, a-rn AS diff FROM(
SELECT a, @a:=@a+1 rn FROM t, (SELECT @a:=0) AS a
) AS b
) AS c GROUP BY diff;
-- 问题二:求数据的间断范围
-- 核心思路:构造自定义变量@a,使得如果数据连续,每行的值减去@a应该是1
SELECT rn+1 start_range, a-1 end_range FROM(
SELECT a, @a rn, @a:=a FROM t, (
SELECT @a:=MIN(a)-1 FROM t
) AS b
) AS c WHERE a-rn <> 1;
-- 在项目中的应用:求驾驶员最长连续签到的时间段
SELECT * FROM(
SELECT e.*, @npre:=@ncur, @ncur:=e.driverId, IF(@npre=@ncur, @nrn:=@nrn+1, @nrn:=1) AS nrn FROM(
SELECT driverId, MIN(signInTime), MAX(signInTime), COUNT(*) FROM(
SELECT b.*, @pre:=@cur, @cur:=driverId, IF(@pre=@cur, @rn:=@rn+1, @rn:=1) AS rank, diff-@rn AS flag
-- rank是列的别名,不能直接引用。而@表示变量,可以引用,但是需要给它赋值。
-- “AS rank, diff-@rank”,这里rank和@rank的类型不同。
FROM(
SELECT DISTINCT driverId, DATE_FORMAT(signInTime, '%Y-%m-%d') signInTime, DATEDIFF(signInTime, NOW()) diff
FROM driver_attendance ORDER BY driverId, signInTime
) AS b, (SELECT @pre:=1, @cur:=1, @rn:=1) AS c
) AS d GROUP BY driverId, flag ORDER BY driverId, COUNT(*) DESC
) AS e, (SELECT @npre:=1, @ncur:=1, @nrn:=1) AS f
)AS g WHERE nrn=1