如何识别有效/无效的数字组合，而每个组合被拆分为多行

Question

我有两个表

1. 有效组合范围(验证表)
2. 我的组合要验证。(我的列表表)

如何在MyListTable中识别有效或无效的组合？ 而每个组合都按 Id 和类型 ID 拆分为多行

就像下面的例子一样，验证表中有两个组合，MYListTable中有两个组合要验证。

示例表和预期结果：

=====================
** ValidationTable **
===========================================================
| ValidRangeId | TypeId | ValidNumberFrom | ValidNumberTo |
===========================================================
|      1       |    1   |       0         |     10        |
|      1       |    2   |       50        |     100       |
|      1       |    3   |       5         |     999       |
|      2       |    1   |       100       |     200       |
|      2       |    2   |       300       |     999       |
|      2       |    3   |       400       |     999       |
===========================================================
====================
** MyListTable **
===========================================
| MyRangeId    | TypeId |    MyNumber     |
===========================================
|     10       |    1   |       8         |
|     10       |    2   |       75        |
|     10       |    3   |       500       |
|     20       |    1   |       1         |
|     20       |    2   |       400       |
|     20       |    3   |       500       |
===========================================
====================
** EXPACTED RESULT **
===========================
| MyRangeId    | Result   |
===========================
|     10       | Valid    | Combination numbers are in valid range
|     20       | Invalid  | Combination numbers are NOT in valid range
===========================

10 在 MyListTable 中如何有效!!

我的组合成为 Id 10，

我的数字范围(10) => 8-75-500

组合范围变为：

验证范围(1) => 0-50-5 到 10-100-999

验证范围(2) => 100-300-400 到 200-999-999

SO MyNumberRange (8-75-500) 属于 ValidateRange(0-50-5 to 10-100-999)

因此 10 有效

HOW 20 在 MyListTable 中无效!!

我的组合成为 Id 20，

我的数字范围(20) => 1-400-500

组合范围变为：

验证范围(1) => 0-50-5 到 10-100-999

验证范围(2) => 100-300-400 到 200-999-999

所以我的号码范围 (1-400-500) 不属于验证范围(0-50-5 到 10-100-999 也不是 100-300-400 到 200-999-999)

因此 20 无效

这是上表的 SQL：(结果在此 SQL 中是固定的，只是为了解释)

IF OBJECT_ID('tempdb.dbo.#ValidationTable', 'U') IS NOT NULL  DROP TABLE #ValidationTable;
IF OBJECT_ID('tempdb.dbo.#MyListTable', 'U') IS NOT NULL  DROP TABLE #MyListTable;
IF OBJECT_ID('tempdb.dbo.#Result', 'U') IS NOT NULL  DROP TABLE #Result;
SELECT * INTO #ValidationTable
FROM (SELECT 1 ValidRangeId, 1 TypeId, 0 ValidNumberFrom, 10 ValidNumberTo UNION
SELECT 1 ValidRangeId, 2 TypeId, 50 ValidNumberFrom, 100 ValidNumberTo UNION
SELECT 1 ValidRangeId, 3 TypeId, 5 ValidNumberFrom, 999 ValidNumberTo UNION
SELECT 2 ValidRangeId, 1 TypeId, 100 ValidNumberFrom, 200 ValidNumberTo UNION
SELECT 2 ValidRangeId, 2 TypeId, 300 ValidNumberFrom, 999 ValidNumberTo UNION
SELECT 2 ValidRangeId, 3 TypeId, 400 ValidNumberFrom, 999 ValidNumberTo) v 
SELECT * INTO #MyListTable
FROM (SELECT 10 MyRangeId, 1 TypeId, 8 MyNumber UNION
SELECT 10 MyRangeId, 2 TypeId, 75 MyNumber UNION
SELECT 10 MyRangeId, 3 TypeId, 500 MyNumber UNION
SELECT 20 MyRangeId, 1 TypeId, 1 MyNumber UNION
SELECT 20 MyRangeId, 2 TypeId, 400 MyNumber UNION
SELECT 20 MyRangeId, 3 TypeId, 500 MyNumber) m
SELECT * INTO #Result
FROM (SELECT 10 MyRangeId, 'Valid' Result UNION
SELECT 20 MyRangeId, 'Invalid' Result) r
SELECT * FROM #ValidationTable
SELECT * FROM #MyListTable
SELECT * FROM #Result

Answer 1

请检查一下：

with RI as
(
select
distinct(MyRangeId) MyRangeIdDistinct
from
MyListTable
)    
select
RI.MyRangeIdDistinct MyRangeId,
case
when
(
select count(distinct(VT.ValidRangeId)) from
ValidationTable VT,
MyListTable MLT
where
RI.MyRangeIdDistinct=MLT.MyRangeId and
VT.TypeId=MLT.TypeId and
MLT.MyNumber not between VT.ValidNumberFrom and ValidNumberTo
) < (select count(distinct(VT2.ValidRangeId)) from ValidationTable VT2) 
then
'Valid'
else
'Invalid'
end Result
from
RI
order by 1;

Answer 2

好吧，测试不多，可能不是最佳解决方案，但第一场比赛可以这样添加：

with RI as
(
select
distinct(MyRangeId) MyRangeIdDistinct
from
MyListTable
)    
select
RI.MyRangeIdDistinct MyRangeId,
case
when
(
select count(distinct(VT.ValidRangeId)) from
ValidationTable VT,
MyListTable MLT
where
RI.MyRangeIdDistinct=MLT.MyRangeId and
VT.TypeId=MLT.TypeId and
MLT.MyNumber not between VT.ValidNumberFrom and VT.ValidNumberTo
) < (select count(distinct(VT2.ValidRangeId)) from ValidationTable VT2) 
then
'Valid'
else
'Invalid'
end Result,
(
select Min(VT3.ValidRangeId) from
ValidationTable VT3,
MyListTable MLT3
where
(
select count(distinct(VT4.ValidRangeId)) from
ValidationTable VT4,
MyListTable MLT4
where
RI.MyRangeIdDistinct=MLT4.MyRangeId and
VT4.TypeId=MLT4.TypeId and
MLT4.MyNumber not between VT4.ValidNumberFrom and VT4.ValidNumberTo
) = (select count(distinct(VT5.ValidRangeId)) from ValidationTable VT5
where VT5.ValidRangeId=VT3.ValidRangeId) 
) FirstMatch
from
RI
order by 1;