SQL 返回表的前两列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4924365/
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 to return first two columns of a table
提问by Jez Clark
Is there any SQLlingo to return JUST the first two columnsof a table WITHOUTknowing the field names?
是否有任何SQL行话可以在不知道字段名称的情况下仅返回columns表的前两个？
Something like
就像是
SELECT Column(1), Column(2) FROM Table_Name
Or do I have to go the long way around and find out the column names first? How would I do that?
或者我必须走很长的路，先找出列名？我该怎么做？
回答by Ken Downs
You have to get the column names first. Most platforms support this:
您必须先获取列名。大多数平台都支持这个：
select column_name,ordinal_position
from information_schema.columns
where table_schema = ...
and table_name = ...
and ordinal_position <= 2
回答by pcofre
There it′s
在那里
declare @select varchar(max)
set @select = 'select '
select @select=@select+COLUMN_NAME+','
from information_schema.columns
where table_name = 'TABLE' and ordinal_position <= 2
set @select=LEFT(@select,LEN(@select)-1)+' from TABLE'
exec(@select)
回答by user2306008
I wrote a stored procedure a while back to do this exact job. Even though in relational theory there is no technical column order SSMS is not completely relational. The system stores the order in which the columns were inserted and assigns an ID to them. This order is followed using the typical SELECT * statement which is why your SELECT statements appear to return the same order each time. In practice its never a good idea to SELECT * with anything as it doesn't lock the result order in terms of columns or rows. That said I think people get so stuck on 'you shouldn't do this' that they don't write scripts that actually can do it. Fact is there is predictable system behavior so why not use it if the task isn't super important.
不久前我写了一个存储过程来完成这项工作。即使在关系理论中没有技术列顺序，SSMS 也不是完全相关的。系统存储列的插入顺序并为其分配 ID。使用典型的 SELECT * 语句遵循此顺序，这就是为什么您的 SELECT 语句似乎每次都返回相同的顺序。在实践中，用任何东西 SELECT * 从来都不是一个好主意，因为它不会根据列或行锁定结果顺序。也就是说，我认为人们太执着于“你不应该这样做”，以至于他们不会编写实际上可以做到的脚本。事实是存在可预测的系统行为，所以如果任务不是非常重要，为什么不使用它。
This SPROC of course has caveats and is written in T-SQL but if your looking to just return all of the values with the same behavior of SELECT * then this should do the job pretty easy for you. Put in your table name, the amount of columns, and hit F5. It returns them in order from left to right the same as you'd be expecting. I limited it to only 5 columns but you can edit the logic if you need any more. Takes both temp and permanent tables.
这个 SPROC 当然有警告并且是用 T-SQL 编写的，但是如果您只想返回具有 SELECT * 相同行为的所有值，那么这对您来说应该很容易完成。输入您的表名、列数，然后按 F5。它按照您所期望的从左到右的顺序返回它们。我将其限制为 5 列，但如果您需要更多，您可以编辑逻辑。使用临时表和永久表。
EXEC OnlySomeColumns 'MyTable', 3
EXEC OnlySomeColumns 'MyTable', 3
/*------------------------------------------------------------------------------------------------------------------
Document Title: The Unknown SELECT SPROC.sql
Created By: CR
Date: 4.28.2013
Purpose: Returns all results from temp or permanent table when not knowing the column names
SPROC Input Example: EXEC OnlySomeColumns 'MyTable', 3
--------------------------------------------------------------------------------------------------------------------*/
IF OBJECT_ID ('OnlySomeColumns', 'P') IS NOT NULL
DROP PROCEDURE OnlySomeColumns;
GO
CREATE PROCEDURE OnlySomeColumns
@TableName VARCHAR (1000),
@TotalColumns INT
AS
DECLARE @Column1 VARCHAR (1000),
@Column2 VARCHAR (1000),
@Column3 VARCHAR (1000),
@Column4 VARCHAR (1000),
@Column5 VARCHAR (1000),
@SQL VARCHAR (1000),
@TempTable VARCHAR (1000),
@PermanentTable VARCHAR (1000),
@ColumnNamesAll VARCHAR (1000)
--First determine if this is a temp table or permanent table
IF @TableName LIKE '%#%' BEGIN SET @TempTable = @TableName END --If a temporary table
IF @TableName NOT LIKE '%#%' BEGIN SET @PermanentTable = @TableName END --If a permanent column name
SET NOCOUNT ON
--Start with a few simple error checks
IF ( @TempTable = 'NULL' AND @PermanentTable = 'NULL' )
BEGIN
RAISERROR ( 'ERROR: Please select a TempTable or Permanent Table.',16,1 )
END
IF ( @TempTable <> 'NULL' AND @PermanentTable <> 'NULL' )
BEGIN
RAISERROR ( 'ERROR: Only one table can be selected at a time. Please adjust your table selection.',16,1 )
END
IF ( @TotalColumns IS NULL )
BEGIN
RAISERROR ( 'ERROR: Please select a value for @TotalColumns.',16,1 )
END
--Temp table to gather the names of the columns
IF Object_id('tempdb..#TempName') IS NOT NULL DROP TABLE #TempName
CREATE TABLE #TempName ( ID INT, Name VARCHAR (1000) )
--Select the column order from a temp table
IF @TempTable <> 'NULL'
BEGIN
--Verify the temp table exists
IF NOT EXISTS ( SELECT 1
FROM tempdb.sys.columns
WHERE object_id = object_id ('tempdb..' + @TempTable +'') )
BEGIN
RAISERROR ( 'ERROR: Your TempTable does not exist - Please select a valid TempTable.',16,1 )
RETURN
END
SET @SQL = 'INSERT INTO #TempName
SELECT column_id AS ID, Name
FROM tempdb.sys.columns
WHERE object_id = object_id (''tempdb..' + @TempTable +''')
ORDER BY column_id'
EXEC (@SQL)
END
--From a permanent table
IF @PermanentTable <> 'NULL'
BEGIN
--Verify the temp table exists
IF NOT EXISTS ( SELECT 1
FROM syscolumns
WHERE id = ( SELECT id
FROM sysobjects
WHERE Name = '' + @PermanentTable + '' ) )
BEGIN
RAISERROR ( 'ERROR: Your Table does not exist - Please select a valid Table.',16,1 )
RETURN
END
SET @SQL = 'INSERT INTO #TempName
SELECT colorder AS ID, Name
FROM syscolumns
WHERE id = ( SELECT id
FROM sysobjects
WHERE Name = ''' + @PermanentTable + ''' )
ORDER BY colorder'
EXEC (@SQL)
END
--Set the names of the columns
IF @TotalColumns >= 1 BEGIN SET @Column1 = (SELECT Name FROM #TempName WHERE ID = 1) END
IF @TotalColumns >= 2 BEGIN SET @Column2 = (SELECT Name FROM #TempName WHERE ID = 2) END
IF @TotalColumns >= 3 BEGIN SET @Column3 = (SELECT Name FROM #TempName WHERE ID = 3) END
IF @TotalColumns >= 4 BEGIN SET @Column4 = (SELECT Name FROM #TempName WHERE ID = 4) END
IF @TotalColumns >= 5 BEGIN SET @Column5 = (SELECT Name FROM #TempName WHERE ID = 5) END
--Create a select list of only the column names you want
IF Object_id('tempdb..#FinalNames') IS NOT NULL DROP TABLE #FinalNames
CREATE TABLE #FinalNames ( ID INT, Name VARCHAR (1000) )
INSERT #FinalNames
SELECT '1' AS ID, @Column1 AS Name UNION ALL
SELECT '2' AS ID, @Column2 AS Name UNION ALL
SELECT '3' AS ID, @Column3 AS Name UNION ALL
SELECT '4' AS ID, @Column4 AS Name UNION ALL
SELECT '5' AS ID, @Column5 AS Name
--Comma Delimite the names to insert into a select statement. Bracket the names in case there are spaces
SELECT @ColumnNamesAll = COALESCE(@ColumnNamesAll + '], [' ,'[') + Name
FROM #FinalNames
WHERE Name IS NOT NULL
ORDER BY ID
--Add an extra bracket at the end to complete the string
SELECT @ColumnNamesAll = @ColumnNamesAll + ']'
--Tell the user if they selected to many columns
IF ( @TotalColumns > 5 AND EXISTS (SELECT 1 FROM #FinalNames WHERE Name IS NOT NULL) )
BEGIN
SELECT 'This script has been designed for up to 5 columns' AS ERROR
UNION ALL
SELECT 'Only the first 5 columns have been selected' AS ERROR
END
IF Object_id('tempdb..#FinalNames') IS NOT NULL DROP TABLE ##OutputTable
--Select results using only the Columns you wanted
IF @TempTable <> 'NULL'
BEGIN
SET @SQL = 'SELECT ' + @ColumnNamesAll + '
INTO ##OutputTable
FROM ' + @TempTable + '
ORDER BY 1'
EXEC (@SQL)
END
IF @PermanentTable <> 'NULL'
BEGIN
SET @SQL = 'SELECT ' + @ColumnNamesAll + '
INTO ##OutputTable
FROM ' + @PermanentTable + '
ORDER BY 1'
EXEC (@SQL)
END
SELECT *
FROM ##OutputTable
SET NOCOUNT OFF
回答by NeverHopeless
A dynamic queryusing for xml pathwill also do the job:
一个dynamic query使用for xml path也将做的工作：
declare @sql varchar(max)
set @sql = (SELECT top 2 COLUMN_NAME + ',' from information_schema.columns where table_name = 'YOUR_TABLE_NAME_HERE' order by ordinal_position for xml path(''))
set @sql = (SELECT replace(@sql +' ',', ',''))
exec('SELECT ' + @sql + ' from YOUR_TABLE_NAME_HERE')
回答by Ned Batchelder
SQL doesn't understand the order of columns. You need to know the column names to get them.
SQL 不理解列的顺序。您需要知道列名才能获得它们。
You can look into querying the information_schema to get the column names. For example:
您可以查看查询 information_schema 以获取列名。例如：
SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tbl_name'
ORDER BY ordinal_position
LIMIT 2;
回答by odez213
You can query the sysobject of the table to find out the first two column then dynamically generate the SQL statement you need.
你可以查询表的sysobject，找出前两列，然后动态生成你需要的SQL语句。
回答by Scott Bruns
If you want a permant object that you can query over and over again make a view for each table that only returns the first 2 columns. You can name the columns Column1 and Column2 or use the existing names.
如果您想要一个可以反复查询的永久对象，请为每个只返回前 2 列的表创建一个视图。您可以命名列 Column1 和 Column2 或使用现有名称。
If you want to return the first two columns from any table without any preprocessing steps create a stored procedure that queries the system information and executes a dynamic query that return the first two columns from the table.
如果您想在没有任何预处理步骤的情况下从任何表中返回前两列，请创建一个存储过程来查询系统信息并执行一个动态查询，从表中返回前两列。
回答by RichardTheKiwi
Or do I have to go the long way around and find out the column names first? How would I do that?
或者我必须走很长的路，先找出列名？我该怎么做？
It's pretty easy to do manually.
Just run this first
手动完成非常容易。
先运行这个
select * from tbl where 1=0
This statement works on all major DBMS without needing any system catalogs. That gives you all the column names, then all you need to do is type the first two
此语句适用于所有主要 DBMS，无需任何系统目录。这给了你所有的列名，然后你需要做的就是输入前两个
select colname1, colnum2 from tbl
