mySQL、存储过程、双计数不起作用(w/SQLfiddle)

Question

我有这个SQL

   SELECT COUNT( DISTINCT `ct`.`content_type_id`) AS `type_count`
        , COUNT(`content_id`) AS `content_count`
     FROM `user_to_content` `uc`
     JOIN `content` `c` USING (`content_id`)
     JOIN `content_type` `ct` USING (`content_type_id`)
    WHERE `user_id`= 1;

我试图同时从同一张表中获取 2 个数字。

这个SQLfiddle文件显示了上面的 SQL 工作...

但是我无法让 SP 版本工作。我似乎无法让 SQLfiddle 与 SP 一起工作(也许有人可以告诉我如何操作)，所以 here是pastebin 的SP...

无论如何，SP 不会“编译”第二个 COUNT 命令...

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 24

这是第 24 行

DECLARE $type_count    TINYINT(1) UNSIGNED DEFAULT 0;

我对此的解决方法是调用表上的 COUNT 两次。我宁愿不。

Answer 1

这是一个经过一些修改的示例:

DELIMITER $$

CREATE PROCEDURE `SP_system_content_badges`
(  IN `$user_id` INT(10) UNSIGNED
 , IN `$content_id` INT(10) UNSIGNED
 , IN `$content_type_id` INT(10) UNSIGNED
)
BEGIN
  -- DECLARE $type_count    TINYINT(1) UNSIGNED DEFAULT 0;
  -- DECLARE $content_count TINYINT(1) UNSIGNED DEFAULT 0;

  DECLARE `$type_count`    INT UNSIGNED DEFAULT 0;
  DECLARE `$content_count` INT UNSIGNED DEFAULT 0;

  -- How many pieces of content has this person consumed
  -- Is there 4 unique kinds as well

  -- SELECT COUNT( DISTINCT `ct`.`content_type_id`) INTO $type_count
  --      , COUNT(`content_id`) INTO $content_count

  SELECT COUNT(DISTINCT `ct`.`content_type_id`), COUNT(`c`.`content_id`)
    INTO `$type_count`, `$content_count`
  FROM `user_to_content` `uc`
    JOIN `content` `c` USING (`content_id`)
    JOIN `content_type` `ct` USING (`content_type_id`)
  WHERE `user_id`= `$user_id`;
  SELECT `$type_count` 'TYPE_COUNT', `$content_count` 'CONTENT_COUNT';
END$$

DELIMITER ;

SQL Fiddle example