I have a table called ClientUrls that has the following structure:
我有一个名为ClientUrls的表,它具有以下结构:
+------------+----------------+----------+
| ColumnName | DataType | Nullable |
+------------+----------------+----------+
| ClientId | INT | No |
| CountryId | INT | Yes |
| RegionId | INT | Yes |
| LanguageId | INT | Yes |
| URL | NVARCHAR(2048) | NO |
+------------+----------------+----------+
I have a stored procedure up_GetClientUrls that takes the following parameters:
我有一个存储过程up_GetClientUrls,它采用以下参数:
@ClientId INT
@CountryId INT
@RegionId INT
@LanguageId INT
Information about the proc
关于过程的信息
- All of the parameters are required by the proc and none of them will be NULL
- The aim of the proc is to return a single matching row in the table based on a pre-defined priority. The priority being ClientId>Country>Region>Language
- Three of the colums in the ClientUrls table are nullable. If one column contains a NULL, it refers to "All". e.g. if LanguageId IS NULL, then it refers to "AllLanguages". So if we send a LanguageId of 5 to the proc, we look for that first, otherwise we try and find the one that is NULL.
proc需要所有参数,它们都不是NULL
proc的目的是根据预定义的优先级返回表中的单个匹配行。优先级为ClientId>国家>地区>语言
ClientUrls表中的三个列可以为空。如果一列包含NULL,则引用“全部”。例如如果LanguageId为NULL,则它引用“AllLanguages”。因此,如果我们将一个5的LanguageId发送到proc,我们先查找它,否则我们会尝试找到一个NULL。
Matrix of priority (1 being first)
优先级矩阵(1为第一)
+---------+----------+-----------+----------+------------+
| Ranking | ClientId | CountryId | RegionId | LanguageId |
+---------+----------+-----------+----------+------------+
| 1 | NOT NULL | NOT NULL | NOT NULL | NOT NULL |
| 2 | NOT NULL | NULL | NOT NULL | NOT NULL |
| 3 | NOT NULL | NOT NULL | NULL | NOT NULL |
| 4 | NOT NULL | NULL | NULL | NOT NULL |
| 5 | NOT NULL | NOT NULL | NOT NULL | NULL |
| 6 | NOT NULL | NULL | NOT NULL | NULL |
| 7 | NOT NULL | NULL | NULL | NULL |
+---------+----------+-----------+----------+------------+
Here is some example data:
以下是一些示例数据:
+----------+-----------+----------+------------+-------------------------------+
| ClientId | CountryId | RegionId | LanguageId | URL |
+----------+-----------+----------+------------+-------------------------------+
| 1 | 1 | 1 | 1 | http://www.Website.com |
| 1 | 1 | 1 | NULL | http://www.Otherwebsite.com |
| 1 | 1 | NULL | 2 | http://www.Anotherwebsite.com |
+----------+-----------+----------+------------+-------------------------------+
Example stored proc call
示例存储的proc调用
EXEC up_GetClientUrls @ClientId = 1
,@CountryId = 1
,@RegionId = 1
,@LanguageId = 2
Expected response (based on example data)
预期响应(基于示例数据)
+----------+-----------+----------+------------+-------------------------------+
| ClientId | CountryId | RegionId | LanguageId | URL |
+----------+-----------+----------+------------+-------------------------------+
| 1 | 1 | NULL | 2 | http://www.Anotherwebsite.com |
+----------+-----------+----------+------------+-------------------------------+
This row is returned because matching on a NULL RegionId with the correct LanguageId is a higher priority than matching on a NULL LanguageId with the correct RegionId.
返回此行是因为在具有正确LanguageId的NULL RegionId上的匹配优先于在具有正确RegionId的NULL LanguageId上匹配。
Here is the code for the proc (which works). To actually get to my question, is there a better way to write this? If i extend this table in future, I'm going to just keep multiplying the number of UNION statements and therefore it isn't really scalable.
这是proc的代码(有效)。要真正回答我的问题,有没有更好的方法来写这个?如果我将来扩展这个表,我将继续增加UNION语句的数量,因此它不是真正可扩展的。
Actual stored procedure
实际存储过程
CREATE PROC up_GetClientUrls
(
@ClientId INT
,@CountryId INT
,@RegionId INT
,@LanguageId INT
)
AS
BEGIN
SELECT TOP 1
prioritised.ClientId
,prioritised.CountryId
,prioritised.RegionId
,prioritised.LanguageId
,prioritised.URL
FROM
(
SELECT
c.ClientId
,c.CountryId
,c.RegionId
,c.LanguageId
,c.URL
,1 [priority]
FROM ClientUrls c
WHERE c.ClientId = @ClientId
AND c.CountryId = @CountryId
AND c.RegionId = @RegionId
AND c.LanguageId = @LanguageId
UNION
SELECT
c.ClientId
,c.CountryId
,c.RegionId
,c.LanguageId
,c.URL
,2 [priority]
FROM ClientUrls c
WHERE c.ClientId = @ClientId
AND c.CountryId IS NULL
AND c.RegionId = @RegionId
AND c.LanguageId = @LanguageId
UNION
SELECT
c.ClientId
,c.CountryId
,c.RegionId
,c.LanguageId
,c.URL
,3 [priority]
FROM ClientUrls c
WHERE c.ClientId = @ClientId
AND c.CountryId = @CountryId
AND c.RegionId IS NULL
AND c.LanguageId = @LanguageId
UNION
SELECT
c.ClientId
,c.CountryId
,c.RegionId
,c.LanguageId
,c.URL
,4 [priority]
FROM ClientUrls c
WHERE c.ClientId = @ClientId
AND c.CountryId IS NULL
AND c.RegionId IS NULL
AND c.LanguageId = @LanguageId
UNION
SELECT
c.ClientId
,c.CountryId
,c.RegionId
,c.LanguageId
,c.URL
,5 [priority]
FROM ClientUrls c
WHERE c.ClientId = @ClientId
AND c.CountryId = @CountryId
AND c.RegionId = @RegionId
AND c.LanguageId IS NULL
UNION
SELECT
c.ClientId
,c.CountryId
,c.RegionId
,c.LanguageId
,c.URL
,6 [priority]
FROM ClientUrls c
WHERE c.ClientId = @ClientId
AND c.CountryId IS NULL
AND c.RegionId = @RegionId
AND c.LanguageId IS NULL
UNION
SELECT
c.ClientId
,c.CountryId
,c.RegionId
,c.LanguageId
,c.URL
,7 [priority]
FROM ClientUrls c
WHERE c.ClientId = @ClientId
AND c.CountryId IS NULL
AND c.RegionId IS NULL
AND c.LanguageId IS NULL
) prioritised
ORDER BY prioritised.[Priority]
END
4 个解决方案
#1
2
This is easy (if i understand you correctly). You can do it with very little code. Plus it would be easy to extend if needed.
这很容易(如果我理解正确的话)。你可以用很少的代码完成它。此外,如果需要,它很容易扩展。
Here is a working example
这是一个有效的例子
--Make a table
CREATE TABLE #ClientUrls (ClientId INT NOT NULL,CountryId INT NULL,RegionId INT NULL,LanguageId INT NULL,URL NVARCHAR(2048) NOT NULL)
--Put some data into it
INSERT INTO #ClientUrls (ClientId, CountryId, RegionId, LanguageId, URL)
VALUES
(1,1,1,1,'http://www.Website.com'),
(1,1,1,NULL,'http://www.Otherwebsite.com'),
(1,1,NULL,2,'http://www.Anotherwebsite.com')
--This would all be in your proc
----------------------------------------------
DECLARE @ClientId INT = 1
DECLARE @CountryId INT = 1
DECLARE @RegionId INT = 1
DECLARE @LanguageId INT = 2
--This is the interesting bit
----------------------------------------------
SELECT TOP 1 C.*
FROM #ClientUrls AS C
ORDER BY
--Order the ones with the best hit count near the top
IIF(ISNULL(C.ClientId, @ClientId) = @ClientId ,1,0) +
IIF(ISNULL(C.CountryId, @CountryId) = @CountryId ,2,0) +
IIF(ISNULL(C.RegionId, @RegionId) = @RegionId ,4,0) +
IIF(ISNULL(C.LanguageId,@LanguageId) = @LanguageId,8,0) DESC,
--Order the ones with the least nulls of each hit count near the top
IIF(C.ClientId IS NULL,0,1) +
IIF(C.CountryId IS NULL,0,2) +
IIF(C.RegionId IS NULL,0,4) +
IIF(C.LanguageId IS NULL,0,8) DESC
DROP TABLE #ClientUrls
Thats it. In older versions of SQL you cant use IIF, but you can replace that with a case statement if needed.
而已。在旧版本的SQL中,您无法使用IIF,但如果需要,可以使用case语句替换它。
It works like this.
它的工作原理如下。
Each matching item is given a value (a bit like in a binary number) Then based on each matching item we use the value or 0 if its not a match by adding up the total we will always pick the best combination of matches.
每个匹配项都给出一个值(有点像二进制数)然后根据每个匹配项我们使用值或0如果它不匹配通过累加总数我们将始终选择最佳匹配组合。
value 1 2 4 8 Total value
+---------+----------+-----------+----------+------------+
| Ranking | ClientId | CountryId | RegionId | LanguageId |
+---------+----------+-----------+----------+------------+
| 1 | NOT NULL | NOT NULL | NOT NULL | NOT NULL | 15
| 2 | NOT NULL | NULL | NOT NULL | NOT NULL | 13
| 3 | NOT NULL | NOT NULL | NULL | NOT NULL | 11
| 4 | NOT NULL | NULL | NULL | NOT NULL | 9
| 5 | NOT NULL | NOT NULL | NOT NULL | NULL | 7
| 6 | NOT NULL | NULL | NOT NULL | NULL | 5
| 7 | NOT NULL | NULL | NULL | NULL | 1
+---------+----------+-----------+----------+------------+
I just updated this to make sure that you get the Non null version over a null option.
我刚刚更新了这个,以确保您获得null null版本的非null版本。
If you edit the results to return more then the top 1 you can see the items in the correct order. Ie if you change language from 2 to 1 you will get the 1,1,1,1 line Over the 1,1,1,Null option
如果您编辑结果以返回多于前1,您可以按正确的顺序查看项目。即如果你将语言从2改为1,你将得到1,1,1,1行超过1,1,1,Null选项
#2
1
Not tested but you could do something like this:
没有测试,但你可以做这样的事情:
SELECT TOP 1 c.ClientId,
c.CountryId,
c.RegionId,
c.LanguageId,
c.URL
FROM ClientUrls c
ORDER BY CASE
WHEN c.ClientId = @ClientId
THEN 1000
ELSE 0
END +
CASE
WHEN c.CountryId = @CountryId
THEN 200
WHEN c.CountryId IS NULL
THEN 100
ELSE 0
END +
CASE
WHEN c.RegionId = @RegionId
THEN 20
WHEN c.CountryId IS NULL
THEN 10
ELSE 0
END +
CASE
WHEN c.LanguageId = @LanguageId
THEN 2
WHEN c.CountryId IS NULL
THEN 1
ELSE 0
END DESC
By giving a value to each match and selecting the highest value you could reduce the code needed. But you will increase the number of case statements needed, instead of the amount of unions.
通过为每个匹配项赋值并选择最高值,您可以减少所需的代码。但是你会增加所需的案例陈述数量,而不是工会数量。
This could also be a function instead of a stored procedure. So it could be used more easier in other query's
这也可以是函数而不是存储过程。所以它可以在其他查询中更容易使用
#3
0
You can change the where clause to :
您可以将where子句更改为:
AND (c.CountryID = @CountryID OR c.CountryID IS NULL)
Coding-wise, its less code. But tuning is much more problematic.
编码方式,它的代码较少。但调整更成问题。
#4
0
An alternative might be to try manipulating NULL values to create a heirarchy, something like this:
另一种方法是尝试操作NULL值来创建一个层次结构,如下所示:
WITH priorities as (SELECT
c.ClientId
,c.CountryId
,c.RegionId
,c.LanguageId
,c.URL
,COALESCE(
NULLIF(c.CountryId,@CountryId),
NULLIF(c.RegionId,@RegionId),
NULLIF(c.LanguageId,@LanguageId),
1000000)
+ ISNULL(c.CountryId,200000)
+ ISNULL(c.RegionId,100000)
+ COALESCE(c.CountryId,RegionId,40000)
+ ISNULL(c.LanguageId,10000)
+ COALESCE(c.CountryId,c.LanguageId,4000)
+ COALESCE(c.CountryId,c.RegionId,c.LanguageId,1000)
[priority]
FROM ClientUrls c
WHERE c.ClientId = @ClientId
AND (c.CountryId = @CountryId
OR c.RegionId = @RegionId
OR c.LanguageId = @LanguageId)
)
SELECT TOP 1 ClientId,CountryId,RegionId,LanguageId,URL FROM priorities ORDER BY priority DESC
#1
2
This is easy (if i understand you correctly). You can do it with very little code. Plus it would be easy to extend if needed.
这很容易(如果我理解正确的话)。你可以用很少的代码完成它。此外,如果需要,它很容易扩展。
Here is a working example
这是一个有效的例子
--Make a table
CREATE TABLE #ClientUrls (ClientId INT NOT NULL,CountryId INT NULL,RegionId INT NULL,LanguageId INT NULL,URL NVARCHAR(2048) NOT NULL)
--Put some data into it
INSERT INTO #ClientUrls (ClientId, CountryId, RegionId, LanguageId, URL)
VALUES
(1,1,1,1,'http://www.Website.com'),
(1,1,1,NULL,'http://www.Otherwebsite.com'),
(1,1,NULL,2,'http://www.Anotherwebsite.com')
--This would all be in your proc
----------------------------------------------
DECLARE @ClientId INT = 1
DECLARE @CountryId INT = 1
DECLARE @RegionId INT = 1
DECLARE @LanguageId INT = 2
--This is the interesting bit
----------------------------------------------
SELECT TOP 1 C.*
FROM #ClientUrls AS C
ORDER BY
--Order the ones with the best hit count near the top
IIF(ISNULL(C.ClientId, @ClientId) = @ClientId ,1,0) +
IIF(ISNULL(C.CountryId, @CountryId) = @CountryId ,2,0) +
IIF(ISNULL(C.RegionId, @RegionId) = @RegionId ,4,0) +
IIF(ISNULL(C.LanguageId,@LanguageId) = @LanguageId,8,0) DESC,
--Order the ones with the least nulls of each hit count near the top
IIF(C.ClientId IS NULL,0,1) +
IIF(C.CountryId IS NULL,0,2) +
IIF(C.RegionId IS NULL,0,4) +
IIF(C.LanguageId IS NULL,0,8) DESC
DROP TABLE #ClientUrls
Thats it. In older versions of SQL you cant use IIF, but you can replace that with a case statement if needed.
而已。在旧版本的SQL中,您无法使用IIF,但如果需要,可以使用case语句替换它。
It works like this.
它的工作原理如下。
Each matching item is given a value (a bit like in a binary number) Then based on each matching item we use the value or 0 if its not a match by adding up the total we will always pick the best combination of matches.
每个匹配项都给出一个值(有点像二进制数)然后根据每个匹配项我们使用值或0如果它不匹配通过累加总数我们将始终选择最佳匹配组合。
value 1 2 4 8 Total value
+---------+----------+-----------+----------+------------+
| Ranking | ClientId | CountryId | RegionId | LanguageId |
+---------+----------+-----------+----------+------------+
| 1 | NOT NULL | NOT NULL | NOT NULL | NOT NULL | 15
| 2 | NOT NULL | NULL | NOT NULL | NOT NULL | 13
| 3 | NOT NULL | NOT NULL | NULL | NOT NULL | 11
| 4 | NOT NULL | NULL | NULL | NOT NULL | 9
| 5 | NOT NULL | NOT NULL | NOT NULL | NULL | 7
| 6 | NOT NULL | NULL | NOT NULL | NULL | 5
| 7 | NOT NULL | NULL | NULL | NULL | 1
+---------+----------+-----------+----------+------------+
I just updated this to make sure that you get the Non null version over a null option.
我刚刚更新了这个,以确保您获得null null版本的非null版本。
If you edit the results to return more then the top 1 you can see the items in the correct order. Ie if you change language from 2 to 1 you will get the 1,1,1,1 line Over the 1,1,1,Null option
如果您编辑结果以返回多于前1,您可以按正确的顺序查看项目。即如果你将语言从2改为1,你将得到1,1,1,1行超过1,1,1,Null选项
#2
1
Not tested but you could do something like this:
没有测试,但你可以做这样的事情:
SELECT TOP 1 c.ClientId,
c.CountryId,
c.RegionId,
c.LanguageId,
c.URL
FROM ClientUrls c
ORDER BY CASE
WHEN c.ClientId = @ClientId
THEN 1000
ELSE 0
END +
CASE
WHEN c.CountryId = @CountryId
THEN 200
WHEN c.CountryId IS NULL
THEN 100
ELSE 0
END +
CASE
WHEN c.RegionId = @RegionId
THEN 20
WHEN c.CountryId IS NULL
THEN 10
ELSE 0
END +
CASE
WHEN c.LanguageId = @LanguageId
THEN 2
WHEN c.CountryId IS NULL
THEN 1
ELSE 0
END DESC
By giving a value to each match and selecting the highest value you could reduce the code needed. But you will increase the number of case statements needed, instead of the amount of unions.
通过为每个匹配项赋值并选择最高值,您可以减少所需的代码。但是你会增加所需的案例陈述数量,而不是工会数量。
This could also be a function instead of a stored procedure. So it could be used more easier in other query's
这也可以是函数而不是存储过程。所以它可以在其他查询中更容易使用
#3
0
You can change the where clause to :
您可以将where子句更改为:
AND (c.CountryID = @CountryID OR c.CountryID IS NULL)
Coding-wise, its less code. But tuning is much more problematic.
编码方式,它的代码较少。但调整更成问题。
#4
0
An alternative might be to try manipulating NULL values to create a heirarchy, something like this:
另一种方法是尝试操作NULL值来创建一个层次结构,如下所示:
WITH priorities as (SELECT
c.ClientId
,c.CountryId
,c.RegionId
,c.LanguageId
,c.URL
,COALESCE(
NULLIF(c.CountryId,@CountryId),
NULLIF(c.RegionId,@RegionId),
NULLIF(c.LanguageId,@LanguageId),
1000000)
+ ISNULL(c.CountryId,200000)
+ ISNULL(c.RegionId,100000)
+ COALESCE(c.CountryId,RegionId,40000)
+ ISNULL(c.LanguageId,10000)
+ COALESCE(c.CountryId,c.LanguageId,4000)
+ COALESCE(c.CountryId,c.RegionId,c.LanguageId,1000)
[priority]
FROM ClientUrls c
WHERE c.ClientId = @ClientId
AND (c.CountryId = @CountryId
OR c.RegionId = @RegionId
OR c.LanguageId = @LanguageId)
)
SELECT TOP 1 ClientId,CountryId,RegionId,LanguageId,URL FROM priorities ORDER BY priority DESC