求教一个数据库语句,数据之间的比较

[xianyushen]

目前我有一张表T,里面有3个字段a,b,c. 其中a是主键,b和c的格式长度都相同.
表内有一百多万条数据,想比较出所有数据中a的末6位相同,且b之间相同或者c之间相同或者两组数据中b与c相同的所有数据集合.怎样的语句能达到性能最优,速度最快呢?

[吉普赛的歌]

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

[繁花尽流年]

可以考虑在1楼语句中加一句，如果T是临时表的话 DELETE FROM t WHERE a6 IN (SELECT a6 FROM #tmp_1 GROUP BY a6 HAVING COUNT(a6)=1)