INNER JOIN和COUNT在同一个查询中 [英] INNER JOIN and COUNT in the same query

问题描述

我在将INNER JOIN和COUNT放在同一个查询中时遇到问题。

I am having trouble with putting together INNER JOIN and COUNT in the same query.

表格是：

TABLE STREETS

ID | STREET_NAME
------------------------
1  | Elm street
2  | Some other street
3  | Unknown street
4  | Killer street
5  | Dead-end street




TABLE ACCIDENTS_STREETS

STREET_ID | ACCIDENT_ID
-----------------------
   2      | 4
   2      | 7
   2      | 2
   2      | 1
   5      | 3

我想获得发生大多数事故的街道名称。

I would like to get the street name where most accidents have occured.

这是COUNT的：

SELECT TOP 1 COUNT(STREET_ID) AS dangerous_street FROM ACCIDENTS_STREETS GROUP BY STREET_ID ORDER BY dangerous_street DESC

如何在INNER JOIN中添加INNER JOIN以获取街道名称？

How to add INNER JOIN there to get only the name of the street?

感谢任何建议！

推荐答案

/ p>

The Following should work

SELECT TOP 1 S.STREET_NAME,COUNT(a.*) AS dangerous_street 
FROM ACCIDENTS_STREETS A 
inner Join STREET S on S.ID = A.STREET_ID 
GROUP BY S.STREET_NAME ORDER BY dangerous_street DESC

这篇关于INNER JOIN和COUNT在同一个查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！