如何在Grouped数据中获取缺失的数字 [英] How to get missing number in Grouped data

问题描述

嗨我还有另外一个要求



i有数据如

Hi I have another requirement like this

i have the data like

301 1 june
301 2 june
301 5 june
301 7 june
301 10 june

301 2 july
301 3 july
301 5 july
301 8 july
301 9 july

303 5 june
303 6 june
303 8 june
303 9 june

303 2 july
303 4 july
303 8 july
303 10 july

在这个数据中我想找出每组中缺少的数字。



例如在301和6月组中，缺少的数字是3,4,6,8 ，9（假设范围是1到10）



在301和7月组缺失的数字是1,4,6,7,10





在所有团体中都是这样的





so现在我想在sql server中找到缺少的数字。

可以帮助我。





谢谢



Prakash.ch

In this data i want find out missing number in each group.

For example in 301 and june group the missing numbers are 3,4,6,8,9(Assume that the range is 1 to 10)

in 301 and july group the missing numbers are 1,4,6,7,10


like that in all groups


so now i want find missing numbers in sql server.
could any one help me .


Thanks

Prakash.ch

推荐答案

你需要像以前一样做：

You need to do it in the same way as previous:

DECLARE @tdata TABLE (grp INT, val INT, mth NVARCHAR(30))
DECLARE @dtmp TABLE (grp INT, val INT, mth NVARCHAR(30))

INSERT INTO @tdata (grp, val, mth)
VALUES(301, 1, 'june')
INSERT INTO @tdata (grp, val, mth)
VALUES(301, 2, 'june')
INSERT INTO @tdata (grp, val, mth)
VALUES(301, 5, 'june')
INSERT INTO @tdata (grp, val, mth)
VALUES(301, 7, 'june')
INSERT INTO @tdata (grp, val, mth)
VALUES(301, 10, 'june')
INSERT INTO @tdata (grp, val, mth)
VALUES(301, 2, 'july')
INSERT INTO @tdata (grp, val, mth)
VALUES(301, 3, 'july')
INSERT INTO @tdata (grp, val, mth)
VALUES(301, 5, 'july')
INSERT INTO @tdata (grp, val, mth)
VALUES(301, 8, 'july')
INSERT INTO @tdata (grp, val, mth)
VALUES(301, 9, 'july')
--INSERT INTO @tdata (grp, val, mth)
--VALUES(302, 2, 'june')
--INSERT INTO @tdata (grp, val, mth)
--VALUES(302, 2, 'july')
INSERT INTO @tdata (grp, val, mth)
VALUES(303, 5, 'june')
INSERT INTO @tdata (grp, val, mth)
VALUES(303, 6, 'june')
INSERT INTO @tdata (grp, val, mth)
VALUES(303, 8, 'june')
INSERT INTO @tdata (grp, val, mth)
VALUES(303, 9, 'june')
INSERT INTO @tdata (grp, val, mth)
VALUES(303, 2, 'july')
INSERT INTO @tdata (grp, val, mth)
VALUES(303, 4, 'july')
INSERT INTO @tdata (grp, val, mth)
VALUES(303, 8, 'july')
INSERT INTO @tdata (grp, val, mth)
VALUES(303, 10, 'july')


;WITH FullData AS
(
	SELECT grp, val, mth, 1 AS nval
	FROM @tdata
	WHERE val<=10 
	UNION ALL
	SELECT grp, val, mth, nval + 1 AS nval
	FROM FullData
	WHERE nval < 10
) 
INSERT INTO @dtmp (grp, val, mth)
SELECT DISTINCT t1.grp, t1.nval as val, t1.mth
FROM FullData AS t1 LEFT JOIN @tdata AS t2 ON t1.grp = t2.grp 

SELECT DISTINCT grp, val, mth
FROM @dtmp AS MissingVal
WHERE MissingVal.val NOT IN (SELECT val FROM @tdata WHERE grp = MissingVal.grp AND mth = MissingVal.mth)
ORDER BY grp, mth, val

结果：

results:

grp     val     mth
301	1	july
301	4	july
301	6	july
301	7	july
301	10	july
301	3	june
301	4	june
301	6	june
301	8	june
301	9	june
303	1	july
303	3	july
303	5	july
303	6	july
303	7	july
303	9	july
303	1	june
303	2	june
303	3	june
303	4	june
303	7	june
303	10	june

这篇关于如何在Grouped数据中获取缺失的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！