SQL查询以获取另一个表中具有多行的行数 [英] SQL query to get count of rows with more than one row in another table

问题描述

在我的应用程序中，我有一组过滤器，可以在列出资源时应用，这些资源在执行查询之前通过添加WHERE子句等构建查询。这是使用SQL Server 2008.

我有两个相关的表，一个包含一些有关资源的静态数据，另一个可以包含任意/可选字段资源。

第一个表格是这样的（表格名称和栏位已更改）：

  CREATE TABLE资源（
 ResID varbinary（28），
 
 ...省略了额外的东西
 
类型的整数）; 
  

第二个表只有名称/值对和相应的资源ID

  CREATE TABLE ResourceFields（
 ResID varbinary（28）NOT NULL，
 
名称nvarchar（255）NOT NULL，
值nvarchar（1024）NOT NULL）; 
  

因此，对于这个例子，'ResourceFields'中可能有多行，其中name ='ContactName'对于相同的ResID。

我想要做的是获取'Resources'表中具有多个'ContactName ''在'ResourceFields'中列出'type'等于某个值。

我想出了这个（不要笑 - 我只知道SQL导致问题）
$ b $ pre $ SELECT count（r.ResID）
FROM资源为r
INNER JOIN ResourceFields AS rf
ON rf.ResID = r.ResID
AND rf.name ='ContactName'
WHERE r.type = 1
GROUP BY rf.ResID
HAVING COUNT（rf.Value）> 1;


，但不是返回'Resources'中的行数（在我的测试集中为43） '，我得到所有返回的COUNT（rf.Value）值（即43个人计数）。

我做错了什么？

解决方案

只需使用原始查询作为派生表（将其放入子查询中）：

<$ （*）
FROM（
）SELECT count（*）AS C
FROM资源作为r
INNER JOIN ResourceFields AS rf
ON rf.ResID = r.ResID
AND rf.name ='ContactName'
WHERE r.type = 1
GROUP BY rf.ResID
HAVING COUNT（rf .Value）> 1;
）s

Within my application, I have a set of filters that can be applied when listing resources, which builds up a query by adding WHERE clauses, etc. before executing the query. This is using SQL Server 2008.

I have two pertinent tables, one which contains some static data about the resource, and another which can contain arbitrary/optional fields pertaining to that resource.

First table is something like this (table names & fields changed):

CREATE TABLE Resources (
    ResID       varbinary(28),

    ... extra stuff omitted

    type        integer );

The second table just has name/value pairs and the corresponding resource ID

CREATE TABLE ResourceFields (
    ResID       varbinary(28) NOT NULL,

    Name        nvarchar(255) NOT NULL,
    Value       nvarchar(1024) NOT NULL);

So, for this example, there could be multiple rows in 'ResourceFields' with name = 'ContactName' for the same ResID.

What I want to do is get a count of the number of rows in the 'Resources' table that have more than one 'ContactName' listed in 'ResourceFields' with 'type' equal to some value.

I came up with this (don't laugh -- I know just enough SQL to cause problems)

SELECT count(r.ResID) 
    FROM Resources as r 
        INNER JOIN ResourceFields AS rf 
            ON rf.ResID = r.ResID 
                AND rf.name = 'ContactName' 
    WHERE r.type = 1 
    GROUP BY rf.ResID 
    HAVING COUNT(rf.Value) > 1;

but instead of returning the count of the number of rows (43 in my test set) in 'Resources', I get all the COUNT(rf.Value) values returned (that is, 43 individual counts).

What am I doing wrong?

解决方案

Just use your original query as a derived table (put it in a subselect):

SELECT COUNT(*)
FROM (
    SELECT count(*) AS C
    FROM Resources as r 
        INNER JOIN ResourceFields AS rf 
            ON rf.ResID = r.ResID 
                AND rf.name = 'ContactName' 
    WHERE r.type = 1 
    GROUP BY rf.ResID 
    HAVING COUNT(rf.Value) > 1;
) s

这篇关于SQL查询以获取另一个表中具有多行的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！