在列表中查找差距数字
本文关键字:差距 数字 查找 列表 | 更新日期: 2023-09-27 18:35:20
我正在用 c# 编写应用程序,Visual Studio 2010
我有如下
数据
Id tagNo TermNo
1 1000 2
2 1000 3
3 1000 7
4 1002 1
5 1002 10
如何通过 LINQ 或 TSQL
获得以下结果
tagNo TermNo
1000 1,4,5,6
1002 2,3,4,5,6,7,8,9
谢谢
我会做如下事情:
var openTags =
from item in source // Take the source items
group item by item.tagNo into g // and group them by their tagNo.
let inUse = g.Select(_ => _.TermNo) // Find all of the in-use tags
let max = inUse.Max() // and max of that collection.
let range = Enumerable.Range(1, max - 1) // Construct the range [1, max)
select new
{ // Select the following
TagNo = g.Key // for each group
TermNo = range.Except(inUse) // find the available tags
};
我使用 linq 解决了它,
var tagsList = sourceLists.Select(t => t.TagNo).Distinct().ToList();
foreach (var tagList in tagsList)
{
var terminalList = sourceLists.Where(t => t.TagNo == tagList).Select(t => int.Parse(t.TermNo)).ToList();
var result = Enumerable.Range(1, terminalList.Max()).Except(terminalList).ToList();
}
但是谁能告诉我在 TSQL 中是否有可能谢谢
使用 oracle 执行此操作的一个提示如下- (您也可以在 TSQL 中实现它)
SELECT COLUMN_VALUE
FROM TABLE(SYS.DBMS_DEBUG_VC2COLL(1,2,3,4,5,6,7,8,9,10))
MINUS
SELECT '4'
FROM DUAL;
这将从提供的列表中过滤掉"4"。(1 至 10)
同样,您可以过滤掉您的项目 (2,3,7,1,10) 前提是,您需要编写查询
假设sourceLists
实际上是一个 EF 实体,以下内容应在数据库上执行
var terminalsByTag = sourceLists.GroupBy(x => x.TagNo)
.Select(x => new {
TagNo = x.Key,
Terminals = x.Select(t => Int32.Parse(t.TermNo))
});
var result = Enumerable.Range(1, terminalsByTag.Max(g => g.Terminals.Max()).Except(g => g.Terminals).ToList();
这是您要求的 TSQL 解决方案(已更正):
declare @t table(Id int, tagNo int, TermNo int)
insert @t values
(1,1000,2), (2,1000,3), (3,1000,7), (4,1002,1), (5,1002,10)
;with a as
(
select max(TermNo)-1 MaxTermNo, TagNo
from @t
group by TagNo
),
b as
(
select 1 TermNo, TagNo, MaxTermNo
from a
union all
select TermNo+1, TagNo, MaxTermNo
from b
where TermNo < MaxTermNo
),
c as
(
select TermNo, TagNo
from b
except
select TermNo, TagNo
from @t
)
select t.TagNo
,STUFF((
select ',' + cast(TermNo as varchar(9))
from c t1
where t1.TagNo = t.TagNo
order by TermNo
for xml path(''), type
).value('.', 'varchar(max)'), 1, 1, '') TermNo
from c t
group by t.TagNo
option (maxrecursion 0)
结果:
TagNo TermNo
1000 1,4,5,6
1002 2,3,4,5,6,7,8,9