SQL SQL查询以获取另一个表中多于一行的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7029747/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query to get count of rows with more than one row in another table
提问by Shawn D.
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.
在我的应用程序中，我有一组可以在列出资源时应用的过滤器，这些过滤器在执行查询之前通过添加 WHERE 子句等来构建查询。这是使用 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
第二个表只有名称/值对和相应的资源 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.
因此，对于此示例，对于同一个 ResID，“ResourceFields”中可能有多行 name = 'ContactName'。
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.
我想要做的是计算“Resources”表中在“ResourceFields”中列出了多个“ContactName”且“type”等于某个值的行数。
I came up with this (don't laugh -- I know just enough SQL to cause problems)
我想出了这个（别笑——我只知道足够的 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;
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).
但是我没有返回“资源”中的行数（我的测试集中的 43 行），而是返回了所有的 COUNT(rf.Value) 值（即 43 个单独的计数）。
What am I doing wrong?
我究竟做错了什么？
回答by Andriy M
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
回答by Yahia
I am not really familiar with SQL Server but try:
我对 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;
回答by Clockwork-Muse
You need to pre-aggregate the count of the 'ContactName' attributes. This is perhaps most easily accomplished with a CTE:
您需要预先聚合“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
Some other things to consider -
Possibly make namein ResourceFieldsactually be a foreign key to a different table (so changing all attributes to a different text name is trivial). This would also allow you to put information about the expected format of data in valuein the referenced table, hopefully limiting invalid data (using regex masks, etc.) - you have to be extremely careful with multi-domain tables like this (generally, they shouldn't be used, but there are probably some use-cases).
Also, are you really anticipating storing 28 bytesworth of different resources? This is a rather large number... (remember that an Int is usually 4 bytes, and stores about 4 billion different values).
一些其他的事情要考虑-
可能使name中ResourceFields实际上是一个外键不同的表（因此更改所有属性不同的文本名称是微不足道的）。这也将允许您将有关预期数据格式的信息放入value引用表中，希望限制无效数据（使用正则表达式掩码等） - 您必须非常小心这样的多域表（通常，它们不应该使用，但可能有一些用例）。
另外，您真的希望存储 28字节的不同资源吗？这是一个相当大的数字......（记住一个 Int 通常是 4 个字节，并存储大约 40 亿个不同的值）。
回答by pkk
Hope this helps:
希望这可以帮助：
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
UPDATE: Grouping removed from the subquery, which added irrelevant rows to the count.
更新：从子查询中删除了分组，这将不相关的行添加到计数中。
