blocks|key|971945|text|您不能使用标准SQL。列名与SQL中的数据不同。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|971946|如果您使用的SQL引擎具有存储列名称、类型等的元数据表，则可以在该表上进行选择。|971947|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

You cannot with standard SQL. Column names are not treated like data in 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.

blocks|key|972045|text|这将为您提供列表|type|unstyled|depth|inlineStyleRanges|entityRanges|data|972046|select+*+from+information_schema.columns+
where+table_name='table1'+and+column_name+like+'a%25'|code-block|syntax|javascript|972047|如果你想用它来构造一个查询，你可以这样做：|972048|declare+@sql+nvarchar(max)
set+@sql+=+'select+'
select+@sql+=+@sql+%2B+'['+%2B+column_name+%2B'],'
from+information_schema.columns+
where+table_name='table1'+and+column_name+like+'a%25'
set+@sql+=+left(@sql,len(@sql)-1)+--+remove+trailing+comma
set+@sql+=+@sql+%2B+'+from+table1'
exec+sp_executesql+@sql|972049|请注意，以上代码是为SQL+Server编写的。|972050|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

This will get you the list

<pre><code>select * from information_schema.columns 
where table_name='table1' and column_name like 'a%'
</code></pre>

If you want to use that to construct a query, you could do something like this:

<pre><code>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
</code></pre>

Note that the above is written for SQL Server.

blocks|key|972024|text|您需要使用视图INFORMATION_SCHEMA.COLUMNS|type|unstyled|depth|inlineStyleRanges|entityRanges|data|972025|select+COLUMN_NAME+from+INFORMATION_SCHEMA.COLUMNS+where+TABLE_NAME+=+my_table_name+AND+COLUMN_NAME+like+'a%25'|code-block|syntax|javascript|972026|要内联行，可以使用PIVOT和执行EXEC()函数。|offset|length|972027|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms177410.aspx|1|http://msdn.microsoft.com/en-us/library/ms188332.aspx^0|0|0|9|5|0|H|6|1|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@$I|X|J|Y|1|Z]|$I|10|J|11|1|12]]|A|$]]|$1|K|3|-4|5|6|7|13|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]|S|$5|N|O|P|A|$Q|T]]]]

You need to use view INFORMATION_SCHEMA.COLUMNS

<pre><code>select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = my_table_name AND COLUMN_NAME like 'a%'
</code></pre>

TO inline rows you can use <a href="http://msdn.microsoft.com/en-us/library/ms177410.aspx" rel="noreferrer">PIVOT</a> and for execution <a href="http://msdn.microsoft.com/en-us/library/ms188332.aspx" rel="noreferrer">EXEC()</a> function.

blocks|key|972126|text|这将显示表名和列名|type|unstyled|depth|inlineStyleRanges|entityRanges|data|972127|select+table_name,column_name+from+information_schema.columns
where+column_name+like+'%25breakfast%25'|code-block|syntax|javascript|972128|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This will show you the table name and column name

<pre><code>select table_name,column_name from information_schema.columns
where column_name like '%breakfast%'
</code></pre>

blocks|key|971986|text|SELECT+*+FROM+SysColumns+WHERE+Name+like+'a%25'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|971987|将为您提供列的列表，您将希望过滤更多列以将其限制到目标表|unstyled|971988|在那里，您可以构造一些即席sql|971989|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>SELECT * FROM SysColumns WHERE Name like 'a%'
</code></pre>

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

blocks|key|756548|text|以下是显示所需信息的一种很好的方式：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|756549|SELECT+B.table_catalog+as+'Database_Name',
+++++++++B.table_name+as+'Table_Name',
++++++++stuff((select+',+'+%2B+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+'%25%25'
++++++++AND+B.COLUMN_NAME+like+'%25%25'
++GROUP+BY+B.Table_Catalog,+B.Table_Name
++Order+by+1+asc|code-block|syntax|javascript|756550|在主select中的'%25%25‘之间添加任何内容，以缩小所需的表和/或列名的范围。|756551|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Here is a nice way to display the information that you want:

<pre><code>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
</code></pre>

Add anything between either '%%' in the main select to narrow down what tables and/or column names you want.

blocks|key|972174|text|对于SQL+server，Blorgbeard有一个很好的答案。如果您有一个像我这样的MySQL服务器，那么下面的代码将允许您从名称类似于某个关键短语的列中选择信息。您只需替换表名、数据库名和关键字。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|972175|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+"%25keyword%25");

SET+@burrito+=+CONCAT("SELECT+",@columnnames,"+FROM+your_table");

PREPARE+result+FROM+@burrito;
EXECUTE+result;|code-block|syntax|javascript|972176|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code>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;
</code></pre>

blocks|key|972206|text|感谢你@蓝胡子提出了这个聪明的想法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|972207|顺便说一句，Blorgbeard的查询对我不起作用，所以我编辑了它：|972208|DECLARE+@Table_Name+as+VARCHAR(50)+SET+@Table_Name+=+'MyTable'++++++++++--+put+here+you+table+name
DECLARE+@Column_Like+as+VARCHAR(20)+SET+@Column_Like+=+'%25something%25'++++--+put+here+you+element
DECLARE+@sql+NVARCHAR(MAX)+SET+@sql+=+'select+'

SELECT+@sql+=+@sql+%2B+'['+%2B+sys.columns.name+%2B+'],'
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+%2B+'+from+'+%2B+@Table_Name

EXEC+sp_executesql+@sql|code-block|syntax|javascript|972209|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Thank you @Blorgbeard for the genious idea. 

By the way Blorgbeard's query was not working for me so I edited it:

<pre><code>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
</code></pre>

I have a table with column names <code>a1,a2...,b1.b2...</code>.

How can I select all those with column names like <code>a%</code>?

sql select with column name like

我有一个列名为a1,a2...,b1.b2...的表。如何选择列名类似于a%的所有类型

问列名如下的sql select
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问列名如下的sql selectEN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问列名如下的sql select
EN