SQL语句删除重复项并获取计数 [英] SQL Statement to Remove Duplicates and get Counts

问题描述

我持续的SQL头痛...

My continuing SQL headache...

如果我运行以下解释的代码:

If I run this paraphrased code:

SELECT State, Name, ID
FROM ...
WHERE Lost=False

我将得到类似以下的内容:

I will get something like the following back:

State        Name        ID
NY            A          123
NY            A          123
NY            B          234
NY            C          345
MD            X          356
MD            Y          668

我真正需要的是每种状态下资源的总和.这里的问题是，如果我只是执行以下操作，就不会考虑到明显存在重复项(请参见上面的前两个记录).

What I really need is a sum of the number of resources in each state. The problem here is that If I just do something like the following, it won't take into account that there are clearly duplicates (see the first two records above).

 SELECT state, Count(state) AS statecount
    FROM ....
   GROUP BY state, Lost
   HAVING Lost=false

在仅提取不同记录的情况下，如何获取丢失=否的每个状态的记录数? 我以为也许将DISTINCT放在第一个查询中，然后在ColdFusion中运行查询查询，但是您不能在子查询中执行诸如聚合的操作.

How can I get a count of the # of records for each state where lost = false while only pulling distinct records? I thought maybe putting DISTINCT in the first query and then running a Query of a Query in ColdFusion but you can't do things like aggregates in subqueries.

基本上，以上面的示例为例，我最终想要这样做:

Basically, using the above as an example, I'd ultimately want this:

            State      Count
              NY          3 (not 4!)
              MD          2

我应该注意...这在Access中

I should note... this is in Access

推荐答案

显然，Access 2007支持嵌入式视图.所以你可以做到这一点. (我不知道较早的版本)

Apparently Access 2007 supports inline views. so you can do this. (I don't know about earlier versions)

SELECT 
   state,
   COUNT(CR_ID )

   FROM

   (


       SELECT  DISTINCT 
       lkuState.StateName AS state, 
       tblMain.CR_ID 
       FROM lkuState 
       INNER JOIN 
       (tblMain 
       INNER JOIN (locLink 
       INNER JOIN tblLoc 
       ON locLink.GEOMETRY_ID = tblLoc.GEOMETRY_ID) 
       ON tblMain.CR_ID = locLink.CR_ID) 
       ON lkuState.FIPS_State = tblLoc.FIPS_State 

       WHERE tblMain.Lost=False) t

GROUP BY
State

更新

就像下面的David-W-Fenton Notes所说的那样，我不确定哪些版本支持派生表/内联视图

As David-W-Fenton Notes below in regards to my uncertainty about which versions supported derived tables/inline views

Jet/ACE长期以来一直支持派生表，尽管其语法曾经与简单的(SELECT ...)As Alias完全不同-它是[SELECT ...].请注意，右方括号的尾部为句号，这排除了需要内部方括号的SQL SELECT

Derived tables have long been supported by Jet/ACE, though the syntax used to be quite different from the simple (SELECT...) As Alias -- it was [SELECT...]. Note the trailing period on the close square bracket, and that this precluded a SQL SELECT where internal square brackets were required

这篇关于SQL语句删除重复项并获取计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！