Want to implement a way to query, need para match, find the same as the query conditions more digits than six figures, I now query method is
:
The SELECT S.G UID from (SELECT (case when the substring (lincess, 1, 1)='A' end then 1 else 0 +
The case when the substring (lincess, 2, 1)='B' end then 1 else 0 +
The case when the substring (lincess, 3, 1)='C' end then 1 else 0 +
The case when the substring (lincess, 4, 1)='D' end then 1 else 0 +
The case when the substring (lincess, 5, 1)='E' end then 1 else 0 +
The case when the substring (lincess, 6, 1)='F' end then 1 else 0 +
Case when the substring (lincess, 7, 1)='G' then one else 0 end) as num FROM sys_lincess) S where num>=6,
This way of query efficiency is a little low, could you tell me there is no other funny way to query

CodePudding user response:

To personal answer ah, worry about it

CodePudding user response:

 
The SELECT S.G UID from sys_lincess 

Where lincess like 'ABCDEFG' 
The or lincess like '_BCDEFG' 
The or lincess like 'A_CDEFG' 
The or lincess like 'AB_DEFG' 
The or lincess like 'ABC_EFG' 
The or lincess like 'ABCD_FG' 
The or lincess like 'ABCDE_G' 
The or lincess like 'ABCDEF_' 

CodePudding user response:

 USE tempdb for 
GO 
IF OBJECT_ID (' sys_lincess) IS NOT NULL 
DROP TABLE sys_lincess 
GO 
The CREATE TABLE sys_lincess (
PkId VARCHAR (50) NOT NULL, 
Lincess VARCHAR (50), NOT NULL 
) 
GO 
INSERT INTO sys_lincess (pkId, lincess) VALUES (' A01 ', 'ABCDEFGHIJ'); 
INSERT INTO sys_lincess (pkId, lincess) VALUES (' how A02 ', 'ABCKEFGHIJ'); 
INSERT INTO sys_lincess (pkId, lincess) VALUES (' A03 ', 'ABCKEZZ'); 
-- -- -- -- -- - the above table for the test data and test -- -- -- -- -- -- -- -- -- 

-- 1. Add computed columns for this table, the logic of computed columns are the same as your original, is statistical lincess column with ABCDEFG how many of the same 
The ALTER TABLE sys_lincess ADD num AS case when the substring (lincess, 1, 1)='A' end then 1 else 0 + 
The case when the substring (lincess, 2, 1)='B' end then 1 else 0 + 
The case when the substring (lincess, 3, 1)='C' end then 1 else 0 + 
The case when the substring (lincess, 4, 1)='D' end then 1 else 0 + 
The case when the substring (lincess, 5, 1)='E' end then 1 else 0 + 
The case when the substring (lincess, 6, 1)='F' end then 1 else 0 + 
Case when the substring (lincess, 7, 1)='G' then one else 0 END PERSISTED 
GO 
- 2. Create an index on the column 
The CREATE INDEX ix_sys_lincess_num ON sys_lincess (num); 

Query - 3. 
SELECT * FROM sys_lincess WHERE num> 
=6

Because use the computed columns, that is, after all how many characters are the same, calculated from the start, to query is simply don't have to calculate, this efficiency is the highest,