Visual Basic，对Acces Databse中最常见的情况进行过滤 [英] Visual Basic, filter on most occuring in Acces Databse

问题描述

我刚开始使用Visual Basic 2015进行编程.而我正面临着这个问题:

I just started with programming in Visual Basic 2015. And I'm facing this problem:

我已经使用Microsoft Acces 2010建立了一个数据库，看起来像这样的表称为符号".

I've made a database with Microsoft Acces 2010, the table I've made is called "notations" it looks like this.

您可以看到我的数据库中已经有10条记录，其中有4列. 我想做的是从类别"的第三栏中过滤掉最常出现的内容.结果，我想创建一个文档.

As you can see I have already 10 records in my database with 4 columns. What I would like to do is filter the most occurring from the third column which is "categorie". With the result, I would like to create a document.

我真的不知道如何开始使用它，所以我真的可以帮助您. 如果您需要任何其他信息，请告诉我

I really have no clue on how to get started with this so I really could use you help with this. If you need any additional information please let me know

推荐答案

您需要将问题分为两个步骤:

You need to split your problem in two steps:

1. 获取表中重复次数最多的值
2. 获取与该值相关的数据.

我将提供解决方案的SQL部分:

I'll provide the SQL part of the solution:

对于步骤1:

select top 1 categorie, count(id) as n
from notations
group by categorie
order by n desc

此查询将对表中的行进行计数，按类别对行进行分组，然后按从大到小的顺序对其进行排序.

This query will count the rows in your table, grouping them by categorie, and sorting them from greatest to smallest.

对于第2步:您需要仅使用在第一个查询中检索到的值从表中获取数据:

For step 2: You need to get the data from your table, using only the values you've retreived in your first query:

select notations.*
from notations
     inner join (select top 1 categorie, count(id) as n 
                 from notations 
                 group by categorie 
                 order by n desc) as a on notations.categorie = a.categorie

您可以使用最后一个查询来获取所需的内容.

You can use this last query to get what you need.

我建议您寻找有关SQL和Access的好书或教程.

I recommend you look for a good book or tutorial on SQL and Access.

在阅读您的评论后，我想解释一下发生了什么.

After reading your comment, I'd like to explain a little bit about what's going on.

注意:我要解释的假设是您至少具有SQL知识(不是任何特定产品，而是语言).另外，请考虑到每个RDBMS(包括Microsoft Access)都使用特定的SQL方言，因此在Access中起作用的内容可能在其他RDBMS(SQL-Server，MySQL，PostreSQL，Oracle等)中不起作用.

Note: What I'm about to explain assumes you have at least some knowledge of SQL (not any particular product, but the language). Also, consider that every RDBMS (Microsoft Access included) uses a particular dialect of SQL, so what works in Access may not work in other RDBMS (SQL-Server, MySQL, PostreSQL, Oracle, etcetera).

让我们在第1步"中剖析查询:

Let's dissect the query in "Step 1":

select               -- This is a SELECT query, so it will return rows.
    top 1            -- Return ONLY the firs row.
    categorie,       -- Show column "categorie".
    count(id) as n   -- This is an aggregate function: It will count
                     -- the values of column "Id", and will group them
                     -- by the columns defined in the GROUP BY clause;
                     -- we define an alias for this column using the keyword "AS".
from notations       -- The source of the data this query will return rows from.
group by categorie   -- All aggregate functions will be affected by the
                     -- GROUP BY clause; in this case, "count(id)" will be
                     -- grouped by "categorie".
order by n desc      -- Sort the rows from the greatest to the smallest value
                     -- of column "n" (which is the alias for "count(id)".

用外行"一词，您要告诉Access要执行的操作:在对所有类别"值从最高记录数到最低记录进行排序之后，获取我的第一个类别"值 存储在表格符号"中.

In "layman" words, what you're telling Access to do is: Fetch me the first "categorie" value after ordering all "categorie" values from the highest to the lowest number of records stored in the table "notations".

请注意，此查询不会修改基础数据中的任何内容，它只会选择与手头任务相关的内容.

Notice that this query does not modify anything in the underlying data, it only selects what's relevant for the task at hand.

现在，关于步骤2":查询可以将许多东西作为数据源，包括其他查询.因此，既然您拥有最多重复的类别"，则可以从符号"表中获取所有数据.而且，为此，我们使用第二个查询.我们也对其进行剖析:

Now, about "Step 2": Queries can take many things as data sources, including other queries. So, now that you have the most repeated "categorie", you can get all the data from the "notations" table. And, for that, we use the second query. Let's dissect it too:

select             -- Again, this is a SELECT query, so it will show rows.
    notations.*    -- Show all columns of the "notations" table.
from notations     -- You need to tell the query that you're pulling data
                   -- from the "notations" table.
     inner join    -- You will join the rows pulled from the "notations" table
                   -- with the rows of another data source, which, in this case,
                   -- is another SELECT query (it's important to remember
                   -- that if you use a SELECT statement as data source,
                   -- you need to provide an alias for it (again, use the
                   -- keyword "AS").
         (select top 1 categorie, count(id) as n
          from notations group by categorie
          order by n desc) as a
                  -- Join the data sets so the query only returns rows
                  -- where the values of "categorie" from each one is identical.
         on notations.categorie = a.categorie

用外行"术语来说，Access的意思是:获取表"notations"中所有与"categorie"的值匹配所提供的子查询返回的值的值(在步骤"中进行了解释) 1).

In "layman" terms, what you're telling Access is: Fetch me all the values in table "notations" for which the value of "categorie" matches the value returned by the subquery provided (which was explained in "Step 1").

同样，您不会对数据进行修改:您只是在进行过滤，这对于您需要执行的任何任务都是有用的.

Again, you're not performing modifications to your data: you're just filtering in a way that is useful for whatever task you need to do.

您可以将此查询用作另一个"SELECT"查询，表单或报表的行源；您可以在VBA代码中使用它来执行一些特定的任务.不管发生什么，这都是您要做的事情.

You can use this query as a row source for another "SELECT" query, or for a Form, or for a Report; you can use it in your VBA code to perform some specific tasks. Whatever follows, it is for you to do.

同样，我建议您获得一本有关Access或SQL的好书.我本人是几年前从乔恩·维斯卡斯(Jon Viescas)的一本名为运行Microsoft Access 97"的书中学到的(免责声明:这不是商业广告，只是一个建议).是的，这是一本旧书，但是我可以从经验告诉您，从那时起Access并没有发生太大变化(是的，文件可以变大，形式不同，并且具有新功能...但是所有这些都是基础知识相同的).重要的是:找到良好的学习资源，做一些练习，并发挥创造力.

Again, I suggest you get a good book on Access or SQL. I, for myself, learned some years ago from a book called "Running Microsoft Access 97" by Jon Viescas (DISCLAIMER: This is not a commercial, it's just a suggestion). Yes, it is an old book, but I can tell you from experience that Access hasn't changed much since then (yes, files can get bigger, forms are different, and there are new features... but the basics are all the same). What's important is: Find a good learning resource, do some excercises, and be creative.

希望这会有所帮助.

这篇关于Visual Basic，对Acces Databse中最常见的情况进行过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！