我有这个MySQL表,我希望使用同一个表上的COUNT()和SUM()更新多个列(子列、大小)。
mytable
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存储函数来计算子函数:
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:
SELECT dbname.getchildren( 1, 1 );
这个返回数字5
要更新行(例如id 2和5),我使用以下查询:
UPDATE `dbname`.`mytable`
SET `children` = getchildren( `mytable`.`id` , `mytable`.`userid` )
WHERE `mytable`.`id` IN ( 2, 5 )
和工作正常。
但是我不想使用函数,因为以后我需要更新多个列(例如: size ),我不想为每个列调用函数。
为此,我尝试了以下查询:
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中也不能工作,如果我通过:id
或path
更改组,返回NULL或返回多行(每个组有正确的计数)。看起来COUNT()在子查询上不像往常那样工作。
我在这个查询中遗漏了什么?有人能解释一下到底是什么导致了这种行为吗?或者我完全错了?
在线选择测验
谢谢。
发布于 2014-10-11 18:38:31
经过多次尝试,我被一个简单的“技巧”困住了,我会在这里发帖子。我对我的解决方案不满意,但最终还是奏效了。我使用用户定义变量作为从用户定义函数多变量(INT变量)“返回”的一种方式。
函数( rowid,userid):
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
测试新功能:
SELECT `dbname`.getchildren( 1, 1 ) AS children, @childrensize AS size;
这将返回:
Children size
===============
5 105
现在让我们用id 2和5更新行(包括子行和大小)。
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)是必需的。
这样,该函数将为每一行调用一次并更新多个列。
更新:
以下是解决此问题的正确解决方案:
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%。
https://stackoverflow.com/questions/26289764
复制相似问题