22,209
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
a VARCHAR(50) PRIMARY KEY,
b INT,
c INT
)
GO
--1. 增加计算列
ALTER TABLE t ADD a6 AS RIGHT(a,6) PERSISTED
--2. 增加索引
CREATE INDEX ix_t_a6_b ON t(a6,b)
CREATE INDEX ix_t_a6_c ON t(a6,c)
GO
--具体的查询语句
SELECT * FROM t AS t1 INNER JOIN t AS t2 ON t1.a!=t2.a AND t1.a6=t2.a6 AND t1.b=t2.b
UNION
SELECT * FROM t AS t1 INNER JOIN t AS t2 ON t1.a!=t2.a AND t1.a6=t2.a6 AND t1.c=t2.c
UNION
SELECT * FROM t AS t1 INNER JOIN t AS t2 ON t1.a!=t2.a AND t1.a6=t2.a6 AND t1.b=t2.c