sql 选择列名，如
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5274594/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql select with column name like
提问by user 4231
I have a table with column names a1,a2...,b1.b2....
我有一个列名表a1,a2...,b1.b2...。
How can I select all those with column names like a%?
我怎样才能选择所有那些列名像a%？
采纳答案by Stephen Chung
You cannot with standard SQL. Column names are not treated like data in SQL.
您不能使用标准 SQL。列名在 SQL 中不被视为数据。
If you use a SQL engine that has, say, meta-data tables storing column names, types, etc. you may select on that table instead.
如果您使用的 SQL 引擎具有存储列名、类型等的元数据表，则您可以在该表上进行选择。
回答by Blorgbeard is out
This will get you the list
这将为您提供列表
select * from information_schema.columns
where table_name='table1' and column_name like 'a%'
If you want to use that to construct a query, you could do something like this:
如果你想用它来构造一个查询，你可以这样做：
declare @sql nvarchar(max)
set @sql = 'select '
select @sql = @sql + '[' + column_name +'],'
from information_schema.columns
where table_name='table1' and column_name like 'a%'
set @sql = left(@sql,len(@sql)-1) -- remove trailing comma
set @sql = @sql + ' from table1'
exec sp_executesql @sql
Note that the above is written for SQL Server.
请注意，以上内容是为 SQL Server 编写的。
回答by Damian Leszczyński - Vash
回答by Technotronic
This will show you the table name and column name
这将显示表名和列名
select table_name,column_name from information_schema.columns
where column_name like '%breakfast%'
回答by majestzim
Here is a nice way to display the information that you want:
这是显示所需信息的好方法：
SELECT B.table_catalog as 'Database_Name',
B.table_name as 'Table_Name',
stuff((select ', ' + A.column_name
from INFORMATION_SCHEMA.COLUMNS A
where A.Table_name = B.Table_Name
FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)')
, 1, 2, '') as 'Columns'
FROM INFORMATION_SCHEMA.COLUMNS B
WHERE B.TABLE_NAME like '%%'
AND B.COLUMN_NAME like '%%'
GROUP BY B.Table_Catalog, B.Table_Name
Order by 1 asc
Add anything between either '%%' in the main select to narrow down what tables and/or column names you want.
在主选择中的 '%%' 之间添加任何内容以缩小所需的表和/或列名称。
回答by Neil N
SELECT * FROM SysColumns WHERE Name like 'a%'
Will get you a list of columns, you will want to filter more to restrict it to your target table
将为您提供一个列列表，您将需要过滤更多以将其限制为您的目标表
From there you can construct some ad-hoc sql
从那里你可以构建一些特别的 sql
回答by Altimus Prime
Blorgbeard had a great answer for SQL server. If you have a MySQL server like mine then the following will allow you to select the information from columns where the name is like some key phrase. You just have to substitute the table name, database name, and keyword.
Blorgbeard 对 SQL 服务器有很好的回答。如果你有一个像我这样的 MySQL 服务器，那么下面的内容将允许你从名称像一些关键短语的列中选择信息。您只需要替换表名、数据库名和关键字。
SET @columnnames = (SELECT concat("`",GROUP_CONCAT(`COLUMN_NAME` SEPARATOR "`, `"),"`")
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA`='your_database'
AND `TABLE_NAME`='your_table'
AND COLUMN_NAME LIKE "%keyword%");
SET @burrito = CONCAT("SELECT ",@columnnames," FROM your_table");
PREPARE result FROM @burrito;
EXECUTE result;
回答by Francesco Mantovani
Thank you @Blorgbeard for the genious idea.
谢谢@Blorgbeard 的天才想法。
By the way Blorgbeard's query was not working for me so I edited it:
顺便说一下，Blorgbeard 的查询对我不起作用，所以我对其进行了编辑：
DECLARE @Table_Name as VARCHAR(50) SET @Table_Name = 'MyTable' -- put here you table name
DECLARE @Column_Like as VARCHAR(20) SET @Column_Like = '%something%' -- put here you element
DECLARE @sql NVARCHAR(MAX) SET @sql = 'select '
SELECT @sql = @sql + '[' + sys.columns.name + '],'
FROM sys.columns
JOIN sys.tables ON sys.columns.object_id = tables.object_id
WHERE sys.columns.name like @Column_Like
and sys.tables.name = @Table_Name
SET @sql = left(@sql,len(@sql)-1) -- remove trailing comma
SET @sql = @sql + ' from ' + @Table_Name
EXEC sp_executesql @sql
