对单个插入的多个 MySQL 查询 [英] multiple MySQL queries to a single insert

问题描述

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

I'm trying to perform a number of MySQL queries and to write the results as a single line to a different database and table.

这样就行了

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 列

I get an error #1241 - Operand should contain 1 column(s)

有谁知道我如何才能使所有查询都有效?

Does anyone know how I can make this work for all the queries?

推荐答案

尝试使用会话变量或将其抽象为存储过程，例如 -

Try using session variables or abstract it away into a stored procedure, something like -

首先:

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)

顺便说一句:调用列date并不是一个好主意.

BTW: It is not a good idea to call a column date.

编辑

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

Ofcourse you need to make sure, those queries run in the same session to use session variables. This is the default when run with a single DB connection in PHP

编辑 2

要处理没有活动的时期，您可以

To handle periods without activity, you can

• 在前面加上 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();

这篇关于对单个插入的多个 MySQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！