mysql - 在 MySQL 中组合多个查询-6ren

mysql - 在 MySQL 中组合多个查询

转载作者：行者123 更新时间：2023-11-29 03:04:49

这个问题有点啰嗦，对此我深表歉意。我的 SQL 技能严重缺乏(我想尽快补救)。结果，我真的无法理解如何解决我遇到的这个问题。

基本上，我们的项目将用户通知存储在单个表 notifications 中。表结构如下所示:

+-----------+-----------+----------------+------------+
|  user_id  |  subject  |     action     |     date   |
+-----------+-----------+----------------+------------+
|     1     |     2     | started_follow | 1371034287 |
|     1     |     2     | stopped_follow | 1371034287 |
|     2     |     5     |   added_item   | 1371034287 |
+-----------+-----------+----------------+------------+

user_id 始终包含执行该操作的用户的 ID，而 date 显然是通知注册的日期。棘手的部分是 subject 是对另一个表的 ID 的引用，而该表在很大程度上取决于 action 列的值。

因此，例如，在示例数据的前两条记录中，subject 是对 users 表中 ID 的引用(即被关注的用户，然后取消关注)。在第三条记录中，subject 是对 items 表中 ID 的引用。

我们还需要执行多个JOIN 语句，具体取决于action 的条件。因此，例如，如果它是 added_item，我们需要 JOIN 其他几个表(以检查设置和其他要求)。

我在代码中遇到了一个遗留函数，它主要检查自指定日期以来表中存在给定用户的通知数量。之前的开发者只是简单的使用了一系列的查询，然后返回了几个SELECT COUNT(*)语句的总数如下(注意，这都是在PHP的User类):

// Get the number of notifications since the specified time (or of all time):
public function countNotifications($since = '')
{
    $sinceString = ($since == '') ? '' : "AND `date` > '$since'";

    // Notifications when someone follows $this:
    $started_following = $this->_database->query("SELECT COUNT(*) AS `count`
                                                  FROM `notifications`
                                                  WHERE `action` = 'started_following'
                                                    AND `subject` = '{$this->id}'
                                                    $sinceString
                                                    AND `notifications`.`user_id` != '{$this->id}'")->fetchObject();

    // Notifications when someone stops following $this:
    $stopped_following = $this->_database->query("SELECT COUNT(*) AS `count`
                                                  FROM `notifications`
                                                  WHERE `action` = 'stopped_following'
                                                    AND `subject` = '{$this->id}'
                                                    $sinceString
                                                    AND `notifications`.`user_id` != '{$this->id}'")->fetchObject();

    // Notifications when someone sends $this a message:
    $sent_message = $this->_database->query("SELECT COUNT(*) AS `count`
                                             FROM `notifications`
                                             WHERE `action` = 'sent_message'
                                               AND `subject` = '{$this->id}'
                                               $sinceString
                                               AND `notifications`.`user_id` != '{$this->id}'")->fetchObject();

    // Notifications when someone favorites $this' items:
    $favorited_item = $this->_database->query("SELECT COUNT(*) AS `count`
                                               FROM `notifications`
                                               INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
                                               INNER JOIN `categories` ON `items`.`category` = `categories`.`id`
                                               WHERE `notifications`.`action` = 'favorited_item'
                                                 AND `categories`.`owner` = '{$this->id}'
                                                 $sinceString
                                                 AND `notifications`.`user_id` != '{$this->id}'")->fetchObject();

    // Notifications when someone adds a comment to $this' items:
    $comments = $this->_database->query("SELECT COUNT(*) AS `count`
                                         FROM `notifications`
                                         INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
                                         INNER JOIN `categories` ON `items`.`category` = `categories`.`id`
                                         WHERE `notifications`.`action` = 'added_comment'
                                           AND `categories`.`owner` = '{$this->id}'
                                           $sinceString
                                           AND `notifications`.`user_id` != '{$this->id}'")->fetchObject();

    // Notifications when a follower of $this adds a new item:
    $new_items = $this->_database->query("SELECT COUNT(*) AS `total`
                                         FROM `notifications`
                                         INNER JOIN `categories` ON `notifications`.`subject` = `categories`.`id`
                                         INNER JOIN (SELECT `followee` FROM `user_followers` WHERE `follower` = '{$this->id}') `followers`
                                         WHERE `notifications`.`action` = 'added_item'
                                           AND `followee` = `user_id`
                                           $sinceString
                                           AND `user_id` !=  '{$this->id}'")->fetchObject();

    // Notifications when a follower of $this adds a new collection:
    $new_collections = $this->_database->query("SELECT COUNT(*) AS `total`
                                                FROM `notifications`
                                                INNER JOIN `categories` ON `notifications`.`subject` = `categories`.`id`
                                                INNER JOIN (SELECT `followee` FROM `user_followers` WHERE `follower` = '{$this->id}') `followers`
                                                WHERE `notifications`.`action` = 'added-collection'
                                                   AND `followee` = `user_id`
                                                   $sinceString
                                                   AND `user_id` !=  '{$this->id}'")->fetchObject();

    // Notifications when a follower of $this adds a new category:
    $new_categories = $this->_database->query("SELECT COUNT(*) AS `total`
                                               FROM `notifications`
                                               INNER JOIN  `categories` ON `notifications`.`subject` =  `categories`.`id`
                                               INNER JOIN (SELECT `followee` FROM `user_followers` WHERE `follower` = '{$this->id}') `followers`
                                               WHERE `notifications`.`action` =  'added-category'
                                                 AND `followee` = `user_id`
                                                 $sinceString
                                                 AND `user_id` !=  '{$this->id}'")->fetchObject();

    // Selling Notifications:
    // Notifications when someone makes an offer for an item $this is selling:
    $offers = $this->_database->query("SELECT COUNT(*) AS `count`
                                       FROM `notifications`
                                       INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
                                       INNER JOIN `categories` ON `items`.`category` = `categories`.`id`
                                       WHERE `notifications`.`action` = 'made_offer'
                                         AND `categories`.`owner` = '{$this->id}'
                                         $sinceString
                                         AND `notifications`.`user_id` != '{$this->id}'")->fetchObject();

    // Notifications when someone purchases an item $this is selling:
    $purchases = $this->_database->query("SELECT COUNT(*) AS `count`
                                          FROM `notifications`
                                          INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
                                          INNER JOIN `categories` ON `items`.`category` = `categories`.`id`
                                          INNER JOIN (SELECT COUNT(*) AS `count`, `item_id`
                                                      FROM `user_favorite_items`
                                                      WHERE `user_id` = '{$this->id}') `following` ON `items`.`id` = `following`.`item_id`
                                          WHERE `notifications`.`action` = 'bought_item'
                                            AND `following`.`count` = 1
                                            AND `categories`.`owner` = '{$this->id}'
                                            $sinceString
                                            AND `notifications`.`user_id` != '{$this->id}'")->fetchObject();

    // Notifications when an item that $this favorited is listed for sale:
    $item_sales = $this->_database->query("SELECT COUNT(*) AS `count`
                                           FROM `notifications`
                                           INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
                                           INNER JOIN (SELECT COUNT(*) AS `count`, `item_id`
                                                       FROM `user_favorite_items`
                                                       WHERE `user_id` = '{$this->id}'
                                                    ) `following` ON `items`.`id` = `following`.`item_id`
                                           WHERE `notifications`.`action` = 'selling_item'
                                             AND `following`.`count` = 1
                                             $sinceString
                                             AND `notifications`.`user_id` != '{$this->id}'")->fetchObject();

    // Return the counts:
    return ($started_following->count + 
            $stopped_following->count +
            $sent_message->count +
            $favorited_item->count +
            $comments->count +
            $new_items->count +
            $new_collections->count +
            $new_categories->count +
            $offers->count +
            $purchases->count +
            $item_sales->count);
}

虽然这很好地完成了工作，但它使得从指定日期获取所有记录变得极其困难，例如，或者与特定用户 ID 相关的所有记录。

我想我的问题确实是组合所提供的众多 SQL 语句的最佳方式是什么？我已经尝试过 LEFT JOIN，但如您所见，我们需要根据 notifications 的值将表连接到不同的列。action。虽然我可以使用表别名来做到这一点，但它往往会返回很多的冗余数据。

基本上，我想结合上面给出的 COUNT(*) 查询，这样我们就可以简单地返回给定用户 ID 和/的所有 notifications.*或时间段。

我还想尽可能避免使用 UNION(出于显而易见的原因)。

很抱歉这个冗长的问题，但我已尽力让所有内容都尽可能清楚。在有人询问之前，我无法更改数据结构或数据库模式，因为这是针对现有站点的。

我整理了一个 SQLFiddle让事情更清楚一点。

最佳答案

这些查询的复杂性和它们正在做的事情各不相同。

前 3 个可以组合成一个返回(最多)3 行的查询:-

SELECT action, COUNT(*) AS `count`
  FROM `notifications`
  WHERE `action` IN ( 'started_following', 'stopped_following', 'sent_message')
    AND `subject` = '{$this->id}'
    $sinceString
    AND `notifications`.`user_id` != '{$this->id}'
GROUP BY action

要强制返回 3 行，您可以这样做:-

SELECT WantedActions.WantedAction, COUNT(*) AS `count`
  FROM (SELECT 'started_following' AS WantedAction UNION SELECT 'stopped_following' UNION SELECT 'sent_message') AS WantedActions
  LEFT OUTER JOIN `notifications`
  ON WantedActions.WantedAction = notifications.action
  WHERE `subject` = '{$this->id}'
    $sinceString
    AND `notifications`.`user_id` != '{$this->id}'
GROUP BY WantedActions.WantedAction

你或许可以为其他人做类似的事情

编辑

SELECT started_following, stopped_following, sent_message, favorited_item, comments, new_items, new_collections, new_categories, offers, purchases, item_sales
FROM (SELECT SUM(IF(`action` = 'started_following', 1, 0) AS started_following, 
                    SUM(IF(`action` = 'stopped_following', 1, 0) AS stopped_following, 
                    SUM(IF(`action` = 'sent_message', 1, 0) AS sent_message
            FROM `notifications`
            WHERE `subject` = '{$this->id}'
            $sinceString
            AND `notifications`.`user_id` != '{$this->id}') Sub1
CROSS JOIN (SELECT SUM(IF(`action` = 'favorited_item', 1, 0) AS favorited_item, 
                    SUM(IF(`action` = 'added_comment', 1, 0) AS comments, 
                    SUM(IF(`action` = 'made_offer', 1, 0) AS offers
            FROM `notifications`
            INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
            INNER JOIN `categories` ON `items`.`category` = `categories`.`id`
            WHERE `categories`.`owner` = '{$this->id}'
            $sinceString
            AND `notifications`.`user_id` != '{$this->id}') Sub2
CROSS JOIN (SELECT SUM(IF(`action` = 'added_item', 1, 0) AS new_items, 
                    SUM(IF(`action` = 'added-collection', 1, 0) AS new_collections, 
                    SUM(IF(`action` = 'added-category', 1, 0) AS new_categories
            FROM `notifications`
            INNER JOIN `categories` ON `notifications`.`subject` = `categories`.`id`
            INNER JOIN (SELECT `followee` FROM `user_followers` WHERE `follower` = '{$this->id}') `followers`
            WHERE `followee` = `user_id`
            $sinceString
            AND `user_id` !=  '{$this->id}') Sub3
CROSS JOIN (SELECT COUNT(*) AS purchases
          FROM `notifications`
          INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
          INNER JOIN `categories` ON `items`.`category` = `categories`.`id`
          INNER JOIN (SELECT COUNT(*) AS `count`, `item_id`
                      FROM `user_favorite_items`
                      WHERE `user_id` = '{$this->id}'
                    ) `following` ON `items`.`id` = `following`.`item_id`
          WHERE `notifications`.`action` = 'bought_item'
            AND `following`.`count` = 1
            AND `categories`.`owner` = '{$this->id}'
            $sinceString
            AND `notifications`.`user_id` != '{$this->id}') Sub4
CROSS JOIN (SELECT COUNT(*) AS item_sales
           FROM `notifications`
           INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
           INNER JOIN (SELECT COUNT(*) AS `count`, `item_id`
                       FROM `user_favorite_items`
                       WHERE `user_id` = '{$this->id}'
                    ) `following` ON `items`.`id` = `following`.`item_id`
           WHERE `notifications`.`action` = 'selling_item'
             AND `following`.`count` = 1
             $sinceString
             AND `notifications`.`user_id` != '{$this->id}') Sub5

编辑 - 使用联合

SELECT action, COUNT(*)  AS action_count
            FROM `notifications`
            WHERE `subject` = '{$this->id}'
            AND action IN ('started_following', 'stopped_following', 'sent_message')
            $sinceString
            AND `notifications`.`user_id` != '{$this->id}'
            GROUP BY action
UNION
SELECT action, COUNT(*)  AS action_count
            FROM `notifications`
            INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
            INNER JOIN `categories` ON `items`.`category` = `categories`.`id`
            WHERE `categories`.`owner` = '{$this->id}'
            AND action IN ('favorited_item', 'added_comment', 'made_offer')
            $sinceString
            AND `notifications`.`user_id` != '{$this->id}'
            GROUP BY action
UNION
SELECT action, COUNT(*)  AS action_count
            FROM `notifications`
            INNER JOIN `categories` ON `notifications`.`subject` = `categories`.`id`
            INNER JOIN (SELECT `followee` FROM `user_followers` WHERE `follower` = '{$this->id}') `followers`
            WHERE `followee` = `user_id`
            AND action IN ('added_item', 'added-collection', 'added-category')
            $sinceString
            AND `user_id` !=  '{$this->id}'
            GROUP BY action
UNION
SELECT 'purchases' AS action, COUNT(*)  AS action_count
          FROM `notifications`
          INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
          INNER JOIN `categories` ON `items`.`category` = `categories`.`id`
           INNER JOIN (SELECT user_id, COUNT(*) AS `count`, `item_id`
                       FROM `user_favorite_items`
                       GROUP BY `user_id`, item_id
                    ) `following` ON `items`.`id` = `following`.`item_id` AND `notifications`.`user_id` = `following`.`user_id`
          WHERE `notifications`.`action` = 'bought_item'
            AND `following`.`count` = 1
            AND `categories`.`owner` = '{$this->id}'
            $sinceString
            AND `notifications`.`user_id` != '{$this->id}'
UNION
SELECT 'item_sales' AS action, COUNT(*) AS action_count
           FROM `notifications`
           INNER JOIN `items` ON `notifications`.`subject` = `items`.`id`
           INNER JOIN (SELECT user_id, COUNT(*) AS `count`, `item_id`
                       FROM `user_favorite_items`
                       GROUP BY `user_id`, item_id
                    ) `following` ON `items`.`id` = `following`.`item_id` AND `notifications`.`user_id` = `following`.`user_id`
           WHERE `notifications`.`action` = 'selling_item'
             AND `following`.`count` = 1
             $sinceString
             AND `notifications`.`user_id` != '{$this->id}'

关于mysql - 在 MySQL 中组合多个查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/17409400/

文章推荐： php - MySQL DATETIME 函数美与性能(速度)

文章推荐： java - 这是对这段 Java 代码的正确封装吗？我很困惑

mysql - 只有 Mysql OR mysql+sqlite OR mysql+自己的解决方案
目前我正在构建相当大的网络系统，我需要强大的 SQL 数据库解决方案。我选择 Mysql 而不是 Postgres，因为一些任务需要只读(MyISAM 引擎)而其他任务需要大量写入(InnoDB)。
mysql - Linux/mysql 将 mysql 表输出写入文件并保持 mysql 格式。
我在 mysql 中使用如下命令。当它显示表格数据时，它被格式化为一个非常干净的表格，间距均匀且 |作为列分隔符。 SELECT * FROM TABLE_NAME; 当我从 CLI 运行命令时，如下
mysql - 无法从终端加载 mysql 但可以使用系统首选项启动 mysql
我知道这个问题之前已经被问过好几次了，我已经解决了很多问题，但到目前为止没有任何效果。 MySQL 试图将自身安装到的目录 (usr/local/mysql) 肯定有问题。关于我的错误的奇怪之处在于我
mysql - 在 mysql 数据查询上获取不需要的输出 mysql
以下是我的 SQL 数据结构，我正在尝试如下两个查询: Select Wrk_ID, Wrk_LastName, Skill_Desc from Worker, Skill where
mysql - 将本地 mysql 服务器复制到基于云的 mysql
我们有一个本地 mysql 服务器(不在公共(public)域上)，并希望将该服务器复制到我们拥有的 google 云 sql 实例。我的问题是:1.这可能吗？2.我们的本地服务器只能在本地网络上访问
mysql - MySQL 触发器上 MySQL 变量的算术运算
我有一个表(test_table)，其中一些字段值(例如字段 A、B 和 C)是从外部应用程序插入的，还有一个字段(字段 D)，我想从现有表(store_table)插入其值，但在插入前者(A、B 和
mysql - 无法在创建 MySQL 服务器的同一计划中使用 mysql
我想创建一个 AWS RDS 实例，然后使用 terraform 管理数据库用户。因此，首先，我创建了一个 RDS 实例，然后使用创建的 RDS 实例初始化 mysql 提供程序，以进一步将其用于用户
mysql - MySql 用户数量对 MySql 性能影响大吗？
当用户在我的网站上注册时，他们会在我的一个数据库中创建自己的表格。该表存储用户发布的所有帖子。我还想做的是也为他们生成自己的 MySql 用户——该用户仅有权从他们的表中读取、写入和删除。创建它应该
mysql - mysql 表中的下和子类别(coldfusion，mysql)
我有一个关于 ColdFusion 和 Mysql 的问题。我有两个表:PRODUCT 和 PRODUCT_CAT。我想列出包含一些标记为:IS_EXTRANET=1 的特殊产品的类别。所以我写了这个
mysql - 如何使用 MYSQL 运算符选择列所有值都必须可用 - MYSQL
我想获取 recipes_id 列的值，以获取包含 ingredient_id 的 2,17 和 26 条目的值。假设 ingredient_id 2 丢失则不获取记录。我已经尝试过 IN 运算符
mysql - MySQL 服务器和 MySQL 客户端有什么区别
在 Ubuntu 中，我通常安装两者，但 MySQL 的客户端和服务器之间有什么区别。作为奖励，当一个新语句提到它需要 MySQL 5.x 时，它是指客户端、服务器还是两者兼而有之。例如这个链接ht
mysql - mysql - 如何在没有触发器和手动插入的情况下在插入时生成/自动增加 guid mysql？
我重新访问了我的数据库并注意到我有一些 INT 类型的主键。这还不够独特，所以我想我会有一个指导。我来自微软 sql 背景，在 ssms 中你可以选择类型为“uniqeidentifier”并自
mysql - Oracle MySQL 与 MySQL 相同吗？
我的系统上有 MySQL，我正在尝试确定它是 Oracle MySQL 还是 MySQL。 Oracle MySQL 有区别吗: http://www.oracle.com/us/products/m
mysql - 本地 mysql 服务器和生产 mysql 服务器之间的显着性能差异
我是在生产 MySQL 中运行的应用程序的新维护者。之前的维护者已经离开，留下的文档很少，而且联系不上了。我面临的问题是执行以下请求大约需要 10 秒: SELECT COUNT(*) FROM `
mysql - 如何自动将数据从一个 MySQL 数据库传输到另一个 MySQL 数据库？
我有两个位于不同机器上的 MySQL 数据库。我想自动将数据从一台服务器传输到另一台服务器。比方说，我希望每天早上 4:00 进行数据传输。可以吗？是否有任何 MySQL 内置功能可以让我们做到这一
mysql - 从 mysql 目录外的 mysql 表查询？
有什么方法可以使用 jdbc 查询位于 mysql 根目录之外的目录中的 mysql 表，还是必须将它们移动到 mysql 根目录内的数据库文件夹中？我在 Google 上搜索时没有找到任何东西。最
mysql - 使用另一个 mysql 表的值更新 Mysql 表
我在 mysql 数据库中有两个表。成员和 ClassNumbers。两个表都有一个付费年份字段，都有一个代码字段。我想用代码数字表中的值更新成员表中的付费年份，其中成员中的代码与 ClassNumb
mysql - 是否可以将本地 MySQL 数据库复制到远程 MySQL 数据库？
情况:我有 2 台服务器，其中一台当前托管一个实时 WordPress 站点，我希望能够将该站点转移到另一台服务器，以防第一台服务器出现故障。传输源文件很容易；传输数据库是我需要弄清楚如何做的。两台服
mysql - 使用 mysql 查询复制 mysql 数据库
Phpmyadmin 有一个功能是“复制数据库到”..有没有mysql查询来写这个函数？类似于将 db A 复制到新的 db B。最佳答案首先创建复制数据库: CREATE DATABASE du
mysql - 当 mySQL 已安装并由另一个应用程序配置时，为新应用程序配置 mySQL
我有一个使用 mySQL 作为后端的库存软件。我已经在我的计算机上对其进行了测试，并且运行良好。当我在计算机上安装我的软件时，我必须执行以下步骤: 安装 mySQL 服务器将用户名指定为“root

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

mysql - 在 MySQL 中组合多个查询