显示同一个表中缺少记录的结果。 [英] Display result from the same table which has missing records.
问题描述
我想显示表中缺少的记录,而不使用其他表。喜欢使用自我加入。
Ex:
表A
1
2
3
5
6
8
10
以上表格显示缺少数字。
我想显示那些丢失的数字。这也没有使用任何其他表。
因此结果应显示为:
表A
4
7
9
提前致谢:)
I want to display the records which are missing in the table, with out using other table. Like using the self join.
Ex:
Table A
1
2
3
5
6
8
10
above table shows that there are missing numbers.
I want to display those missing numbers. That too with out using any other table.
Therefore the result shall display as:
Table A
4
7
9
Thanks in advance :)
推荐答案
试试这个:
Try this:
;WITH CTE AS
(
SELECT 1 AS FirstNo, MAX(MyNumber) AS LastNo
FROM A
UNION ALL
SELECT FirstNo +1 As FirstNo, LastNo
FROM CTE
WHERE FirstNo<LastNo
)
SELECT FirstNo AS MyNumber
FROM CTE
EXCEPT
SELECT MyNumber
FROM A
OPTION (MAXRECURSION 0)
SqlFiddle
有关详细信息,请参阅:
WITH common_table_expression(Transact-SQL ) [ ^ ]
EXCEPT和INTERSECT(Transact-SQL) [ ^ ]
Linq解决方案:
SqlFiddle
For further information, please see:
WITH common_table_expression (Transact-SQL)[^]
EXCEPT and INTERSECT (Transact-SQL)[^]
Linq solution:
DataTable dt = new DataTable();
DataColumn dc = new DataColumn("ID", Type.GetType("System.Int32"));
dt.Columns.Add(dc);
dt.Rows.Add(1);
dt.Rows.Add(2);
dt.Rows.Add(3);
dt.Rows.Add(5);
dt.Rows.Add(6);
dt.Rows.Add(8);
dt.Rows.Add(10);
int maxid = dt.AsEnumerable().Max(x=>x.Field<int>("ID"));
var qry = Enumerable.Range(1, maxid).Except(dt.AsEnumerable().Select(x=>x.Field<int>("ID")));
foreach(var n in qry)
{
Console.WriteLine("{0}", n);
}
这篇关于显示同一个表中缺少记录的结果。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!