How to exclude a field during a select query?

If it is sqlserver, all table names and all column names are stored in it, and you can query it by using the following statement:

declare @columnlist varchar(8000), tablename varchar(255) - Defines two variables.

select @columnlist = ' ', @tablename= 'The name of the table to be queried'--Variable assignment.

select @columnlist=@columnlist+name+ ', ' from where object_id = object_id(@tablename) and name<>'The list name to be culled'--Loop through all the columns you need for the query (minus the ones you don't want).

select @columnlist=left(@columnlist,len(@columnlist)-1) - Remove the last ',' sign in the constructed statement.

exec( 'select '+@columnlist+ ' from '+@tablename) - Executes dynamic statements.

Try.

1. Create a test table, here we take creating 6 fields as an example, if there are 100 fields, the operation is similar, create table test sel cols(id number, password varchar2(200), remark1 varchar2(100), remark2 varchar2(100), remark3 varchar2(100), remark4 varchar2(100));

2. View the table structure through the system view; You can see all the fields of the newly created table;

select * from user_tab_cols t where table_name = test_sel_cols';

3. Write SQL to get all the fields with password removed from the view;

select 'select '|listagg(column_name, 'within group(order by column_id) |from test_sel_cols'知芹 str from user tab cols t where table name = test sel cols'and column name <> Li Sen'password'Completion of the disturbance;

4. Copy the query result from the previous step to generate the required SQL as follows:

select id, remark1, remark2, remark3, remark4 from test_sel_cols

It can be found that the password field is missing;

1. Create a test table, here we take creating 6 fields as an example, if there are 100 fields, the operation is similar, create table test sel cols(id number, password varchar2(200), remark1 varchar2(100), remark2 varchar2(100), remark3 varchar2(100), remark4 varchar2(100));

2. View the table structure through the system view; You can see all the fields of the newly created table;

select * from user_tab_cols t where table_name = 'test_sel_cols';

3. Write SQL to get all the fields with password removed from the view;

select 'select '||listagg(column_name, ',') within group(order by column_id) |' from test_sel_cols' str from user_tab_cols t where table_name = 'test_sel_cols' and column_name <>'password';

4. Copy the query result from the previous step to generate the required SQL as follows:

select id, remark1, remark2, remark3, remark4 from test_sel_cols

It can be found that the password field is missing;

If you want to write it in one sentence, you have to list all 99 fields one by one.

But you can use dynamic statements to get lazy.

declare @cols as varchar(4000)

select @cols = isnull(@cols,'') +name +',' from syscolumns s

where 1=1 and id = (select id from sysobjects where name ='yourtable') -and name <>'Fill in the fields you exclude here'

set @cols = left(@cols,len(@cols)-1 )

exec ('select ' + cols + ' from yourtable')

Note: This is tsql**, only for mssql

The way you want this is not there, and there are only two cases of a query list for a query statement

from [tablename] - Query all columns.

a, b, c from [tablename] - Query the specified columns.

Didn't get what you mean.

Can't you query the information in the table directly?

select

from user_col_commentswhere table_name = upper( 'The name of a table');

It should be the only way.