基于两列的所有组合检索数据的有效方法是什么？

I am dealing with a seemingly complex problem here. Unfortunately I am not an expert in SQL so I am not able to determine an efficient, general solution to this issue.

我在这里处理一个看似复杂的问题。不幸的是,我不是SQL的专家,所以我无法确定这个问题的有效,通用的解决方案。

To add some context, I am working with vehicular crash data in a MySQL table. The crash table has these attributes: id, date-time, the state that it occurred in and the weather and location of the car at the time of the accident. The weather and location are numeric values and the corresponding description is given in separate tables.

为了添加一些上下文,我正在使用MySQL表中的车辆崩溃数据。崩溃表具有以下属性:id,日期时间,发生的状态以及事故发生时汽车的天气和位置。天气和位置是数值,相应的描述在单独的表中给出。

Due to certain formatting issues, I have just taken screenshots of the sample tables that I am using.

由于某些格式问题,我刚刚拍摄了我正在使用的示例表的屏幕截图。

Crash Data Table

崩溃数据表

Weather Descriptions Table

天气描述表

Crash Location Descriptions Table

碰撞位置描述表

I would like to get the number of crash cases for each combination of weather and location of the car. For example, if there are 8 weather conditions and 8 crash locations then I would like to get 64 results with the weather & location combination and the number of fatalities for that combination.

我想获得每种天气和汽车位置组合的碰撞案例数量。例如,如果有8个天气条件和8个崩溃位置,那么我希望得到64个结果,包括天气和位置组合以及该组合的死亡人数。

Combination Possibilities

Currently I have tried doing this sequentially but it is too slow. Here is the query I am currently working with:

目前我已尝试按顺序执行此操作,但速度太慢。这是我目前正在使用的查询:

SELECT locationDescriptionTable.type as Location, 
       weatherDescirptionTable.type as AtmCond, 
       count( c.casenum ) as Cases 

FROM state AS stateTable, 
     data_all AS crashDataTable, 
     nm_location AS locationDescriptionTable, 
     atm_cond AS weatherDescirptionTable 

WHERE crashDataTable.statenum = stateTable.id AND 
      crashDataTable.nmlocat = locationDescriptionTable.id AND 
      crashDataTable.atmcond = weatherDescirptionTable.id AND 
      locationDescriptionTable.id ="crashLocationName" AND 
      weatherDescirptionTable.id ="weatherConditionName"

I have thought about this quite a lot, using JOINS or VIEWS to separate this out into different queries. However I am having no luck. Any help is greatly appreciated!

我已经考虑了很多,使用JOINS或VIEWS将其分成不同的查询。但是我没有运气。任何帮助是极大的赞赏!

Also, I am working with user: srr on this so there might be replies from that account as well.

此外,我正在与用户:srr一起工作,因此也可能会有来自该帐户的回复。

1 个解决方案

#1

Try the below query. You may want to change the table and column name as required. The inner query is getting all possible combination from your location and weather table and the left join with Crash Data table will give you null value on the column from Crash Data table if the matching Weather_ID and Location_ID is not found. Then you can group it and get a sum of how many had null and how many didn't.

尝试以下查询。您可能希望根据需要更改表和列名称。内部查询从您的位置和天气表获得所有可能的组合,如果找不到匹配的Weather_ID和Location_ID,则使用Crash Data表的左连接将在Crash Data表的列中为您提供空值。然后你可以对它进行分组并得到一个总和,其中有多少有空,有多少没有。

create table Weather(Weather_ID int, Type varchar(50));
create table Location(Location_ID int, Type varchar(50));
create table CrashData(Case_ID int, Weather_ID int, location_ID int);

insert into Weather(Weather_ID, Type)
values(1, 'Clear'), (2, 'Cloudy');

insert into Location(Location_ID, Type)
values(1, 'Intersection'), (2, 'Parking Lot');

insert into CrashData(Case_ID, Weather_ID, Location_ID)
values(1, 1, 1), (2, 1, 2), (3, 2, 1), (4, 2, 1);

SELECT Weather, Location, Sum(CASE WHEN Case_ID IS NULL THEN 0 ELSE 1 END)  Number_Of_Cases
FROM (SELECT Weather.Weather_ID, Weather.Type Weather,
        Location.Location_ID, Location.Type Location
    FROM Weather, Location) Temp 
LEFT OUTER JOIN CrashData
ON Temp.Weather_ID=CrashData.Weather_ID
    AND Temp.Location_ID=CrashData.Location_ID
GROUP BY Weather, Location
ORDER BY Weather, Location

http://sqlfiddle.com/#!2/89693b/3/0