条件mysql insert

m1m5dgzv 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(442)

我开发这个解决方案，如果有其他更好的解决方案请分享。

SET @num = (SELECT count(distinct(detalle)) as 'distintos'
FROM
(SELECT detalle, SUBSTRING(momento, 1, 10) as momento FROM t2
where detalle in ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I')) AS T1
where momento like (Select SUBSTRING(momento, 1, 10) as momento from t2 order by momento desc limit 1));
INSERT INTO `t2`.`log_reportes` (`accion`, `detalle`)
SELECT 'REVISION AUTOMATICA', 'ERROR' FROM DUAL
WHERE @num = 6;
INSERT INTO `t2`.`log_reportes` (`accion`, `detalle`)
SELECT 'REVISION AUTOMATICA', 'OK' FROM DUAL
WHERE @num = 7;

sql mysql if-statement Conditional

来源：https://stackoverflow.com/questions/51252258/conditional-mysql-insert

1条答案

按热度按时间

gfttwv5a1#

它可以一次完成 INSERT 查询。

INSERT INTO `t2`.`log_reportes` (`accion`, `detalle`)
SELECT 'REVISION AUTOMATICA', IF(distintos = 6, 'ERROR', 'OK')
FROM (SELECT COUNT(DISTINCT detalle) as distintos
        FROM (SELECT detalle, SUBSTRING(momento, 1, 10) as momento FROM t2
              where detalle in ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I')) AS T1
        where momento = (Select MAX(SUBSTRING(momento, 1, 10)) from t2)
    ) AS t3
WHERE distintos IN (6, 7)

我还简化了子查询

(Select SUBSTRING(momento, 1, 10) as momento from t2 order by momento desc limit 1)

变成正义

(Select MAX(SUBSTRING(momento, 1, 10)) from t2)

如果 momento 是一个 DATETIME , SUBSTRING(momento, 1, 10) 更恰当的做法是 DATE(momento) .

展开查看全部

赞(0）回复(0）举报 2021-06-21

我来回答

条件mysql insert

1条答案

相关问题

热门标签

最新问答