SQL은 SELECT * [columnA 제외] FROM tableA를 사용하여 열을 제외합니까?
우리 모두는 테이블에서 모든 열을 선택하기 위해
SELECT * FROM tableA
모든 열을 지정하지 않고 테이블에서 열을 제외하는 방법이 있습니까?
SELECT * [except columnA] FROM tableA
내가 아는 유일한 방법은 모든 열을 수동으로 지정하고 원하지 않는 열을 제외하는 것입니다. 이것은 정말 시간이 많이 걸리므로 테이블에 열이 더 많거나 적을 경우 향후 유지 관리뿐만 아니라 이에 대한 시간과 노력을 절약 할 방법을 찾고 있습니다.
나는 모든 사람에게 동의한다 ...하지만 내가 이런 식으로한다면 나는 이렇게 할 수있다.
/* Get the data into a temp table */
SELECT * INTO #TempTable
FROM YourTable
/* Drop the columns that are not needed */
ALTER TABLE #TempTable
DROP COLUMN ColumnToDrop
/* Get results and drop temp table */
SELECT * FROM #TempTable
DROP TABLE #TempTable
아니.
유지 관리가 간단한 모범 사례는 필수 열만 지정하는 것입니다.
최소한 2 가지 이유 :
- 이것은 클라이언트와 데이터베이스 간의 계약을 안정적으로 만듭니다. 매번 동일한 데이터
- 성능, 인덱스 포함
편집 (2011 년 7 월) :
Object Explorer에서 Columns테이블 의 노드를 드래그 하면 목표 중 하나를 달성하는 쿼리 창에 CSV 열 목록이 표시됩니다.
SQL (SQL Server)에서이 작업을 자동화하는 방법은 다음과 같습니다.
declare @cols varchar(max), @query varchar(max);
SELECT @cols = STUFF
(
(
SELECT DISTINCT '], [' + name
FROM sys.columns
where object_id = (
select top 1 object_id from sys.objects
where name = 'MyTable'
)
and name not in ('ColumnIDontWant1', 'ColumnIDontWant2')
FOR XML PATH('')
), 1, 2, ''
) + ']';
SELECT @query = 'select ' + @cols + ' from MyTable';
EXEC (@query);
각 열 이름을 수동으로 작성하지 않으려면 Script Table As다음 과 같이 SSMS 에서 테이블 또는 보기 를 마우스 오른쪽 단추로 클릭하여 사용할 수 있습니다 .
그런 다음 새 쿼리 편집기 창 에서 전체 선택 쿼리를 얻은 다음 다음과 같이 원하지 않는 열을 제거합니다.
끝난
선택하려는 열이있는보기를 만든 다음보기에서 *를 선택하기 만하면됩니다.
예, 가능합니다 (권장되지 않음).
CREATE TABLE contact (contactid int, name varchar(100), dob datetime)
INSERT INTO contact SELECT 1, 'Joe', '1974-01-01'
DECLARE @columns varchar(8000)
SELECT @columns = ISNULL(@columns + ', ','') + QUOTENAME(column_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'contact' AND COLUMN_NAME <> 'dob'
ORDER BY ORDINAL_POSITION
EXEC ('SELECT ' + @columns + ' FROM contact')
코드 설명 :
- 쉼표로 구분 된 열 이름 목록을 저장할 변수를 선언하십시오. 기본값은 NULL입니다.
- 시스템보기를 사용하여 테이블의 열 이름을 결정하십시오.
SELECT @variable = @variable + ... FROM열 이름을 연결하는 데 사용 합니다. 이 유형은SELECT결과 세트를 리턴하지 않습니다. 이것은 문서화되지 않은 동작이지만 모든 버전의 SQL Server에서 작동합니다. 대안으로SET @variable = (SELECT ... FOR XML PATH(''))문자열을 연결 하는 데 사용할 수 있습니다 .ISNULL첫 번째 열 이름이 아닌 경우에만 함수를 사용하여 쉼표를 앞에 추가 하십시오 .QUOTENAME함수를 사용하여 열 이름에 공백과 구두점을 지원합니다.- 이
WHERE절을 사용하여 보고 싶지 않은 열을 숨 깁니다. - 동적 SQL
EXEC (@variable)이라고도하는를 사용 하여 런타임에 열 이름을 확인합니다. 이것은 컴파일 타임에 열 이름을 모르기 때문에 필요합니다.
다른 사람들이 이것을 할 방법이 없다고 말했듯이 Sql Server를 사용하는 경우 내가 사용하는 트릭은 출력을 쉼표로 구분하여 변경하는 것입니다.
select top 1 * from table
출력 창에서 전체 열 목록을 잘라냅니다. 그런 다음 모두 입력하지 않고도 원하는 열을 선택할 수 있습니다.
기본적으로 원하는 것을 할 수는 없지만 작업을 좀 더 쉽게 만드는 데 도움이되는 올바른 도구를 얻을 수 있습니다.
Red-Gate의 SQL 프롬프트 를 보면 "SELECT * FROM MyTable"을 입력 한 다음 커서를 "*"뒤로 이동하고 <TAB>을 눌러 필드 목록을 확장하고 해당 필드를 제거 할 수 있습니다. 필요 없습니다.
완벽한 솔루션은 아니지만 좋은 솔루션입니다! :-) MS SQL Server Management Studio의 Intellisense는 여전히이 기능을 제공 할만큼 지능적이지 않습니다 .......
마크
이것을 할 방법이 없습니다. 상황에서 가능하다면 커스텀 뷰를 만들 수 있습니다.
EDIT DB가 동적 SQL 실행을 지원하는 경우 u는 SP를 작성하고보고 싶지 않은 열을 전달하고 쿼리를 동적으로 생성하고 결과를 반환 할 수 있습니다. 나는 이것이 SQL Server에서 적어도 할 수 있다고 생각합니다.
SQL Server Management Studio를 사용하는 경우 다음을 수행하십시오.
- 원하는 테이블 이름을 입력하고 선택하십시오
- Alt+를 누릅니다.F1
- o / p는 테이블의 열을 보여줍니다.
- 원하는 열을 선택하십시오.
- 선택한 검색어에 복사하여 붙여 넣기
- 쿼리를 실행하십시오.
즐겨.
요약하면 할 수 없지만 위의 모든 설명에 동의하지 않습니다. 전체 목록에서 특정 범위를 선택하기 위해 중첩 된 쿼리를 만들 때 * 페이징) 왜 내부에서 수행했을 때 외부 select 문에 각 열을 지정하려고할까요?
SQL Management Studio에서 개체 탐색기의 열을 확장 한 다음 Columns트리 항목을 쿼리 창으로 끌어 쉼표로 구분 된 열 목록을 가져올 수 있습니다.
DECLARE @SQL VARCHAR(max), @TableName sysname = 'YourTableName'
SELECT @SQL = COALESCE(@SQL + ', ', '') + Name
FROM sys.columns
WHERE OBJECT_ID = OBJECT_ID(@TableName)
AND name NOT IN ('Not This', 'Or that');
SELECT @SQL = 'SELECT ' + @SQL + ' FROM ' + @TableName
EXEC (@SQL)
최신 정보:
더 자주 사용하는 경우이 작업을 처리하는 저장 프로 시저를 만들 수도 있습니다. 이 예제에서는 SQL Server 2016+에서 사용할 수 있는 기본 제공 STRING_SPLIT () 를 사용했지만 필요한 경우 SO에서 수동으로 만드는 방법에 대한 예제가 많이 있습니다.
CREATE PROCEDURE [usp_select_without]
@schema_name sysname = N'dbo',
@table_name sysname,
@list_of_columns_excluded nvarchar(max),
@separator nchar(1) = N','
AS
BEGIN
DECLARE
@SQL nvarchar(max),
@full_table_name nvarchar(max) = CONCAT(@schema_name, N'.', @table_name);
SELECT @SQL = COALESCE(@SQL + ', ', '') + QUOTENAME([Name])
FROM sys.columns sc
LEFT JOIN STRING_SPLIT(@list_of_columns_excluded, @separator) ss ON sc.[name] = ss.[value]
WHERE sc.OBJECT_ID = OBJECT_ID(@full_table_name, N'u')
AND ss.[value] IS NULL;
SELECT @SQL = N'SELECT ' + @SQL + N' FROM ' + @full_table_name;
EXEC(@SQL)
END
And then just:
EXEC [usp_select_without]
@table_name = N'Test_Table',
@list_of_columns_excluded = N'ID, Date, Name';
If we are talking of Procedures, it works with this trick to generate a new query and EXECUTE IMMEDIATE it:
SELECT LISTAGG((column_name), ', ') WITHIN GROUP (ORDER BY column_id)
INTO var_list_of_columns
FROM ALL_TAB_COLUMNS
WHERE table_name = 'PUT_HERE_YOUR_TABLE'
AND column_name NOT IN ('dont_want_this_column','neither_this_one','etc_column');
Is there a way to exclude column(s) from a table without specifying all the columns?
Using declarative SQL in the usual way, no.
I think your proposed syntax is worthy and good. In fact, the relational database language 'Tutorial D' has a very similar syntax where the keywords ALL BUT are followed by a set of attributes (columns).
However, SQL's SELECT * already gets a lot a flak (@Guffa's answer here is a typical objection), so I don't think SELECT ALL BUT will get into the SQL Standard anytime soon.
I think the best 'work around' is to create a VIEW with only the columns you desire then SELECT * FROM ThatView.
Postgres sql has a way of doing it
The Information Schema Hack Way
SELECT 'SELECT ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
FROM information_schema.columns As c
WHERE table_name = 'officepark'
AND c.column_name NOT IN('officeparkid', 'contractor')
), ',') || ' FROM officepark As o' As sqlstmt
The above for my particular example table - generates an sql statement that looks like this
SELECT o.officepark,o.owner,o.squarefootage FROM officepark As o
I do not know of any database that supports this (SQL Server, MySQL, Oracle, PostgreSQL). It is definitely not part of the SQL standards so I think you have to specify only the columns you want.
You could of course build your SQL statement dynamically and have the server execute it. But this opens up the possibility for SQL injection..
I know this is a little old, but I had just run into the same issue and was looking for an answer. Then I had a senior developer show me a very simple trick.
If you are using the management studio query editor, expand the database, then expand the table that you are selecting from so that you can see the columns folder.
In your select statement, just highlight the referenced columns folder above and drag and drop it into the query window. It will paste all of the columns of the table, then just simply remove the identity column from the list of columns...
The best way to solve this is using view you can create view with required columns and retrieve data form it
example
mysql> SELECT * FROM calls;
+----+------------+---------+
| id | date | user_id |
+----+------------+---------+
| 1 | 2016-06-22 | 1 |
| 2 | 2016-06-22 | NULL |
| 3 | 2016-06-22 | NULL |
| 4 | 2016-06-23 | 2 |
| 5 | 2016-06-23 | 1 |
| 6 | 2016-06-23 | 1 |
| 7 | 2016-06-23 | NULL |
+----+------------+---------+
7 rows in set (0.06 sec)
mysql> CREATE VIEW C_VIEW AS
-> SELECT id,date from calls;
Query OK, 0 rows affected (0.20 sec)
mysql> select * from C_VIEW;
+----+------------+
| id | date |
+----+------------+
| 1 | 2016-06-22 |
| 2 | 2016-06-22 |
| 3 | 2016-06-22 |
| 4 | 2016-06-23 |
| 5 | 2016-06-23 |
| 6 | 2016-06-23 |
| 7 | 2016-06-23 |
+----+------------+
7 rows in set (0.00 sec)
Well, it is a common best practice to specify which columns you want, instead of just specifying *. So you should just state which fields you want your select to return.
A colleage advised a good alternative:
- Do SELECT INTO in your preceding query (where you generate or get the data from) into a table (which you will delete when done). This will create the structure for you.
- Do a script as CREATE to new query window.
- Remove the unwanted columns. Format the remaining columns into a 1 liner and paste as your column list.
- Delete the table you created.
Done...
This helped us a lot.
Right click table in Object Explorer, Select top 1000 rows
It'll list all columns and not *. Then remove the unwanted column(s). Should be much faster than typing it yourself.
Then when you feel this is a bit too much work, get Red Gate's SQL Prompt, and type ssf from tbl, go to the * and click tab again.
That what I use often for this case:
declare @colnames varchar(max)=''
select @colnames=@colnames+','+name from syscolumns where object_id(tablename)=id and name not in (column3,column4)
SET @colnames=RIGHT(@colnames,LEN(@colnames)-1)
@colnames looks like column1,column2,column5
Sometimes the same program must handle different database stuctures. So I could not use a column list in the program to avoid errors in select statements.
* gives me all the optional fields. I check if the fields exist in the data table before use. This is my reason for using * in select.
This is how I handle excluded fields:
Dim da As New SqlDataAdapter("select * from table", cn)
da.FillSchema(dt, SchemaType.Source)
Dim fieldlist As String = ""
For Each DC As DataColumn In DT.Columns
If DC.ColumnName.ToLower <> excludefield Then
fieldlist = fieldlist & DC.Columnname & ","
End If
Next
I know this question is old, but I hope this can still be helpful.The answer is inspired by a discuss from SQL Server Forums. You can make this a stored procedure. It can also be modified to add more than one except fields.
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL + ', ', ' ' ) + name from sys.columns where name not in ('colName1','colName2') and object_id = (Select id from sysobjects where name = 'tblName')
SELECT @SQL = 'SELECT ' + @SQL + ' FROM ' + 'tblName'
EXEC sp_executesql @SQL
Wouldn't it be simpler to do this:
sp_help <table_name>
-Click on the 'Column_name' column> Copy> Paste (creates a vertical list) into a New Query window and just type commas in front of each column value... comment out the columns you don't want... far less typing than any code offered here and still manageable.
You can get SQL Complete from devart.com, which not just expands the * wildcard just like SQL Prompt from Red Gate does (as described in cairnz's answer), but also provides a column picker drop down with checkboxes in which you can check all the columns that you want in the select list and they will be inserted automatically for you (and if you then uncheck a column it will be automatically removed from the select list).
In SSMS there is an easier way with IntelliSense and Aliasing. Try this
- 텍스트 편집기에서 마우스 오른쪽 단추를 클릭하고 IntelliSense 가 활성화되어 있는지 확인 합니다.
- 별칭 [SELECT t. * FROM tablename t]를 사용 하여 쿼리를 입력합니다 .
- 텍스트 이동 *. t을 하고 삭제 *을 의 열 및 SSMS 의지 자동 목록 F 별칭 테이블을.
이것은 데이터베이스에서로드하는 시간을 절약하지 않습니다. 그러나 배열에서 원하지 않는 열은 항상 설정 해제 할 수 있습니다. 테이블에 여러 열이 있지만 특정 열은 원하지 않았습니다. SELECT 문에 모두 작성하기에는 너무 게으르다.
$i=0;
$row_array = array();
while($row = mysqli_fetch_assoc($result)){
$row_array[$i]=$row;
unset($row_array[$i]['col_name']);
$i++;
}
나는 이것을 이렇게했고 잘 작동합니다 (버전 5.5.41).
# prepare column list using info from a table of choice
SET @dyn_colums = (SELECT REPLACE(
GROUP_CONCAT(`COLUMN_NAME`), ',column_name_to_remove','')
FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE
`TABLE_SCHEMA`='database_name' AND `TABLE_NAME`='table_name');
# set sql command using prepared columns
SET @sql = CONCAT("SELECT ", @dyn_colums, " FROM table_name");
# prepare and execute
PREPARE statement FROM @sql;
EXECUTE statement;
'Programing' 카테고리의 다른 글
| 파일의 전체 경로를 얻는 방법은 무엇입니까? (0) | 2020.10.02 |
|---|---|
| glob ()을 사용하여 파일을 재귀 적으로 찾는 방법은 무엇입니까? (0) | 2020.10.02 |
| IEnumerable의 동적 LINQ OrderBy (0) | 2020.10.02 |
| Java에서 현재 날짜 / 시간을 얻는 방법 (0) | 2020.10.02 |
| PyPy가 6.3 배 빠르면 CPython을 통해 PyPy를 사용하지 않는 이유는 무엇입니까? (0) | 2020.10.02 |