mysql - 从3个 table 上获得最多的产品

Question

我在这里有一个案例，我想基于3个表（这是视图，emails_request和apply_request中的大多数表）创建前10名产品，我的表结构是这样的

product_views

===================
id | product_id | date
--------------------------
1  | 1          | today
2  | 1          | today
3  | 2          | today

product_email_request

===================
id | product_id | date
--------------------------
1  | 1          | today
2  | 1          | today
3  | 2          | today

product_apply_request

===================
id | product_id | date
--------------------------
1  | 1          | today
2  | 2          | today
3  | 2          | today

我的目标是实现这样的目标

product_id | view_count | email_count | apply_count
==========================================================
1          | 2          | 2           | 1
2          | 1          | 1           | 2

是否有可能在mysql上执行此操作？我很困惑，我曾经读过我应该为此使用工会，但仍然没有头绪。

这是SQL脚本

    /*
Navicat MariaDB Data Transfer

Source Server         : LOKAL
Source Server Version : 100121
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MariaDB
Target Server Version : 100121
File Encoding         : 65001

Date: 2017-07-23 17:17:25
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for product_apply
-- ----------------------------
DROP TABLE IF EXISTS `product_apply`;
CREATE TABLE `product_apply` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) DEFAULT NULL,
  `creation` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of product_apply
-- ----------------------------
INSERT INTO `product_apply` VALUES ('1', '1', '2017-07-23 17:17:00');
INSERT INTO `product_apply` VALUES ('2', '2', '2017-07-23 17:17:08');

-- ----------------------------
-- Table structure for product_emails
-- ----------------------------
DROP TABLE IF EXISTS `product_emails`;
CREATE TABLE `product_emails` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) DEFAULT NULL,
  `creation` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of product_emails
-- ----------------------------
INSERT INTO `product_emails` VALUES ('1', '1', '2017-07-23 16:46:32');
INSERT INTO `product_emails` VALUES ('2', '1', '2017-07-23 16:46:47');
INSERT INTO `product_emails` VALUES ('3', '2', '2017-07-23 16:47:05');
INSERT INTO `product_emails` VALUES ('4', '2', '2017-07-23 16:47:47');
INSERT INTO `product_emails` VALUES ('5', '3', '2017-07-23 17:00:18');
INSERT INTO `product_emails` VALUES ('6', '1', '2017-07-23 17:00:29');

-- ----------------------------
-- Table structure for product_views
-- ----------------------------
DROP TABLE IF EXISTS `product_views`;
CREATE TABLE `product_views` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `creation` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of product_views
-- ----------------------------
INSERT INTO `product_views` VALUES ('1', '1', '2017-07-23 16:47:25');
INSERT INTO `product_views` VALUES ('2', '1', '2017-07-23 16:47:32');
INSERT INTO `product_views` VALUES ('3', '2', '2017-07-23 16:47:38');

任何帮助都会很棒。
谢谢

Answer 1

以下查询将为您提供所需的结果。

我已经通过从products表中选择所有产品（我认为已经存在），然后在每个其他表上使用LEFT JOIN来编写此代码。通过使用LEFT JOIN，即使在一个或多个其他表中没有条目，也可以确保产品仍将包含在结果中。相应的计数值将为零。

SELECT p.id, COUNT(DISTINCT pa.id) AS apply_count, COUNT(DISTINCT pe.id) AS email_count, COUNT(DISTINCT pv.id) AS view_count
FROM products p
LEFT JOIN product_apply pa ON (p.id = pa.product_id)
LEFT JOIN product_emails pe ON (p.id = pe.product_id)
LEFT JOIN product_views pv ON (p.id = pv.product_id)
GROUP BY p.id