mysql - 嵌套的 MySQL 在 WordPress 用户元表中选择问题
问题描述
对复杂的 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
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;
我试图使这个可读性,为复杂性道歉。我不想删除任何相关的部分。
该语句在没有嵌套选择的情况下工作正常。如果我用一组 ID 替换最嵌套的选择(所以只需输入:IN (1,2,3)),它也可以工作(没有错误)。这是关于我试图从太远的地方获取父 ID 的问题吗?
这是错误: #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;
解决方案
语法错误与您的 IN cluse 有关...基于子选择的 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;