需要访问过滤器

发布于 2024-11-18 13:25:36 字数 3653 浏览 2 评论 0原文

这是一个示例数据集，

-----------------------------------------------------------------------------------------
id   nameid   name        score         diff          include     quantity     grade
---------------------------------------------------------------------------------------

7     0002     MO          10            0                0          25          3
8     0002     MO          18            0                1          25          3
9     0002     MO          20            0                0          25          3
10    0002     MO          14            0                0          17          6
11    0002     MO          100           0                0          17          6
11    0002     MO          100           0                0          17          6

12    0003     MA          10            0                0          12          3 
13    0003     MA          18            0                0          12          3
14    0003     MA          20            0                0          12          3
15    0003     MA          14            0                0          25          6
16    0003     MA          100           0                1          25          6
17    0003     MA          100           0                0          25          6

12    0004     MB          10            0                0          12          3
13    0004     MB          18            0                1          12          3
14    0004     MB          20            0                0          12          3 
15    0004     MB          14            0                0          07          6
16    0004     MB          100           0                1          07          6
17    0004     MB          100           0                0          07          6

我有一个返回上表的查询。请注意，在每六组中，include 列中至少有一行的值为 1。查看参考：需要访问查询但不需要。

此外，对于每组六人，有 3 行的 grade = 3 和 3 行的 grade = 6。相应地，grade 3 和 grade 6 在该组中的数量相同。

我想要做的是过滤掉所有数量少于 15 的行。但是，我仍然想将它们按 6 分组。

我想删除一个同时具有 quantity <; 的“组”。 3 年级和 6 年级均为 15。从上述数据集中我想要以下结果：

-----------------------------------------------------------------------------------------
id   nameid   name        score         diff          include     quantity     grade
---------------------------------------------------------------------------------------

7     0002     MO          10            0                0          25          3
8     0002     MO          18            0                1          25          3
9     0002     MO          20            0                0          25          3
10    0002     MO          14            0                0          17          6
11    0002     MO          100           0                0          17          6
11    0002     MO          100           0                0          17          6

12    0003     MA          10            0                0          12          3 
13    0003     MA          18            0                0          12          3
14    0003     MA          20            0                0          12          3
15    0003     MA          14            0                0          25          6
16    0003     MA          100           0                1          25          6
17    0003     MA          100           0                0          25          6

所以基本上，如果六人一组的任何行中都有 include = 1，并且 3 级或 6 级 quantity > > 15 那么我想要整个组。

原文

Here is a sample data set

-----------------------------------------------------------------------------------------
id   nameid   name        score         diff          include     quantity     grade
---------------------------------------------------------------------------------------

7     0002     MO          10            0                0          25          3
8     0002     MO          18            0                1          25          3
9     0002     MO          20            0                0          25          3
10    0002     MO          14            0                0          17          6
11    0002     MO          100           0                0          17          6
11    0002     MO          100           0                0          17          6

12    0003     MA          10            0                0          12          3 
13    0003     MA          18            0                0          12          3
14    0003     MA          20            0                0          12          3
15    0003     MA          14            0                0          25          6
16    0003     MA          100           0                1          25          6
17    0003     MA          100           0                0          25          6

12    0004     MB          10            0                0          12          3
13    0004     MB          18            0                1          12          3
14    0004     MB          20            0                0          12          3 
15    0004     MB          14            0                0          07          6
16    0004     MB          100           0                1          07          6
17    0004     MB          100           0                0          07          6

I have a query that returns the above table.
Note that in each group of six, there WILL be atleast one row that has value 1 in include column.
Look at ref: access query needed but not needed.

Also for each group of six, there are three rows that has grade = 3 and 3 rows that has grade = 6.
And corresspondingly, the grade 3 and grade 6 have the same quantity in that group.

What I want to do is filter out all the rows that have less then 15 quantity.
However, I still want to group them by 6.

I want to remove a "group" that has both quantity < 15 for both grade 3 and 6. From the above data set
I wwant the following result:

-----------------------------------------------------------------------------------------
id   nameid   name        score         diff          include     quantity     grade
---------------------------------------------------------------------------------------

7     0002     MO          10            0                0          25          3
8     0002     MO          18            0                1          25          3
9     0002     MO          20            0                0          25          3
10    0002     MO          14            0                0          17          6
11    0002     MO          100           0                0          17          6
11    0002     MO          100           0                0          17          6

12    0003     MA          10            0                0          12          3 
13    0003     MA          18            0                0          12          3
14    0003     MA          20            0                0          12          3
15    0003     MA          14            0                0          25          6
16    0003     MA          100           0                1          25          6
17    0003     MA          100           0                0          25          6

So basically if a group of six has include = 1 in any row, and either grade 3 or 6 quantity > 15 then I want the entire group.

分享到QQ

分享到微博

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

发布评论

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

婴鹅 2024-11-25 13:25:36

“所以基本上，如果六人一组的任何行中包含 = 1，并且 3 年级或 6 年级数量 > 15，那么我想要整个组。”

我的猜测是此查询将识别候选人nameid 组：

SELECT DISTINCT nameid
FROM YourTable
WHERE
    include = 1
    AND quantity > 15
    AND (grade = 3 OR grade = 6);

如果我猜对了，您可以将其保存为单独的查询，或将其用作子查询，并将其 INNER JOIN 到 YourTable 以将返回的行限制为仅那些 nameid 满足您的条件的行。它可能看起来很接近这个未经测试的 SELECT 语句：

SELECT y.id, y.nameid, y.[name], y.score, y.diff, y.include, y.quantity, y.grade
FROM
    YourTable AS y
    INNER JOIN [
        SELECT DISTINCT nameid
        FROM YourTable
        WHERE
            include = 1
            AND quantity > 15
            AND (grade = 3 OR grade = 6)
        ]. AS q
        ON y.nameid = q.nameid
ORDER BY y.nameid;

编辑：如果您还没有 nameid 索引，请添加索引。

"So basically if a group of six has include = 1 in any row, and either grade 3 or 6 quantity > 15 then I want the entire group."

My guess is this query will identify the candidate nameid groups:

SELECT DISTINCT nameid
FROM YourTable
WHERE
    include = 1
    AND quantity > 15
    AND (grade = 3 OR grade = 6);

If I guessed correctly, you can save it as a separate query, or use it as a subquery, and INNER JOIN it to YourTable to limit the rows returned to only those where nameid meets your criteria. It might look close to this untested SELECT statement:

SELECT y.id, y.nameid, y.[name], y.score, y.diff, y.include, y.quantity, y.grade
FROM
    YourTable AS y
    INNER JOIN [
        SELECT DISTINCT nameid
        FROM YourTable
        WHERE
            include = 1
            AND quantity > 15
            AND (grade = 3 OR grade = 6)
        ]. AS q
        ON y.nameid = q.nameid
ORDER BY y.nameid;

Edit: Add an index on nameid if you don't already have one.

回复收藏 0 原文

~没有更多了~