使用 mySQL 返回多个表的排名

这是我的表结构：

___Lang:

|--------|------------|
| LAN_Id | LAN_En     |
|--------|------------|
| DI     | Direct     |
| WE     | Web        |
| OT     | Other      |
|--------|------------|

___细分：

|--------|------------|
| SEG_Id | SEG_Code   |
|--------|------------|
| 1      | DI         |
| 2      | WE         |
| 3      | OT         |
|--------|------------|

___预订：

|--------|------------------|
| BOO_Id | BOO_Segmentation |
|--------|------------------|
| 1      | 1                |
| 2      | 1                |
| 3      | 2                |
|--------|------------------|

___可计费数据：

|--------|---------------|------------|------------|
| BIL_Id | BIL_BookingId | BIL_Date   | BIL_Item   |
|--------|---------------|------------|------------|
| 1      | 1             | 2017-02-21 | Night      |
| 2      | 1             | 2017-02-22 | Night      |
| 3      | 1             | 2017-02-23 | Night      |
| 4      | 1             | 2017-02-24 | Night      |
| 5      | 2             | 2017-02-25 | Night      |
| 6      | 2             | 2017-02-26 | Night      |
| 7      | 3             | 2017-02-28 | Night      |
| 8      | 3             | 2017-03-01 | Night      |
| 9      | 3             | 2017-03-02 | Night      |
| 10     | 3             | 2017-03-03 | Night      |
|--------|---------------|------------|------------|

我想知道某个日期范围内最流行的细分。

所需的结果应该是以下日期范围内的结果：

表格 2017-02-01 至 2017-02-28（含）

|------------|------------|------------|--------------|------------|
| ROO_Name   | Night_Nb   | Percentage | Booking_Nb   | Percentage |
|------------|------------|------------|--------------|------------|
| Direct     | 6          | 85.71      | 2            | 66.66      |
| Website    | 1          | 14.28      | 1            | 33.33      |
| Other      | 0          | 0          | 0            | 0          |
|------------|------------|------------|--------------|------------|

我已经尝试过的：

SELECT r.SEG_Id
     , Sum(CASE WHEN BOO_Id IS NULL THEN 0 ELSE 1 END) Night_Nb
     , Concat(
         Format(
           Sum(CASE WHEN BOO_Id IS NULL THEN 0 ELSE 1 END) 
           / TotalBookings 
           * 100
         , 0) ) AS PercentageTotal
  FROM (  ___Segmentations r LEFT JOIN ___Bookings b ON r.SEG_Id = b.BOO_Segmentation
       ) INNER JOIN (SELECT BOO_HotelId
                          , Count(*) AS TotalBookings
                       FROM ___Bookings 
                      GROUP BY BOO_HotelId
                    ) AS TotalHotelBookings 
                 ON r.SEG_HotelId = TotalHotelBookings.BOO_HotelId
 WHERE r.SEG_HotelId = :hotel_id
 GROUP BY r.SEG_Id
 ORDER BY NumBookings DESC

但实际上行不通。

有人可以帮我解决这个问题吗？

您可以使用 SQL Fiddle：http://sqlfiddle.com/#!9/1aa10a http://sqlfiddle.com/#!9/1aa10a

我建议我们逐步增量地构建查询。验证每一步的查询结果是否符合我们的预期。当某些事情“不起作用”时，后退一步。

我们想要返回三行，每一行对应___Segmentations，对于特定的hotelid

 SELECT r.seg_id
      , r.seg_text
   FROM ___Segmentations r
  WHERE r.seg_hotelid = :hotel_id
  ORDER BY r.seg_id

将外连接添加到__Bookings

 SELECT r.seg_id
      , r.seg_text
      , b.boo_id
   FROM ___Segmentations r
   LEFT
   JOIN ___Bookings b
     ON b.boo_segmentation = r.seg_id
  WHERE r.seg_hotelid = :hotel_id
  ORDER
     BY r.seg_id
      , b.boo_id

将外连接添加到___BillableDatas

 SELECT r.seg_id
      , r.seg_text
      , b.boo_id
      , d.bil_id
   FROM ___Segmentations r
   LEFT
   JOIN ___Bookings b
     ON b.boo_segmentation = r.seg_id
   LEFT
   JOIN `___BillableDatas` d
     ON d.bil_bookingid = b.boo_id
  WHERE r.seg_hotelid = :hotel_id
  ORDER
     BY r.seg_id
      , b.boo_id
      , d.bil_id

如果这是我们感兴趣的行，我们可以进行聚合。

 SELECT r.seg_id
      , r.seg_text
      , COUNT(DISTINCT b.boo_id) AS cnt_bookings
      , COUNT(DISTINCT d.bil_id) AS cnt_billable
   FROM ___Segmentations r
   LEFT
   JOIN ___Bookings b
     ON b.boo_segmentation = r.seg_id
   LEFT
   JOIN `___BillableDatas` d
     ON d.bil_bookingid = b.boo_id
  WHERE r.seg_hotelid = :hotel_id
  GROUP
     BY r.seg_id
      , r.seg_text
  ORDER
     BY r.seg_text

现在获取“总计”的聚合。

我将采取的方法是使用 CROSS JOIN 操作来制作行的“副本”。我们可以连接我们编写的第一个查询返回的行，作为内联视图引用。 （别名为q以下。）

如果我们有一组完整的行，对每个行重复seg_id/seg_text（我们编写的第一个查询），我们可以使用条件聚合。

我们编写的最后一个查询（就在上面）是下面查询中的内联视图，别名为c.

SUM of cnt_bookings所有行就是总数。

对于单个计数，我们可以仅包含具有匹配的行seg_id，该子集的总数。

 SELECT q.seg_id
      , q.seg_text
      , SUM(IF(c.seg_id=q.seg_id,c.cnt_bookings,0))  AS cnt_bookings
      , SUM(c.cnt_bookings)                          AS tot_bookings
      , SUM(IF(c.seg_id=q.seg_id,c.cnt_billable,0))  AS cnt_billable
      , SUM(c.cnt_billable)                          AS tot_billable
   FROM ( SELECT t.seg_id
               , t.seg_text
            FROM ___Segmentations t
           WHERE t.seg_hotelid = :hotel_id_1
           ORDER BY t.seg_id
        ) q
  CROSS
   JOIN ( SELECT r.seg_id
               , COUNT(DISTINCT b.boo_id) AS cnt_bookings
               , COUNT(DISTINCT d.bil_id) AS cnt_billable
            FROM ___Segmentations r
            LEFT
            JOIN ___Bookings b
              ON b.boo_segmentation = r.seg_id
            LEFT
            JOIN `___BillableDatas` d
              ON d.bil_bookingid = b.boo_id
           WHERE r.seg_hotelid = :hotel_id
           GROUP
              BY r.seg_id
        ) c
  GROUP
     BY q.seg_id
      , q.seg_text
  ORDER
     BY q.seg_text

In the SELECT列表，我们可以进行除法以获得百分比：cnt_bookings * 100.0 / tot_bookings

e.g.

 SELECT q.seg_id
      , q.seg_text

      , SUM(IF(c.seg_id=q.seg_id,c.cnt_bookings,0))  AS cnt_bookings
      , SUM(c.cnt_bookings)                          AS tot_bookings
      , SUM(IF(c.seg_id=q.seg_id,c.cnt_bookings,0))
        * 100.0 / SUM(c.cnt_bookings)                AS pct_bookings

      , SUM(IF(c.seg_id=q.seg_id,c.cnt_billable,0))  AS cnt_billable
      , SUM(c.cnt_billable)                          AS tot_billable
      , SUM(IF(c.seg_id=q.seg_id,c.cnt_billable,0))
        * 100.0 / SUM(c.cnt_billable)                AS pct_billable

修改 ORDER BY 子句以按所需顺序返回行

从SELECT列出返回的表达式tot_bookings and tot_billable.

EDIT

我想我错过了日期标准。我们可以将外连接变成内连接，并将CROSS JOIN替换为LEFT JOIN。我们有可能返回 NULL 值cnt_bookings and cnt_billable，我们可以将它们包装在 IFNULL() 或 COALESCE() 函数中，以将 NULL 替换为零。

 SELECT q.seg_id
      , q.seg_text

      , SUM(IF(c.seg_id=q.seg_id,c.cnt_bookings,0))  AS cnt_bookings
      , SUM(c.cnt_bookings)                          AS tot_bookings
      , SUM(IF(c.seg_id=q.seg_id,c.cnt_bookings,0))
        * 100.0 / SUM(c.cnt_bookings)                AS pct_bookings

      , SUM(IF(c.seg_id=q.seg_id,c.cnt_billable,0))  AS cnt_billable
      , SUM(c.cnt_billable)                          AS tot_billable
      , SUM(IF(c.seg_id=q.seg_id,c.cnt_billable,0))
        * 100.0 / SUM(c.cnt_billable)                AS pct_billable

   FROM ( SELECT t.seg_id
               , t.seg_text
            FROM ___Segmentations t
           WHERE t.seg_hotelid = :hotel_id_1
           ORDER BY t.seg_id
        ) q
   LEFT
   JOIN ( SELECT r.seg_id
               , COUNT(DISTINCT b.boo_id) AS cnt_bookings
               , COUNT(DISTINCT d.bil_id) AS cnt_billable
            FROM ___Segmentations r
            JOIN ___Bookings b
              ON b.boo_segmentation = r.seg_id
            JOIN `___BillableDatas` d
              ON d.bil_bookingid = b.boo_id
             AND d.bil_date BETWEEN '2017-02-21' AND '2017-02-28'
           WHERE r.seg_hotelid = :hotel_id
           GROUP
              BY r.seg_id
        ) c
     ON 1=1   
  GROUP
     BY q.seg_id
      , q.seg_text
  ORDER
     BY q.seg_text