php - 生成的 View 填充了NULL

更新：这个问题似乎已经解决了，这是因为我在声明时使用了不同的小写字母而不是大写字母。然而，我现在面临一个问题，即视图“vw_formdatahard”只填充了多个“NULL”。我要疯了。
我有一个基于Wordpress&Buddypress数据库中可用数据创建视图的过程。这在本地Mamp服务器上运行得很好，但每当我调用托管服务器上的过程时都会出错。即使我将整个数据库从本地服务器复制到联机服务器，它仍然会返回错误。我不是mysql的英雄，所以我要离开这里。。为什么这次不跑？
以下查询失败：“CALLstp_FormQueryBuilder）；
MySQL说：“#1054-字段列表”中的未知列“d.user_id”

BEGIN


DECLARE sqlText text;

DROP VIEW IF EXISTS vw_FormDataRows;

CREATE VIEW vw_FormDataRows AS
SELECT d.user_id, f.name,
CASE WHEN left(f.name, 4) = 'FOTO' && LEFT(d.value, 9) = '/profiles' THEN CONCAT("/wp-content/uploads",d.value)
ELSE d.value END AS 'value'


        FROM wp_bp_xprofile_fields AS F
        LEFT JOIN wp_bp_xprofile_data AS D ON F.id = D.field_id
        WHERE
            parent_id = 0
            AND group_id = 1
            AND d.user_id IN(SELECT U.user_id FROM wp_usermeta AS U WHERE U.meta_key = 'wp_user_level' AND U.meta_value = '0')

ORDER BY d.user_id, f.id;

DROP TEMPORARY TABLE IF EXISTS temp1;

CREATE TEMPORARY TABLE temp1 (SELECT b.user_id, b.name, b.value
FROM (SELECT d.user_id, f.name, d.value
FROM wp_bp_xprofile_fields AS F
LEFT JOIN wp_bp_xprofile_data AS D ON F.id = D.field_id
WHERE
    parent_id = 0
    AND group_id = 1
            AND d.user_id IN(SELECT U.user_id FROM wp_usermeta AS U WHERE U.meta_key = 'wp_user_level' AND U.meta_value = '0')
ORDER BY d.user_id, f.id ) AS B);


SET @sql = NULL;

SET group_concat_max_len = 4096;

SELECT GROUP_CONCAT(DISTINCT
         CONCAT('MAX(CASE WHEN name = ', name,
                ' THEN value END) AS `', replace(name, ' ','_'), '`'))
  INTO sqlText
  FROM temp1;


DROP VIEW IF EXISTS vw_FormDataHard;

SET @sql = CONCAT('CREATE VIEW vw_FormDataHard AS SELECT user_id, ', sqlText, '
                     FROM vw_FormDataRows
                    GROUP BY user_id');


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

DROP TEMPORARY TABLE IF EXISTS temp1;

END

最佳答案

您从未定义别名d只是D，它们是不同的，因为别名区分大小写。