想要實作一種查詢方式，需要對位匹配，找到與查詢條件相同的位數大于6位的資料。我現在的查詢方法是
：
SELECT S.GUID from( SELECT( case when substring(lincess,1,1) = 'A' then 1 else 0 end+
case when substring(lincess,2,1) = 'B' then 1 else 0 end+
case when substring(lincess,3,1) = 'C' then 1 else 0 end+
case when substring(lincess,4,1) = 'D' then 1 else 0 end+
case when substring(lincess,5,1) = 'E' then 1 else 0 end+
case when substring(lincess,6,1) = 'F' then 1 else 0 end+
case when substring(lincess,7,1) = 'G' then 1 else 0 end) as num FROM sys_lincess ) S where num>=6 。
這種查詢方式效率上有點低，請問有沒有其他比較搞笑的查詢方式呢
uj5u.com熱心網友回復：
來個人回答下啊，著急呢uj5u.com熱心網友回復：
SELECT S.GUID from sys_lincess
where lincess like 'ABCDEFG'
or lincess like '_BCDEFG'
or lincess like 'A_CDEFG'
or lincess like 'AB_DEFG'
or lincess like 'ABC_EFG'
or lincess like 'ABCD_FG'
or lincess like 'ABCDE_G'
or lincess like 'ABCDEF_'
uj5u.com熱心網友回復：
USE tempdb
GO
IF OBJECT_ID('sys_lincess') IS NOT NULL
DROP TABLE sys_lincess
GO
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('A02','ABCKEFGHIJ');
INSERT INTO sys_lincess(pkId,lincess) VALUES('A03','ABCKEZZ');
------ 以上為測驗資料及測驗表 ---------
--1. 為此表添加計算列，計算列的邏輯同你原來的一樣，就是統計 lincess 列同 ABCDEFG 有多少個相同的
ALTER TABLE sys_lincess ADD num AS case when substring(lincess,1,1) = 'A' then 1 else 0 end+
case when substring(lincess,2,1) = 'B' then 1 else 0 end+
case when substring(lincess,3,1) = 'C' then 1 else 0 end+
case when substring(lincess,4,1) = 'D' then 1 else 0 end+
case when substring(lincess,5,1) = 'E' then 1 else 0 end+
case when substring(lincess,6,1) = 'F' then 1 else 0 end+
case when substring(lincess,7,1) = 'G' then 1 else 0 END PERSISTED
GO
--2. 在這個列上創建索引
CREATE INDEX ix_sys_lincess_num ON sys_lincess(num);
--3. 查詢
SELECT * FROM sys_lincess WHERE num>=6
因為用到了計算列，也就是說到底有多少個字符相同，一開始就計算出來了，要查詢根本是不需要再去計算的，這樣的效率是最高的。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/24384.html
標籤：基礎類
上一篇：集合大佬們
下一篇：求大佬幫忙