在Mysql中为Report报告三个表

Question

I would like to produce report for airlines using

1. Date Table
2. Airline Table
3. Route Table

My date table include series of date

    Entryid | Date 
    ----------------
       1    |2016-06-01
       2    |2016-06-02
       4    |2016-06-03
       5    |2016-06-04
       6    |2016-06-05
       7    |2016-06-06

My airline table include

id|name
---------
1 |Air1
2 |Air2
3 |Air3

and Route table:

id|date     |airline|routename
1|2016-06-01|1      |city1-city2
2|2016-06-01|1      |city1-city3
3|2016-06-01|2      |city1-city3
4|2016-06-02|2      |city1-city3
5|2016-06-02|2      |city1-city3
6|2016-06-04|2      |city1-city3
7|2016-06-04|2      |city1-city3
8|2016-06-04|1      |city1-city3

by using these three table I want to produce result as follow"

    Date   |Airline|totleroute
2016-06-01 |  Air1 | 2
2016-06-01 |  Air2 | 1
2016-06-02 |  Air1 | 0
2016-06-02 |  Air2 | 2
2016-06-03 |  Air1 | 0
2016-06-03 |  Air2 | 0
2016-06-04 |  Air1 | 1
2016-06-04 |  Air2 | 2

How to produce desired result from joining three tables?

Answer 1

You can use a query like the following:

SELECT d.`date`, t.`name`, COUNT(r.`routename`) AS TotalRoutes
FROM `Date` AS d
CROSS JOIN (
  SELECT DISTINCT r.`airline`, a.`name`
  FROM Route AS r 
  INNER JOIN Airline AS a ON r.airline = a.id
) AS t   
LEFT JOIN Route AS r ON d.`date` = r.`date` AND t.`airline` = r.`airline`
GROUP BY d.`date`, t.`airline`

The CROSS JOIN is used in order to produce a result for each date - airline combination, as suggested by the sample output in the OP.

Demo here