mysql - 对单个插入进行多个 MySQL 查询

Question

我正在尝试执行多个 MySQL 查询并将结果作为一行写入不同的数据库和表。

所以这有效

INSERT INTO bridgedb.stats (longestcall, totalmins, totalconfs)
SELECT
(SELECT MAX(duration) AS longestcall FROM bridgedb.log WHERE `start` >= NOW() - INTERVAL 1 DAY),

(SELECT SUM(duration) AS totalmins FROM bridgedb.log WHERE `start` >= NOW() - INTERVAL 1 DAY),

(SELECT COUNT(*) FROM bridgedb.log WHERE `start` >= NOW() - INTERVAL 1 DAY);

这有效

INSERT INTO bridgedb.stats (date, peakchan)
SELECT
  `calldate`,
  MAX(concurrent)+1 AS peakcount
FROM (
    SELECT
      DATE(a.calldate) as calldate,
      COUNT(b.uniqueid) AS concurrent
    FROM asteriskcdr.cdr AS a, asteriskcdr.cdr AS b
    WHERE  
      a.calldate >= NOW() - INTERVAL 4 DAY
      AND (
        (a.calldate<=b.calldate AND (UNIX_TIMESTAMP(a.calldate)+a.duration)>=UNIX_TIMESTAMP(b.calldate))
        OR (b.calldate<=a.calldate AND (UNIX_TIMESTAMP(b.calldate)+b.duration)>=UNIX_TIMESTAMP(a.calldate))
      )
      AND a.uniqueid>b.uniqueid
    GROUP BY a.uniqueid
  ) AS baseview
GROUP BY calldate;

但这行不通

INSERT INTO bridgedb.stats (date, peakchan, longestcall, totalmins, totalconfs)
SELECT
(SELECT
      `calldate`,
      MAX(concurrent)+1 AS peakcount
    FROM (
        SELECT
          DATE(a.calldate) as calldate,
          COUNT(b.uniqueid) AS concurrent
        FROM asteriskcdr.cdr AS a, asteriskcdr.cdr AS b
        WHERE  
          a.calldate >= NOW() - INTERVAL 1 DAY
          AND (
            (a.calldate<=b.calldate AND (UNIX_TIMESTAMP(a.calldate)+a.duration)>=UNIX_TIMESTAMP(b.calldate))
            OR (b.calldate<=a.calldate AND (UNIX_TIMESTAMP(b.calldate)+b.duration)>=UNIX_TIMESTAMP(a.calldate))
          )
          AND a.uniqueid>b.uniqueid
        GROUP BY a.uniqueid
      ) AS baseview
    GROUP BY calldate),

(SELECT MAX(duration) AS longestcall FROM bridgedb.log WHERE `start` >= NOW() - INTERVAL 1 DAY),

(SELECT SUM(duration) AS totalmins FROM bridgedb.log WHERE `start` >= NOW() - INTERVAL 1 DAY),

(SELECT COUNT(*) FROM bridgedb.log WHERE `start` >= NOW() - INTERVAL 1 DAY);

我收到错误 #1241 - 操作数应包含 1 列

有谁知道我如何才能使其适用于所有查询？

Answer 1

尝试使用 session 变量或将其抽象到存储过程中，例如 -

第一:

SELECT
  @calldate:=`calldate`,
  @peakchan:=MAX(concurrent)+1
FROM (
  ...
) AS baseview
GROUP BY calldate

第二:

SELECT @longestcall:=MAX(duration) FROM bridgedb.log WHERE `start` >= NOW() - INTERVAL 1 DAY

第三:

SELECT @totalmins:=SUM(duration)  FROM bridgedb.log WHERE `start` >= NOW() - INTERVAL 1 DAY

第四:

SELECT @totalconfs:=COUNT(*) FROM bridgedb.log WHERE `start` >= NOW() - INTERVAL 1 DAY;

最后

INSERT INTO bridgedb.stats (date, peakchan, longestcall, totalmins, totalconfs)
VALUES (@calldate, @peakchan, @longestcall, @totalmins, @totalconfs)

顺便说一句:将列称为日期并不是一个好主意。

编辑

当然，您需要确保这些查询在同一 session 中运行以使用 session 变量。这是在 PHP 中使用单个数据库连接运行时的默认设置

编辑2

要处理没有事件的时期，您可以

• 可以在前面加上SELECT @calldate:=DATE(NOW() - INTERVAL 1 DAY)、@peakchan:=0、@longestcall:=0、@totalmins:=0、@totalconfs:=0； (创建默认值的记录)
• 或者在第一个查询后停止，如果@calldate为NULL(没有创建记录)

编辑3

这对我有用:

DELIMITER //

CREATE PROCEDURE ReportYesterday()
BEGIN
  DECLARE calldate DATE;
  DECLARE peakchan, longestcall, totalmins, totalconfs INT;

SELECT
  @calldate:=`calldate`,
  @peakchan:=MAX(concurrent)+1
FROM (
       SELECT
          DATE(a.calldate) as calldate,
          COUNT(b.uniqueid) AS concurrent
        FROM asteriskcdr.cdr AS a, asteriskcdr.cdr AS b
        WHERE  
          a.calldate >= NOW() - INTERVAL 1 DAY
          AND (
            (a.calldate<=b.calldate AND (UNIX_TIMESTAMP(a.calldate)+a.duration)>=UNIX_TIMESTAMP(b.calldate))
            OR (b.calldate<=a.calldate AND (UNIX_TIMESTAMP(b.calldate)+b.duration)>=UNIX_TIMESTAMP(a.calldate))
          )
          AND a.uniqueid>b.uniqueid
        GROUP BY a.uniqueid) AS baseview
GROUP BY calldate
;

-- EDIT 4 IS THE FOLLOWING LINE 

IF @calldate IS NOT NULL THEN

  SELECT @longestcall:=MAX(duration) FROM bridgedb.log WHERE `start` >= NOW() - INTERVAL 1 DAY
  ;

  SELECT @totalmins:=SUM(duration)  FROM bridgedb.log WHERE `start` >= NOW() - INTERVAL 1 DAY
  ;

  SELECT @totalconfs:=COUNT(*) FROM bridgedb.log WHERE `start` >= NOW() - INTERVAL 1 DAY
  ;

  INSERT INTO bridgedb.stats (date, peakchan, longestcall, totalmins, totalconfs)
  VALUES (@calldate, @peakchan, @longestcall, @totalmins, @totalconfs)
  ;

END IF;

END
;
//

DELIMITER ;

其次是

CALL ReportYesterday();