需要访问查询 [英] access query needed

问题描述

我正在寻找访问查询，但是sql server 2008可能就足够了，因为我可以在访问中使用直通功能.

I am looking for an access query, but a sql server 2008 could be sufficient as I can use a passthrough feature in access.

我的数据看起来像这样.

My data looks like this .

--------------------------------------------------------------
id   nameid   name        score         diff          include 
--------------------------------------------------------------
1     0001     SO          100           0                0
2     0001     SO          100           0                0
3     0001     SO          100           0                0
4     0001     SO          100           0                0
5     0001     SO          100           0                0
6     0001     SO          100           0                0

7     0002     MO          10            0                0
8     0002     MO          18            0                1
9     0002     MO          20            0                0
10    0002     MO          14            0                0
11    0002     MO          100           0                0
11    0002     MO          100           0                0

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

现在我想要的是遍历每一行，仅选择include = 1所在的行. 这很容易，但是，我不需要整个行.我想选择组".可以通过nameid(或name)标识该组.

Now what i want is to go through each row and only select the rows where include = 1. THIS IS EASY however ,I don't want the entire row.. I want to select the "group". The group can be identified by the nameid (or name).

因此，对于上述情况，我希望得到以下结果:

So for the above I want the following result:

--------------------------------------------------------------
id   nameid   name        score         diff          include 
--------------------------------------------------------------

7     0002     MO          10            0                0
8     0002     MO          18            0                1
9     0002     MO          20            0                0
10    0002     MO          14            0                0
11    0002     MO          100           0                0
11    0002     MO          100           0                0

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

推荐答案

向表查询包含= 1的行.

Ask your table for row with include = 1.

然后再次与表联接，以使所有行均对应于第一个查询的nameid:

Then join again with the table to have all the rows corresponding to the first query's nameid :

SELECT DISTINCT m.*
FROM myTable m
    INNER JOIN myTable m2
        ON m.nameid = m2.nameid
        AND m2.include = 1

对于大量数据，联接查询将比输入"查询更好地工作.您仍然需要在'nameid'字段上建立索引，并且在'include'上也不会造成伤害.

A join query will work better than an 'in' query for big amount of datas. You still need an index on the field 'nameid', and on 'include' could not hurt too.

等同于'WHERE EXISTS':

An equivalent is with 'WHERE EXISTS' :

SELECT m.*
FROM myTable m
WHERE EXISTS
   (
      SELECT * 
      FROM myTable m2
      WHERE m2.include = 1
      AND m2.nameid = m.nameid
   )

您可以在这里看到区别:

You could see the difference here :

> INNER JOIN是否可以提供比EXISTS更好的性能

以及当您具有许多ID的过滤器时，为什么必须使用Where的原因:

And why you have to use a Where exists when you have a filter with a lot of IDs :

SQL中的EXISTS和IN之间的区别?

这篇关于需要访问查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！