我有如下的表产品和列名称:
产品
+----+------+-----------+--------------+-------+
| id | code | category | description | link |
+----+------+-----------+--------------+-------+
| 1 | 1111 | category1 | description1 | link1 |
| 2 | 2222 | category1 | description2 | link2 |
| 3 | 3333 | category1 | description3 | link3 |
| 4 | 4444 | category2 | description4 | link4 |
| 5 | 5555 | category2 | description5 | link5 |
| 6 | 6666 | category3 | description6 | link6 |
+----+------+-----------+--------------+-------+
column_ames
+----+-------------+-------+
| id | column | type |
+----+-------------+-------+
| 1 | id | type1 |
| 2 | code | type1 |
| 3 | category | type2 |
| 4 | description | type2 |
| 5 | link | type3 |
+----+-------------+-------+
我可以这样说:
SELECT ( SELECT `column` FROM `column_names` WHERE `column_id` = 3) FROM `products` WHERE `id` = 1
而我无法得到以下语句:
SELECT ( SELECT `column` FROM `column_names` WHERE `type` = 'type2') FROM `products` WHERE `id` = 1
它给了我错误#1242-子查询返回超过1行
但有可能执行这样的查询吗?也就是说,我只想提取products表中具有column_names表中特定类型的某些列的数据。
这是正确的表格设计吗?还是应该有其他方法?当然,类别应该在另一张表中,但这不是我所要求的。
非常感谢!
所以,多亏了Gordon Linoff和A Paul,我能够做我想做的事。我知道这可能是一个笨拙的解决方案,但它是有效的。欢迎任何想指出笨拙的人。
因此,首先我创建了一个用户定义的过程GetMyColumns((。我说不清前两行的确切目的。这正是当我在编辑器中选择选项时,phpMyAdmin函数编辑器添加的内容。
DELIMITER $$
CREATE DEFINER=`geonextp`@`localhost` FUNCTION `GetMyColumns`(`type` VARCHAR(258)) RETURNS VARCHAR(4096) CHARSET latin1 NOT DETERMINISTIC READS SQL DATA SQL SECURITY DEFINER BEGIN
DECLARE rownum INT DEFAULT 1;
DECLARE counter INT DEFAULT 1;
DECLARE columns_string VARCHAR(4096) DEFAULT '';
DECLARE col_string VARCHAR(512);
SET rownum = (SELECT COUNT(*) FROM column_names);
SET columns_string = '';
WHILE counter <= rownum DO
SET col_string = (
SELECT `column_name`
FROM `column_names`
WHERE
`column_id` = counter AND
`column_type` = type
);
IF col_string IS NULL
THEN
SET col_string = '';
END IF;
IF columns_string = '' THEN
SET columns_string = col_string;
ELSE
IF NOT (col_string = '')
THEN
SET columns_string = CONCAT(CONCAT(columns_string, ', '), col_string);
END IF;
END IF;
SET counter = counter + 1;
END WHILE;
RETURN columns_string;
END$$
DELIMITER ;
然后我添加了一小段代码a Paul建议:
SET @inner_sql = GetColumns('type2');
SET @sql = CONCAT(CONCAT('SELECT ', @inner_sql), ' FROM products WHERE id = 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
结果是:
+-----------+--------------+
| category | description |
+-----------+--------------+
| category1 | description1 |
+-----------+--------------+
这是一次经历:(