Query statement select * from A field where 1="60" to investigate A table field 1 equals 60 information,
But I have A table field 1 to 10 10 column if I want to check all of the fields of data is equal to 60 how to query?
You must write A select * from A field where 1="60" or field 2="60" or 3="60" or 4="60" or field 5="60" or field 6="60" like this?
Is there A good way to help me get A table equals 60 query all the content from?

CodePudding user response:

You must write A select * from A field where 1="60" or field 2="60" or 3="60" or 4="60" or field 5="60" or field 6="60" like this?
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Have to write, so no shortcuts.
Query field is numeric, and after add index quickly. Only spell trouble yourself SQL string

CodePudding user response:

Too painful ah ~!! It seems also can only use the most stupid method

CodePudding user response:

Writing of high efficiency and good

CodePudding user response:

Select * from test where 60 in (A, B, C, D... )

CodePudding user response:

All of the fields of data is equal to 60
-- -- -- -- -- -- -- -- -- --
Should be and not or?

After all, went up to the name of each field or knock, how to write that save trouble,

CodePudding user response:

To change the database, this is what the database, doesn't even have a primary key, or you won't be directly to check the value of the primary key,

CodePudding user response:

Using dynamic SQL to achieve, assuming your table structure for
The CREATE TABLE [dbo] [Items] (
[Id]/int IDENTITY (1, 1) NOT NULL,
[Item1] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[Item2] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[Item3] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON the (PRIMARY)


DECLARE @ ix int, @ pos int, @ STR varchar (1000), @ list varchar (1000), @ SQL varchar (4000)
Set @ list='1, 2, 3' - here you can define to 10 and so on
The SET @ pos=1
The SET @ ix=1
The SET @ SQL='Select * from the Items where'
WHILE @ ix & gt; 0
The BEGIN
The SET @ ix=charindex (', '@ list, @ pos)
IF @ ix & gt; 0
The SET @ STR=the substring (@ list, @ pos, @ ix - @ pos)
The ELSE
The SET @ STR=the substring (@ list, @ pos, len (@ list))
The SET @ STR=ltrim (rtrim (@ STR))
SQL=SET @ @ + SQL 'Item' + @ STR + '=' '60' and '
The SET @ pos=@ ix + 1
END
SQL=SET @ @ SQL + '1=1
- SELECT @ SQL
The exec (@ SQL)

CodePudding user response:

Dynamic more trouble, there will be upstairs with respect to OK, clear, easy to understand

CodePudding user response:

Traverse A table field + TStringList can also be efficient SQL statements that joining together quickly,

CodePudding user response:

The most stupid way is also the most easy to understand

CodePudding user response:

Also write in standard SQL statement
So easy to understand

CodePudding user response:

The easiest way to silly be good way to knock a few more lines of code

CodePudding user response:

refer to 7th floor thinkofblue response:

using dynamic SQL to achieve, assuming your table structure for
The CREATE TABLE [dbo] [Items] (
[Id]/int IDENTITY (1, 1) NOT NULL,
[Item1] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[Item2] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[Item3] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON the (PRIMARY)


DECLARE @ ix int, @ pos int, @ STR varchar (1000), @ list varchar (1000), @ SQL varchar (4000)
Set @ list='1, 2, 3' - here you can define to 10 and so on
The SET @ pos=1
The SET @ ix=1
The SET @ SQL='Select * from the Items where'
WHILE @ ix & gt; 0
The BEGIN
The SET @ ix=charindex (', '@ list, @ pos)
IF @ ix & gt; 0
The SET @ STR=the substring (@ list, @ pos, @ ix - @ pos)
The ELSE
The SET @ STR=the substring (@ list, @ pos, len (@ list))
The SET @ STR=ltrim (rtrim (@ STR))
SQL=SET @ @ + SQL 'Item' + @ STR + '=' '60' and '
The SET @ pos=@ ix + 1
END
SQL=SET @ @ SQL + '1=1
- SELECT @ SQL
The exec SQL) (@

This is very good