时间戳

首页 > 解决方案 > 按单元格是否存在于另一个表中进行分组

mysql - 按单元格是否存在于另一个表中进行分组

问题描述

我的 SQL 语法是 MariaDB (MySQL)

我有一张有组织发言人的桌子，一张有 VIP 组织的桌子，还有一张有演示文稿的桌子。如何按发言人的组织是否为 VIP 进行分组或排序，以便 VIP 组织发言人在检索所有演示文稿时显示在顶部？

表格展示：int presentation_id, int person_id, varchar title, date date

餐桌人：int person_id, varchar name, varchar function, varchar organisation

表 VIP_orgs：int org_id, varchar org_name

不起作用的查询：

CREATE TABLE persons (
    person_id INT AUTO_INCREMENT,
    name VARCHAR(64),
    organisation VARCHAR(64),
    PRIMARY KEY (person_id)
);

INSERT INTO `persons` (name, organisation) VALUES
    ("Guy Fieri",       "VIP-org"),
    ("Fiona",           "VIP inc."),
    ("Mr. Robot",       "Evil Corp"),
    ("Marcus Antonius", "Rome"),
    ("Cicero",          "Rome"),
    ("Shrek",           "VIP inc.");
    
CREATE TABLE presentations (
    presentation_id INT AUTO_INCREMENT,
    person_id INT,
    PRIMARY KEY (presentation_id)
);

INSERT INTO `presentations` (person_id) VALUES
    (1),(1),(1),(1), -- guy fieri has 4
    (2),
    (3),(3),(3),(3),(3),
    (4),(4),(4),(4),
    (5),(5),(5),
    (6),(6),(6),(6);

CREATE TABLE VIP_orgs (
    org_id INT AUTO_INCREMENT,
    org_name VARCHAR(64),
    PRIMARY KEY (org_id)
);

INSERT INTO `VIP_orgs` (org_name) VALUES 
    ("VIP-org"),
    ("VIP inc.");

SELECT organisation, COUNT(*) AS count
FROM `presentations`
JOIN `persons` ON `presentations`.person_id = `persons`.person_id
GROUP BY (SELECT org_name FROM `VIP_orgs` WHERE `VIP_orgs`.org_name = organisation), organisation
ORDER BY count DESC;

我期望它做什么：

返回一个表org_name, (total combined number of presentations by each spokeperson of that org)

按演示数量排序，按组织分组，VIP 组织在顶部分组。VIP 和非 VIP 部分应按数量独立排序。因此，返回的表应如下所示：

name      count
VIP inc.  5
VIP-org   4
Rome      7
Evil Corp 5

查询工作 50%：它计算所有演示文稿并对其进行排序，但它似乎没有按 VIP 组织分组。实际上，返回的表如下所示：

name      count
Rome      7
VIP inc.  5
Evil Corp 5
VIP-org   4

标签： mysql

解决方案

架构看起来不正确。我建议创建一个organisations带有vip BOOLEAN列的表并在表中添加外键persons。在架构中进行以下更改：

CREATE TABLE `organisations` (
    organisation_id INT AUTO_INCREMENT,
    name VARCHAR(64),
    vip BOOLEAN,
    PRIMARY KEY (organisation_id)
);

INSERT INTO `organisations` (name, vip) VALUES
    ("VIP-org", True),
    ("VIP inc.", True),
    ("Evil Corp", False),
    ("Rome", False);

CREATE TABLE persons (
    person_id INT AUTO_INCREMENT,
    name VARCHAR(64),
    organisation_id INT,
    PRIMARY KEY (person_id),
    FOREIGN KEY (organisation_id) REFERENCES `organisations`(organisation_id) 
);

INSERT INTO `persons` (name, organisation_id) VALUES
    ("Guy Fieri", 1),
    ("Fiona", 2),
    ("Mr. Robot", 3),
    ("Marcus Antonius", 4),
    ("Cicero", 4),
    ("Shrek", 2);

现在查询看起来像这样：

SELECT `organisations`.name as organisation, COUNT(*) AS count
FROM `presentations`
JOIN `persons` ON `presentations`.person_id = `persons`.person_id
JOIN `organisations` ON `organisations`.organisation_id = `persons`.organisation_id
GROUP BY `organisations`.organisation_id
ORDER BY `organisations`.vip DESC, count DESC;

输出：

+--------------+------------+
| organisation |      count |
+--------------+------------+
| VIP inc.     |          5 |
| VIP-org      |          4 |
| Rome         |          7 |
| Evil Corp    |          5 |
+--------------+------------+

你可以在这里看到结果：db <> fiddle

推荐阅读