嵌套MySQL选择问题在WordPress usermeta表中

Question

有一个复杂的MySql选择语句的问题，希望一些指针!

所以我在WordPress上使用了一些插件，这些插件有一些有趣的存储数据的方式。我关心的部分如下:有一些"父"帐户有许多子帐户。父子关系存储在usermeta表中(user_id=子帐户的user_id, meta_value = parent_id, meta_key='parent')。每个子帐户还可以完成许多任务。这也存储在usermeta表中(user_id=子帐户的user_id, meta_value = complete_status, meta_key='task_id_'.task_id)。

我试图创建一个视图，在那里我得到每个这些父帐户的列表，以及一些信息，然后从他们的孩子那里得到一些派生值，包括每个父的孩子完成的任务的平均数量。

这是我的MySQL语句，有问题的部分是嵌套的select:

SELECT 
wp_parent_account_info_table.obj_id, 
wp_parent_account_info_table.obj_type, 
wp_parent_account_info_table.id, 
wp_other_custom_table_info.created_at, 
wp_other_custom_table_info.product_id, 
(SELECT AVG(cc.rcount) 
FROM (SELECT DISTINCT COUNT(*) as rcount 
FROM wp_usermeta 
WHERE meta_key LIKE 'task_id_%' 
AND meta_value = 'complete' 
AND wp_usermeta.user_id IN (SELECT DISTINCT user_id 
FROM wp_usermeta 
WHERE meta_key = 'parent' 
AND meta_value = wp_parent_account_info_table.id
) AS sc
) AS cc
) AS a 
FROM wp_parent_account_info_table 
JOIN wp_other_custom_table_info 
ON `wp_parent_account_info_table`.`obj_id`=`wp_other_custom_table_info`.`id` 
INNER JOIN wp_another_custom_table_info 
ON `wp_another_custom_table_info`.`subscription_id`=`wp_parent_account_info_table`.`obj_id` WHERE `wp_parent_account_info_table`.`status` = 'enabled' 
AND `wp_parent_account_info_table`.`sub_accounts_available` <> '0' 
AND `wp_other_custom_table_info`.`status` = 'active' 
AND (`wp_another_custom_table_info`.`expires_at` > CONCAT(CURDATE(), ' 23:59:59') 
OR `wp_another_custom_table_info`.`expires_at` = '0000-00-00 00:00:00') 
LIMIT 0,30;

我已经尽力使其可读性，为复杂性道歉。我不想删除任何部分，以防它们是相关的。

没有嵌套的select语句也可以正常工作。如果我用id数组替换最嵌套的选择(所以只是放入:IN(1,2,3))，它也可以工作(没有错误)。是不是因为我太想知道他父母的身份了?

错误如下:#1064 -你有一个错误的SQL语法;检查与您的MySQL服务器版本对应的手册，以获得正确的语法，以便在'AS sc) AS cc) AS a FROM wp_parent_account_info_table JOIN wp_other_custom_table_info ON'

如能指点，不胜感激。

编辑:

下面的答案解决了这个错误，我也不能访问最远的巢中的id变量(一个新的错误!)，所以我把它们分成一个额外的列。这是我最后的代码:

SELECT 
wp_parent_account_info_table.obj_id, 
wp_parent_account_info_table.obj_type, 
wp_parent_account_info_table.id, 
wp_other_custom_table_info.created_at, 
wp_other_custom_table_info.product_id, 
(SELECT DISTINCT COUNT(*) 
FROM wp_usermeta
WHERE meta_key = 'parent' 
AND meta_value = wp_parent_account_info_table.id) as c,
(SELECT DISTINCT COUNT(*) as rcount 
FROM wp_usermeta 
WHERE meta_key LIKE 'task_id_%' 
AND meta_value = 'complete' 
AND wp_usermeta.user_id IN (SELECT DISTINCT user_id 
FROM wp_usermeta 
WHERE meta_key = 'parent' 
AND meta_value = wp_parent_account_info_table.id
) 
) AS a,
(SELECT a / c)
FROM wp_parent_account_info_table 
JOIN wp_other_custom_table_info 
ON `wp_parent_account_info_table`.`obj_id`=`wp_other_custom_table_info`.`id` 
INNER JOIN wp_another_custom_table_info 
ON `wp_another_custom_table_info`.`subscription_id`=`wp_parent_account_info_table`.`obj_id` WHERE `wp_parent_account_info_table`.`status` = 'enabled' 
AND `wp_parent_account_info_table`.`sub_accounts_available` <> '0' 
AND `wp_other_custom_table_info`.`status` = 'active' 
AND (`wp_another_custom_table_info`.`expires_at` > CONCAT(CURDATE(), ' 23:59:59') 
OR `wp_another_custom_table_info`.`expires_at` = '0000-00-00 00:00:00') 
LIMIT 0,30;

Answer 1

语法错误与您的IN子句有关…如果基于子选择的IN子句不需要表名别名，则避免在)

之后使用sc。

SELECT 
wp_parent_account_info_table.obj_id, 
wp_parent_account_info_table.obj_type, 
wp_parent_account_info_table.id, 
wp_other_custom_table_info.created_at, 
wp_other_custom_table_info.product_id, 
(SELECT AVG(cc.rcount) 
FROM (SELECT DISTINCT COUNT(*) as rcount 
FROM wp_usermeta 
WHERE meta_key LIKE 'task_id_%' 
AND meta_value = 'complete' 
AND wp_usermeta.user_id IN (SELECT DISTINCT user_id 
FROM wp_usermeta 
WHERE meta_key = 'parent' 
AND meta_value = wp_parent_account_info_table.id
) 
) AS cc
) AS a 
FROM wp_parent_account_info_table 
JOIN wp_other_custom_table_info 
ON `wp_parent_account_info_table`.`obj_id`=`wp_other_custom_table_info`.`id` 
INNER JOIN wp_another_custom_table_info 
ON `wp_another_custom_table_info`.`subscription_id`=`wp_parent_account_info_table`.`obj_id` WHERE `wp_parent_account_info_table`.`status` = 'enabled' 
AND `wp_parent_account_info_table`.`sub_accounts_available` <> '0' 
AND `wp_other_custom_table_info`.`status` = 'active' 
AND (`wp_another_custom_table_info`.`expires_at` > CONCAT(CURDATE(), ' 23:59:59') 
OR `wp_another_custom_table_info`.`expires_at` = '0000-00-00 00:00:00') 
LIMIT 0,30;