英文:
Get the all unique permutation and combinations of 'where clause conditions' for my table in SQL Server
问题
以下是翻译好的内容：
我想在一个表中看到我的数据的所有可能唯一情景。
模式 开始日期 结束日期
------------------------------------------------
0 1/1/2018 1/12/2018
0 1/5/2015 1/12/2018
0 3/8/2015 1/12/2019
0 1/1/2020 1/1/2020
0 5/5/2015 5/5/2015
1 1/1/2018 5/5/2015
1 5/8/1947 9/9/1902
1 4/4/2014 3/3/2013
1 1/4/2020 1/1/2001
1 3/8/2015 1/12/2019
1 19/11/1992 19/11/1992
我可以给出输入，如下：
运算符=[<,>,=,......]
操作数=[模式, 开始日期,结束日期,.....]
我期望的结果如下：
0 和 1/1/2018 < 1/12/2018 0 和 1/1/2020 = 1/1/2020 1 和 1/1/2018 >5/5/2015 1 和 3/8/2015<1/12/2019 1 和 19/11/1992= 19/11/1992
这些是我数据中的唯一模式。所以是否有任何查询或Java程序可以获取我数据的这些唯一模式，以便我可以测试我的应用程序以涵盖所有可能的情景。
注意：我在表中有总共6列。出于示例目的，我只提供了3列。
英文:
I would like to see all possible unique scenarios of my data in a table.
Mode StartDate EndDate
------------------------------------------------
0 1/1/2018 1/12/2018
0 1/5/2015 1/12/2018
0 3/8/2015 1/12/2019
0 1/1/2020 1/1/2020
0 5/5/2015 5/5/2015
1 1/1/2018 5/5/2015
1 5/8/1947 9/9/1902
1 4/4/2014 3/3/2013
1 1/4/2020 1/1/2001
1 3/8/2015 1/12/2019
1 19/11/1992 19/11/1992
I can give the Input like
Operators=[<,>,=,......]
Operands=[mode, StartDate,EndDate,.....]
I am expecting the result like
0 and 1/1/2018 < 1/12/2018=
0 and 1/1/2020 1/1/2020>
1 and 1/1/2018 5/5/2015<
1 and 3/8/20151/12/2019=
1 and 19/11/1992 19/11/1992
these are my unique patterns in my data. So is there any query or java program to get these unique patterns of my data so that I can test my application with all possible scenarios.
Note: I am having a total of 6 six columns in my table. for sample purposes, I only gave 3 columns.
答案1
得分: 0
以下是翻译好的内容：
你可以通过使用窗口函数将数据归类为不同的类别，并从每个集合中选择一个来实现这一点。尝试以下操作：
;with cte as
(
select Mode, StartDate, EndDate, ROW_NUMBER() over (partition by Mode,
case when CONVERT(date, StartDate, 103) < CONVERT(date, EndDate, 103) then 0
when CONVERT(date, StartDate, 103) = CONVERT(date, EndDate, 103) then 1
else 2 end order by Mode) rn
from @tab
)
select Mode, StartDate, EndDate from cte
where rn = 1
order by Mode, CONVERT(date, StartDate, 103)
请参阅 此处 的 db<>fiddle 示例。
英文:
You can do that by generalizing the data into categories using window function and pick one from each set.
try the following:
;with cte as
(
select Mode, StartDate, EndDate, ROW_NUMBER() over (partition by Mode, case when CONVERT(date, StartDate, 103) < CONVERT(date, EndDate, 103) then 0 when CONVERT(date, StartDate, 103) = CONVERT(date, EndDate, 103) then 1 else 2 end order by Mode) rn
from @tab
)
select Mode, StartDate, EndDate from cte
where rn = 1
order by Mode, CONVERT(date, StartDate, 103)
Please see db<>fiddle here.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论