select语句中带有子查询的MySQL问题

Question

我需要使用子查询来命名查询中的列。我在SELECT部分中编写了一个带有子查询的语句，但是我得到了以下错误消息(从Codeigniter返回)：

错误号: 1064 您的SQL语法出现错误；请检查与您的MySQL服务器版本对应的手册，以获得使用near‘的正确语法(从product_selects中选择ps_id = '1')，在第1行选择psc_opt_2 A

SELECT
    `psc_opt_1` AS (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '1'),
    `psc_opt_2` AS (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '2'),
    `psc_opt_3` AS (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '3'),
    `psc_opt_4` AS (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '4'),
    `psc_opt_5` AS (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '5')
FROM (`product_stock_control`)
WHERE `psc_prod_id` = '5'

文件名: models/products_model.php 线路号码: 602

有什么不对吗？

Answer 1

AS是一个列别名运算符，意味着您要选择这些变量作为标识符。不能使用运算符标识带有查询的变量。

参考：http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

相反，您需要反向使用别名操作符，如下所示：

SELECT
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '1') AS `psc_opt_1`,
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '2') AS `psc_opt_2`,
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '3') AS `psc_opt_3`,
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '4') AS `psc_opt_4`,
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '5') AS `psc_opt_5`
FROM (`product_stock_control`)
WHERE `psc_prod_id` = '5'

理想情况下，您应该避免使用子查询(尤其是在本例中)来避免表锁定开销。

Answer 2

正确格式是

(SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '1') As `psc_opt_1` 

Answer 3

你试过这个吗？

SELECT
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '1') as `psc_opt_1`,
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '2') as `psc_opt_2`,
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '3') as `psc_opt_3`,
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '4') as `psc_opt_4`,
    (SELECT `ps_url_name` FROM `product_selects` WHERE `ps_id` = '5') as `psc_opt_5`
FROM (`product_stock_control`)
WHERE `psc_prod_id` = '5'