选择符合条件的列名(MySQL) [英] Select column names that match a criteria (MySQL)
问题描述
我的数据库中具有以下结构:
I have the following structure in my DB:
id,col_a,col_b,col_c,etc...
现在,除id以外的所有其他列均为boolean类型.比方说
Now, all the other columns except for id are of type boolean. Lets say for example that
col_a=1,
col_b=0,
col_c=1
我正在寻找一种返回列名称为true(= 1)的列名称的方法,因此在此示例中,返回值应类似于col_a,col_c
I am looking for a way to return the names of the columns where the column is true (=1), so in this example the return should look something like col_a,col_c
会有动态的列数,因为表经常更改以添加新列和删除旧列.
There will be a dynamic number of columns, seeing as the table is altered often to add new columns and delete old ones.
到目前为止,我拥有的函数看起来像这样-应该是该函数返回该列名称的字符串...
The function I have thus far looks like this - it is the function that is supposed to return that string of column names...
DROP FUNCTION fn_access;
DELIMITER //;
CREATE FUNCTION fn_access (myid INT) RETURNS varchar(800)
DETERMINISTIC
BEGIN
DECLARE ret_val VARCHAR(800);
DECLARE col_name VARCHAR(255);
DECLARE i INT;
DECLARE num_rows INT;
DECLARE col_names CURSOR FOR
SELECT column_name
FROM information_schema.columns
WHERE `table_name` = 'access' AND `table_schema` = 'some_db' AND `column_name` <> 'id'
ORDER BY ordinal_position;
SELECT FOUND_ROWS() into num_rows;
SET i = 1;
the_loop: LOOP
IF i > num_rows THEN
CLOSE col_names;
LEAVE the_loop;
END IF;
FETCH col_names
INTO col_name;
SET ret_val = CONCAT(',' col_name);
SET i = i + 1;
END LOOP the_loop;
SELECT * FROM access WHERE id = @myid;
RETURN ret_val;
END
//
有什么方法可以使用直接SQL做到这一点?我正在使用MySQL.
Is there any way to do this using straight SQL? I am using MySQL.
推荐答案
如果我正确理解了您的问题,也许您需要这样的东西:
If I understand your question correctly, maybe you need something like this:
SELECT 'col_a' col
FROM yourtable
WHERE col_a
UNION
SELECT 'col_b'
FROM yourtable
WHERE col_b
UNION
SELECT 'col_c'
FROM yourtable
WHERE col_c
...
这将返回表中至少有一行为真的所有列.
this will return all columns in your table that have at least one row where they are true.
或者也许是这样
SELECT
id,
CONCAT_WS(', ',
CASE WHEN col_a THEN 'col_a' END,
CASE WHEN col_b THEN 'col_b' END,
CASE WHEN col_c THEN 'col_c' END) cols
FROM
yourtable
将以以下格式返回行:
| ID | COLS |
----------------------------
| 1 | col_a, col_c |
| 2 | col_a, col_b, col_c |
| 3 | |
| 4 | col_c |
...
请在此处查看小提琴.而且,如果您需要动态执行此操作,则可以使用以下准备好的语句:
Please see fiddle here. And if you need to do it dynamically, you could use this prepared statement:
SELECT
CONCAT(
'SELECT id, CONCAT_WS(\', \',',
GROUP_CONCAT(
CONCAT('CASE WHEN ',
`COLUMN_NAME`,
' THEN \'',
`COLUMN_NAME`,
'\' END')),
') cols FROM yourtable'
)
FROM
`INFORMATION_SCHEMA`.`COLUMNS`
WHERE
`TABLE_NAME`='yourtable'
AND COLUMN_NAME!='id'
INTO @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
小提琴此处.
这篇关于选择符合条件的列名(MySQL)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!