按特定行值动态获取列名称 [英] Get column name dynamically by Specific row value

问题描述

我在写一个query.i搜索，但没有找到任何解决方案。
这里我想根据一些特定的值显示列名。

I am struck at writing a query.i searched and didn't find any solution to this. Here i want to show the column name based on some specific value

对于实例，我的表是这样的。

For Instance , My table is like.

id  | fruits   |vegetables    |softdrink
-----------------------
1   | apple    | Onion        | Pepsi
2   | mango    | Potato       | Coke    
3   | banana   | Bringal      | RedBull

如果我有一个值mango，那么我应该得到列名为fruit或

if i have a value "mango", then i should get the column name as fruit or

如果我有一个值RedBull，那么我应该得到列名为softdrink

if i have a value "RedBull", then i should get the column name as softdrink

注意：我有大约48个字段可以从其中任何一个获取名称

NOTE:i have many columns around 48 to get the name from any one of them

感谢，有一点帮助。

推荐答案

set @q= CONCAT('SELECT columns.column_name 
                from table inner 
                join information_schema.columns 
                on columns.table_schema = "dbname" 
                and columns.table_name = "table" 
                and ((',
                (SELECT GROUP_CONCAT(CONCAT('columns.column_name="',column_name,'"',' and table.',column_name,' = "value','"') SEPARATOR ' OR ')
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE table_name = 'table'),
                '))');
prepare query from @q;
execute query;

这可以肯定。

hew！

小提琴： http： //sqlfiddle.com/#!2/9420c/2/2

PS：替换表使用您的表名， dbname 与您的数据库名称和值与您的值

PS: Replace table with your table name ,dbname with your db name and value with your value

这篇关于按特定行值动态获取列名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！