在另一张表中有超过一个匹配行的行数的SQL查询

Question

在我的应用程序中，我有一组过滤器，可以在列出资源时应用，通过添加WHERE子句等来构建查询，然后执行查询。此功能使用的是SQL Server 2008。

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

第一个表类似于以下内容（表名和字段已更改）：

CREATE TABLE Resources (
    ResID       varbinary(28),

    ... extra stuff omitted

    type        integer );

第二个表格只包含名称/值对和相应的资源ID。

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

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

因此，在此示例中，在“ResourceFields”中可能会有多个名称为“ContactName”的行，其ResID相同。

我想要做的是获取“Resources”表中具有在“ResourceFields”中列出了多个“ContactName”，并且“type”等于某个值的行数。

我想到了这个（别笑——我知道足够的SQL以造成问题）

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;

但是，我期望返回的是“Resources”表中行数的总和（在我的测试集中为43），但实际上返回了所有“rf.Value”字段的计数值（即，43个单独的计数值）。

我错在哪里了？

- Shawn D.

Answer 1

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

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

- Andriy M

---

他可能实际上想要内部“count”的SUM()，但除此之外我很喜欢这个。 - Chris Cunningham

是的，他肯定可能会。然而，到目前为止，他已经表示他预期获得值43，但实际上却得到了43行的值。 - Andriy M

不，我只想要数量。我会试一下这个。 - Shawn D.

纯属好奇，这个尾随的 's' 是什么意思？ - Shawn D.

它是子查询的名称（在这种情况下不需要，例如AS s）-如果您想将其与其他内容连接，可以使用s访问子查询列（在这种情况下只有C）... - Yahia

@Shawn D：SQL Server 要求子查询必须有别名，即使子查询是你从中提取列的唯一对象。同样，内部的 COUNT(*) 表达式必须有别名，即使你实际上并不关心这个列的值。并非所有子查询都必须始终有别名，但特别是派生表必须有别名。 - Andriy M

Answer 2

我不太熟悉SQL Server，但可以尝试：

SELECT count( r.ResID ) FROM Resources as r where 1 < (select count(rf.value) from ResourceFields AS rf where rf.ResID = r.ResID AND rf.name = 'ContactName') and r.type = 1;

- Yahia

Answer 3

您需要预先聚合“ContactName”属性的计数。这可能最容易通过使用CTE来实现：

WITH Multiple_Contacts (ResID) as (SELECT a.ResID
                                   FROM Resources as a
                                   JOIN ResourceFields as B
                                   ON b.ResID = A.ResID
                                   AND b.name = 'ContactName'
                                   WHERE a.type = 1
                                   GROUP BY a.ResID
                                   HAVING COUNT(a.ResId) > 1)
SELECT COUNT(ResId)
FROM Multiple_Contacts

一些需要考虑的其他事情 -
可能会使 ResourceFields 中的 name 实际上成为指向不同表格的外键（这样更改所有属性为不同文本名称就很容易）。这也允许您在引用的表格中放置有关数据预期格式的信息，希望限制无效数据（使用正则表达式掩码等）-对于多域表格，您必须非常小心（通常不应使用它们，但可能存在某些用例）。
此外，您真的预计要存储28个不同资源的字节吗？这是一个相当大的数字...（请记住，Int通常为4个字节，并存储约40亿个不同值）。

- Clockwork-Muse

Answer 4

希望这能有所帮助：

SELECT COUNT(*)
FROM Resources as r
WHERE EXISTS (
    SELECT 1
    FROM ResourceFields rf
    WHERE rf.ResId = r.ResId
    AND rf.name = 'ContactName'
    HAVING COUNT(*) > 1
)
AND r.type = 1

更新：子查询中的分组已被移除，这会导致计数中出现不相关的行。

- pkk

---

实际上，这个返回了79个计数，所以我认为它在子查询中重复包含了一些行。 - Shawn D.

是的，我过于依赖现有的查询了。现在它应该按预期工作了。 - pkk