查询帮助需要查找缺失的号码

发布于 2024-09-16 02:54:06 字数 314 浏览 3 评论 0原文

我有一个看起来像这样的表：

ID         | GROUP
-------------------
1000001    | 0
1000001    | 1
1000001    | 2
1000002    | 0
1000002    | 2
1000002    | 3
1000003    | 1
1000003    | 2
1000003    | 3
1000004    | 0

我需要列出序列中缺少组的所有 id。所以对于上面的例子我只需要返回 1000002 & 1000003.

提前致谢

原文

I have a table that looks something like this:

ID         | GROUP
-------------------
1000001    | 0
1000001    | 1
1000001    | 2
1000002    | 0
1000002    | 2
1000002    | 3
1000003    | 1
1000003    | 2
1000003    | 3
1000004    | 0

I need to list all the ids where there is a group missing in the sequence.
So for the above example i would only need back 1000002 & 1000003.

Thanks in advance

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

停顿的约定 2024-09-23 02:54:16

也许是这样的：（

SELECT
  id
FROM mytable m
GROUP BY id
HAVING (MAX(group) + 1) > COUNT(DISTINCT group);

我用小写写了键）。如果您在 (ID, GROUP) 上有唯一键，则可以省略 DISTINCT。

Maybe this:

SELECT
  id
FROM mytable m
GROUP BY id
HAVING (MAX(group) + 1) > COUNT(DISTINCT group);

(I've written the keys in lowercase). If you have a unique key on (ID, GROUP), you can leave out the DISTINCT.

回复收藏 0 原文

梦醒时光 2024-09-23 02:54:14

我从您的评论中得到的是，ID 必须以 0 组开头，并且该组应该始终仅递增 1。由此，我得到以下信息：

SELECT id
FROM (
        SELECT  id, count(*) as cnt, max(group) as mx, min(group) as mn
        FROM    myTable
        GROUP BY group
     ) A
WHERE NOT mx = cnt - 1
AND NOT mn = 0

希望这会有所帮助。它可能不是最干净或最有效的，但希望它会有所帮助。

编辑：实际上，在重新查看我之前的答案并思考 HAVING 之后，它可能会像这样更干净。

SELECT ID
FROM   myTable
GROUP BY ID
HAVING MAX(group) >= COUNT(DISTINCT group)

What I'm getting from your comments is that the ID must start with a group of 0 and the group should always increment only by 1. From that, I get this:

SELECT id
FROM (
        SELECT  id, count(*) as cnt, max(group) as mx, min(group) as mn
        FROM    myTable
        GROUP BY group
     ) A
WHERE NOT mx = cnt - 1
AND NOT mn = 0

Hope this helps. It's probably not the cleanest or most effective, but hope it will help.

EDIT: Actually, after re-looking over the answer before mine and thinking about HAVING, it probably would be cleaner like this.

SELECT ID
FROM   myTable
GROUP BY ID
HAVING MAX(group) >= COUNT(DISTINCT group)

回复收藏 0 原文

各自安好 2024-09-23 02:54:12

由于我们不需要有关缺失组编号的信息，因此我们可以比较特定组的所有元素计数是否低于或等于

SELECT ID FROM YOUR_TABLE
GROUP BY ID 
HAVING COUNT(ID) <= max(GROUP);

As we don need the information about the missing group number we can compare that over all count of elements is lower or equal for specific group

SELECT ID FROM YOUR_TABLE
GROUP BY ID 
HAVING COUNT(ID) <= max(GROUP);

回复收藏 0 原文

深海夜未眠 2024-09-23 02:54:11

select distinct id
from
(
    SELECT Id, Group, LAG(Group, 1, -1) over (partition by Id order by Group) prevGroup
 FROM Table
 )     
 WHERe Group -1 <> PrevGroup

select distinct id
from
(
    SELECT Id, Group, LAG(Group, 1, -1) over (partition by Id order by Group) prevGroup
 FROM Table
 )     
 WHERe Group -1 <> PrevGroup

回复收藏 0 原文

~没有更多了~