SELECT * FROM A WHERE (SUBSTRING(sfzhm,7,6)>=CONVERT(VARCHAR(10),DATEADD(yy,-60,GETDATE()),112) AND xb='男') OR (SUBSTRING(sfzhm,7,6)>=CONVERT(VARCHAR(10),DATEADD(yy,-55,GETDATE()),112) AND xb='女')
最好把出生年月或年龄做成计算列,性别用 bit 或 tinyint 的 0/1 表示。 然后在(年龄,性别)上建索引。
SELECT * FROM A WHERE (SUBSTRING(sfzhm,7,8)>=CONVERT(VARCHAR(10),DATEADD(yy,-60,GETDATE()),112) AND xb='男') OR (SUBSTRING(sfzhm,7,8)>=CONVERT(VARCHAR(10),DATEADD(yy,-55,GETDATE()),112) AND xb='女')
然后在(年龄,性别)上建索引。
SELECT * FROM A
WHERE (SUBSTRING(sfzhm,7,8)>=CONVERT(VARCHAR(10),DATEADD(yy,-60,GETDATE()),112) AND xb='男')
OR (SUBSTRING(sfzhm,7,8)>=CONVERT(VARCHAR(10),DATEADD(yy,-55,GETDATE()),112) AND xb='女')