首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >问答首页 >相同表中的Mysql子查询计数返回NULL

相同表中的Mysql子查询计数返回NULL
EN

Stack Overflow用户
提问于 2014-10-09 23:48:34
回答 1查看 654关注 0票数 1

我有这个MySQL表,我希望使用同一个表上的COUNT()和SUM()更新多个列(子列、大小)。

mytable

代码语言:javascript
运行
复制
id  parentid  name      userid   path    children   privatesize    size
=======================================================================
1   0         Test-1    1        NULL      5        20             125
2   0         Test-2    1        NULL      0        15             15
3   1         Test-3    1        /1/       3        25             75
4   1         Test-4    1        /1/       0        30             30
5   3         Test-5    1        /1/3/     0        10             10
6   3         Test-6    1        /1/3/     1        30             40
7   6         Test-7    1        /1/3/6/   0        10             10
8   0         Test-8    2        NULL      0        20             20

备注:

大小=私处大小+儿童私房尺寸

NULL = "",只是为了演示

让我们现在只更新一个列,children列。现在,我使用MySQL存储函数来计算子函数:

代码语言:javascript
运行
复制
DELIMITER $$

CREATE DEFINER=`dbuser`@`localhost` 
FUNCTION `getchildren`( rowid INT, uid INT ) RETURNS INT(11)

BEGIN

DECLARE children    INT DEFAULT 0;

SELECT COUNT( `mytable`.`id` ) INTO children
FROM `dbname`.`mytable`
WHERE `mytable`.`path` LIKE CONCAT( '%/',rowid ,'/%' ) AND `mytable`.`userid` = uid;

RETURN  children;
END

测试函数getchildren:

代码语言:javascript
运行
复制
SELECT dbname.getchildren( 1, 1 );

这个返回数字5

要更新行(例如id 2和5),我使用以下查询:

代码语言:javascript
运行
复制
UPDATE `dbname`.`mytable`
SET `children` = getchildren( `mytable`.`id` , `mytable`.`userid` )
WHERE `mytable`.`id` IN ( 2, 5 )

和工作正常。

但是我不想使用函数,因为以后我需要更新多个列(例如: size ),我不想为每个列调用函数。

为此,我尝试了以下查询:

代码语言:javascript
运行
复制
UPDATE `dbname`.`mytable` mt
INNER JOIN  (
        SELECT `mytable`.`path` AS path, COUNT( `mytable`.`id`)  AS countid
        FROM `dbname`.`mytable`
        GROUP BY `mytable`.`userid`
        ) sub ON  `sub`.`path` LIKE CONCAT( "%/", `mt`.`id` , "/%" )
SET `mt`.`children` = `sub`.`countid`
WHERE `mt`.`id` IN ( 2, 5 );

实际上,如果没有成功,这会将子值更改为NULL。

即使相同的方法(使用相同的子查询逻辑)在SELECT中也不能工作,如果我通过:idpath更改组,返回NULL或返回多行(每个组有正确的计数)。看起来COUNT()在子查询上不像往常那样工作。

我在这个查询中遗漏了什么?有人能解释一下到底是什么导致了这种行为吗?或者我完全错了?

在线选择测验

谢谢。

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2014-10-11 18:38:31

经过多次尝试,我被一个简单的“技巧”困住了,我会在这里发帖子。我对我的解决方案不满意,但最终还是奏效了。我使用用户定义变量作为从用户定义函数多变量(INT变量)“返回”的一种方式。

函数( rowid,userid):

代码语言:javascript
运行
复制
DELIMITER $$

CREATE DEFINER=`dbuser`@`localhost` 
FUNCTION `getchildren`( rowid INT, uid INT ) RETURNS INT(11)

BEGIN

DECLARE children    INT DEFAULT 0;
SET @childrensize := 0;


SELECT 
    COUNT( `mytable`.`id` ),
    SUM( `mytable`.`privatesize` )
INTO children, @childrensize

FROM `dbname`.`mytable`
WHERE 
    `mytable`.`path` LIKE CONCAT( '%/',rowid ,'/%' )
    AND `mytable`.`userid` = uid;

RETURN  children;
END

测试新功能:

代码语言:javascript
运行
复制
SELECT `dbname`.getchildren( 1, 1 ) AS children, @childrensize AS size;

这将返回:

代码语言:javascript
运行
复制
Children   size
===============
5          105

现在让我们用id 2和5更新行(包括子行和大小)。

代码语言:javascript
运行
复制
UPDATE `dbname`.`mytable`
SET 
    `children` = getchildren( `mytable`.`id`, `mytable`.`userid` ),
    `size` = `privatesize` + IFNULL( @childrensize, 0 )
WHERE `mytable`.`id` IN ( 2, 5 )

和工作正常!

逻辑很简单,每次函数getchildren被调用时,他都更新用户定义的变量@childrensize.。如果该行没有子行,则函数将@childrensize设置为空,因为该IFNULL( @childrensize,0)是必需的。

这样,该函数将为每一行调用一次并更新多个列。

更新:

以下是解决此问题的正确解决方案:

代码语言:javascript
运行
复制
UPDATE `dbname`.`mytable` mt
LEFT JOIN (
    SELECT  `mtc1`.`id`, count(*) numchildren, sum( `mtc2`.`privatesize` ) AS tsize
    FROM `tsdata`.`mytable` mtc1, `tsdata`.`mytable` mtc2 
    WHERE `mtc2`.`path` LIKE CONCAT( '%/',mtc1.id,'/%' )
    GROUP by `mtc1`.id)
mtc ON `mtc`.`id` = `mt`.`id`

SET
    `mt`.`children`= IFNULL( `mtc`.numchildren, 0 ),
    `mt`.`size` = `mt`.`privatesize` + IFNULL( `mtc`.tsize, 0 )
WHERE `mt`.`id`  in ( 2, 5 );

其性能比使用函数(在上述方法上)提高了近40%。

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/26289764

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档