mySQL:在多张表上的多列联接第二部分 [英] mySQL: Multi-column join on several tables part II

问题描述

我正在向现有联接中添加第5个表.原始查询将始终返回一行，因为where子句指定了唯一的ID.这是我们正在使用的表:

I am adding a 5th table to an existing join. The original query will always return a single row because the where clause specifies a unique ID. Here are the tables we are using:

表1
龋，患病，makeid，modelid，caryear

Table 1
carid, catid, makeid, modelid, caryear

表2
makeid，makename

Table 2
makeid, makename

表3
型号，型号名称

Table 3
modelid, modelname

表4
catid，猫名

Table 4
catid, catname

表5 id，caryear，makename，modelname

Table 5 id, caryear, makename, modelname

这是我正在使用的现有查询:

Here is the existing query I am using:

SELECT a.*, e.citympg, e.hwympg
FROM table1 a
  JOIN table2 b on a.makeid=b.makeid
  JOIN table3 c on a.modelid=c.modelid
  JOIN table4 d on a.catid=d.catid
  JOIN table5 e on b.makename = e.make
                and c.modelname = e.model
                and a.caryear = e.year
  WHERE a.carid = $carid;

我需要解决2个问题-

1. 当表5上没有匹配项时，它不返回任何结果.看来我需要进行某种左联接或拆分查询并进行并集.

1. When there is no match on table 5, it does not return any results. It would seem that I need to do some sort of left join or split the query and do a union.

当表5上存在匹配项时，它将返回多行.由于未使用返回单行的条件，因此我将求出citympg和hwympg的平均值.

When there is a match on table 5, it returns multiple rows. Since the criteria that would return a single row is not being used, I would settle for an average of citympg and hwympg.

可以通过一个查询同时实现这两个目标吗?怎么样?

Can both objectives be achieved with a single query? How?

推荐答案

假设我正确理解了您的需求...该查询会将表5中的结果限制为每种联接条件组合的一行，并返回平均城市/高速公路mpg.

Assuming I understand what you want correctly... This query will constrain the results from table5 to one row per combination of the join criteria, returning average city/hwy mpg.

SELECT a.*, e.citympg, e.hwympg
FROM table1 a
JOIN table2 b on a.makeid=b.makeid
JOIN table3 c on a.modelid=c.modelid
JOIN table4 d on a.catid=d.catid
LEFT JOIN (SELECT year, make, model, 
                  AVG(citympg) as citympg, 
                  AVG(hwympg) as hwympg           
           FROM table5 
           GROUP BY year, make, model) e on b.makename = e.make
                                         and c.modelname = e.model
                                         and a.caryear = e.year
WHERE a.carid = $carid;

请注意，当table5中不存在任何记录时，它将返回NULL mpg值.

Note that it will return NULL mpg values when no record in table5 exists.

这篇关于mySQL:在多张表上的多列联接第二部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！