表结构:
CREATE TABLE [dbo].[station] (
[station1] [int] NOT NULL ,
[station2] [int] NOT NULL ,
[station3] [int] NOT NULL ,
[station4] [int] NOT NULL ,
[station5] [int] NOT NULL ,
) ON [PRIMARY]
GOALTER TABLE [dbo].[station] WITH NOCHECK ADD
CONSTRAINT [PK_station] PRIMARY KEY CLUSTERED
(
[station1],
[station2],
[station3],
[station4],
[station5]
) ON [PRIMARY]
GO我有5个数,假设是s1、s2、s3、s4、s5,我要用这5个数去station表找出最匹配的记录
匹配过程是这样的:
先用s1和station1、s2和station2、s3和station3、s4和station4、s5和station5判断相等,如果5栏全相等,就选出这个记录,否则再选出前4栏相等的,即s1和station1、s2和station2、s3和station3、s4和station4相等的,如此下去,直到只有s1和station1相等。换个说法,按优先级排序,5栏全相等,优先级最高,其次是前4栏相等,再其次是前3栏相等,如此下去,最低优先级是只有第一栏相等,选出表中优先级最高的记录。请问这样的SQL如何写?
CREATE TABLE [dbo].[station] (
[station1] [int] NOT NULL ,
[station2] [int] NOT NULL ,
[station3] [int] NOT NULL ,
[station4] [int] NOT NULL ,
[station5] [int] NOT NULL ,
) ON [PRIMARY]
GOALTER TABLE [dbo].[station] WITH NOCHECK ADD
CONSTRAINT [PK_station] PRIMARY KEY CLUSTERED
(
[station1],
[station2],
[station3],
[station4],
[station5]
) ON [PRIMARY]
GO我有5个数,假设是s1、s2、s3、s4、s5,我要用这5个数去station表找出最匹配的记录
匹配过程是这样的:
先用s1和station1、s2和station2、s3和station3、s4和station4、s5和station5判断相等,如果5栏全相等,就选出这个记录,否则再选出前4栏相等的,即s1和station1、s2和station2、s3和station3、s4和station4相等的,如此下去,直到只有s1和station1相等。换个说法,按优先级排序,5栏全相等,优先级最高,其次是前4栏相等,再其次是前3栏相等,如此下去,最低优先级是只有第一栏相等,选出表中优先级最高的记录。请问这样的SQL如何写?
这样可以缩小范围。
--TRY:
SELECT TOP 1 * FROM station
ORDER BY
CASE WHEN station1=@s1 THEN 32 ELSE 0 END
+
CASE WHEN station2=@s2 THEN 16 ELSE 0 END
+
CASE WHEN station3=@s3 THEN 8 ELSE 0 END
+
CASE WHEN station4=@s4 THEN 4 ELSE 0 END
+
CASE WHEN station5=@s5 THEN 1 ELSE 0 END
DESC