代码之家 › 专栏 › 技术社区 › Brad

在sql中计数匹配项

sql

Brad · 技术社区 · 17 年前

通过使用“分组方式”并计算匹配次数,这很容易做到。

SELECT     Departments.DepartmentName, count(*) as [survey count]
FROM         Departments INNER JOIN
                  Surveys ON Departments.DepartmentID =  Surveys.DepartmentID
 GROUP BY Departments.DepartmentName

更新:

SELECT     Departments.DepartmentName AS Department, 
        COUNT( Surveys.DepartmentID) AS      [survey count]
        , Departments.DepartmentID
        FROM          Surveys FULL OUTER JOIN
                  Departments ON  Surveys.DepartmentID = Departments.DepartmentID
   GROUP BY Departments.DepartmentName, Departments.DepartmentID

5 回复 | 直到 17 年前

RBarryYoung 17 年前

您需要一个“左外连接”而不是“内连接”,这将在输出中产生行,其中与“调查”对应的所有列都为空,而没有与连接条件匹配的列。

您需要将查询更新为“count(Survey.DepartmentID)”或“sum(当Survey.Department ID不为空时为1,否则为0)”,以避免将此类行计为一次提交。

araqnid 17 年前

只需将您的INNER JOIN更改为LEFT JOIN,并将COUNT()更改为仅引用SURVEYS。到那时,这可能会奏效。

SELECT     Departments.DepartmentName,
           count(Surveys.DepartmentID) as [survey count]
FROM         Departments LEFT JOIN
              Surveys ON Departments.DepartmentID =  Surveys.DepartmentID
 GROUP BY Departments.DepartmentName

pingw33n 17 年前

替换 INNER JOIN 随着 LEFT JOIN

Jim G. 17 年前

SELECT Departments.DepartmentName, count(Surveys.DepartmentID) as [survey count]
FROM Departments
LEFT OUTER JOIN Surveys
ON Departments.DepartmentID =  Surveys.DepartmentID
GROUP BY Departments.DepartmentName

Philip Fourie 14 年前

SELECT     Departments.DepartmentName, count(Surveys.*) as [survey count]
FROM       Departments 
LEFT JOIN  Surveys ON Departments.DepartmentID =  Surveys.DepartmentID
GROUP BY   Departments.DepartmentName

左加入并计数( 调查 .*)

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

1 年前

Shukurullox Komiljonov · 从记录中获得相互和解。使用SQL

1 年前

John D · 需要为NULL或NOT NULL的WHERE子句

1 年前

ojek · 如何对SQL结果进行分组和编号?

1 年前

senek · 如何在PL/SQL中将选择结果(列)放入数组中

1 年前

Sax · 规范化Google表格(第一步)

1 年前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

1 年前

Jatin · 检索卷计数的动态sql抛出错误语法错误[关闭]

1 年前

Andrus · 如何在sql中查找第二个匹配项

1 年前

Nebula Tech · 在SQL Server中搜索字符串中单词的多次出现

1 年前