程序员经验分享-hwhale

使用 mySQL 返回多个表的排名

2024-02-07

这是我的表结构：

___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
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

使用 mySQL 返回多个表的排名 的相关文章

  • MySQL 按主键排序

    某些 SQL 服务器允许使用通用语句 例如ORDER BY PRIMARY KEY 我不相信这适用于 MySQL 是否有任何此类解决方法可以允许跨多个表自动选择 或者是否需要查找查询来确定主键 我一直在研究的解决方法包括调用SHOW COL

  • mysql语句中的*星号是什么意思？

    Ex mysql query SELECT FROM members WHERE id id 这意味着选择表中的所有列

  • 将 UPDATE 转换为 INSERT INTO ON DUPLICATE KEY UPDATE 语句

    我有这个 UPDATE MySQL 语句 效果很好 UPDATE table1 Inner Join table2 ON table2 id table1 gw id SET table1 field1 1 table1 field2 2

  • MySQL 服务器未启动

    当我做 mysql u root p并输入my password这就是我得到的 错误 2002 HY000 无法通过套接字 var run mysqld mysqld sock 连接到本地 MySQL 服务器 2 所以我输入 systemc

  • 单行的总和值？

    我有一个 MySQL 查询 它返回由一系列 1 和 0 组成的单行 它用于进度条指示器 我现在在代码中对它进行求和 但我尝试对查询中的值求和 并意识到我无法使用 SUM 因为它们有很多列 但只有一行 有没有办法可以在查询中自动求和 就像这样

  • 如何在 phpmyadmin 中创建 MySQL 触发器

    我想在 MySQL 中创建一个触发器 我运行以下命令 mysql gt delimiter mysql gt CREATE TRIGGER before insert money BEFORE INSERT ON money gt FOR

  • Mysql innoDB 不断崩溃[关闭]

    Closed 这个问题不符合堆栈溢出指南 help closed questions 目前不接受答案 我的数据库 mysql 服务器不断崩溃 重新启动 我不知道该怎么办 我不断在 dbname org err 文件中收到以下内容 13120

  • 查询中列的顺序重要吗？

    当从 MySQL 表中选择列时 与表中的顺序相比 选择列的顺序是否会影响性能 不考虑可能覆盖列的索引 例如 您有一个包含行 uid name bday 的表 并且有以下查询 SELECT uid name bday FROM table M

  • MYSQL - 查找最近的前一天

    我可以以某种方式 不使用 PHP 找到一周中最近的前一天日期吗 Like 最近的上一个星期二的日期是哪一天 CURDATE INTERVAL WEEKDAY CURDATE wday IF WEEKDAY CURDATE gt wday 0

  • 如何使用 MySQL 的 LOAD DATA LOCAL INFILE 在导入 CSV 时将字符串日期更改为 MySQL 日期格式

    我正在使用 MySQL 的 LOAD DATA LOCAL INFILE SQL 语句将数据从 CSV 文件加载到现有数据库表中 下面是一个 SQL 语句示例 LOAD DATA LOCAL INFILE file csv INTO TAB

  • 如何在 Laravel 查询中使用多个 OR,AND 条件

    我需要 Laravel 查询帮助 我的自定义查询 返回正确结果 Select FROM events WHERE status 0 AND type public or type private 如何写这个查询Laravel Event w

  • MYSQL 区分大小写的 utf8 搜索（使用 hibernate）

    我的登录表具有 utf8 字符集和 utf8 排序规则 当我想要检查用户名并检索该特定用户名的其他信息时 hql 查询会为我提供小写和大写相同的结果 我应该如何处理适用于案例的 HQL 查询 我使用 Mysql 5 和 java hiber

  • Mysql 中 UNION 子句的替代方案

    我有两张桌子 表 a 表 b table a ID 1 2 3 4 5 7 table b ID 2 3 4 5 6 我必须得到这样的输出而无需UNION命令 ID 1 2 3 4 5 6 7 注意 我有一个联合解决方案 select fr

  • MySQL 按重复项从上到下排序

    我有一个lammer问题 因为我不是mysql专业人士 我有类似的字段 id color 1 red 2 green 3 yellow 4 green 5 green 6 red 我想按重复项进行分组 最常见的重复项先进行分组 所以应该这样

  • MySQL连接字符集问题

    我在 Mac 上使用带有 MySQL 的 velosurf 没有任何编码问题 但是当我切换到 Linux 计算机时 从 velosurf 获得的值未正确编码 我发现这可能是默认连接字符集的问题 在 Mac 上我得到 mysql gt sho

  • Mysql 将 int 转换为 MAC

    我有一些数据可以转换 其中有 2 列 其中一列有 IP 它包含整数值 我在 mysql 查询中使用了以下函数 是否有一个函数可以用来转换我的 mac 列 其中包含整数和数据类型是bigint to MAC地址 SELECT INET NTO

  • MySQL 查询到 CSV [重复]

    这个问题在这里已经有答案了 有没有一种简单的方法来运行MySQL查询来自linux命令行并以csv格式输出结果 这就是我现在正在做的事情 mysql u uid ppwd D dbname lt lt EOQ sed e s g tee l

  • 如何在 Play java 中创建数据库线程池并使用该池进行数据库查询

    我目前正在使用 play java 并使用默认线程池进行数据库查询 但了解使用数据库线程池进行数据库查询可以使我的系统更加高效 目前我的代码是 import play libs Akka import scala concurrent Ex

  • INSERT..RETURNING 在 JOOQ 中不起作用

    我有一个 MariaDB 数据库 我正在尝试在表中插入一行users 它有一个生成的id我想在插入后得到它 我见过this http www jooq org doc 3 8 manual sql building sql statemen

  • 在 MySQL 中存储表情符号的编码问题：如何使用 Prisma ORM 在 NodeJS 中定义字符排序规则？

    亲爱的 Nodejs 专家和数据库专家 我们在 MySQL 数据库中存储表情符号和其他特殊字符时遇到问题 我们使用 Prisma 得到一个错误 这是我们使用的 ORM 参数无法从排序规则 utf8 general ci 转换为 utf8mb

随机推荐

热门标签