MySQL查询获取动态列及其值

Question

我有一个复杂的数据库结构。我试图解决它，但不太正确。

我有五个表:

1. User 表，用于获取办公室名称和 office_id
2. Service_list，其中包含所有服务名称
3. Service，包含所有基于office id的服务信息
4. Service_trasaction，我们从中获取所有基于 id (service.service_id = service_transaction.id) 的服务。

现在我需要这样的输出:

[office_name] [count recipients] [service_1] [service_2] .........
=======================================================================
Dhaka               5                4            2
Ctg                 0                5            0
Khulna              2                2            0

目前，我只能使用以下查询获取办公室名称和总收件人:

SELECT u.id, u.office_name, COUNT(r.id) AS `count`
FROM users u LEFT JOIN recipient r ON u.id = r.office_id
where(u.type = 'agency' and u.del_status = 0)
GROUP BY u.id, u.office_name;

这是所有表的结构:

CREATE TABLE `recipient` (
  `id` int(10) NOT NULL,
  `name` varchar(100) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `mobile` varchar(15) NOT NULL,
  `age` int(10) NOT NULL,
  `reg_no` varchar(10) NOT NULL,
  `address` varchar(255) NOT NULL,
  `disability_type` int(10) NOT NULL,
  `education` varchar(255) NOT NULL,
  `office_id` int(10) NOT NULL,
  `del_status` tinyint(1) NOT NULL,
  `create_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;      

 CREATE TABLE `users` (
  `id` tinyint(4) NOT NULL,
  `office_name` varchar(255) DEFAULT NULL,
  `district_id` int(10) DEFAULT NULL,
  `upazilla_id` int(10) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `mobile` varchar(11) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(100) NOT NULL,
  `type` varchar(10) NOT NULL,
  `del_status` tinyint(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `service` (
  `id` int(10) NOT NULL,
  `recipient_number` int(50) NOT NULL,
  `date` date NOT NULL,
  `office_id` int(10) NOT NULL,
  `del_status` tinyint(1) NOT NULL,
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `service_list` (
  `id` int(3) NOT NULL,
  `service_name` varchar(50) NOT NULL,
  `del_status` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `service_transaction` (
  `id` int(10) NOT NULL,
  `service_transaction_id` int(50) NOT NULL,
  `service_id` int(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

这里是 sql 转储文件链接:https://file.town/download/cez6u43ikr84by4xrzm34pjrf

Answer 1

这听起来像是您在尝试转换 SQL 查询的结果，或者生成一个矩阵，其中一个表创建行，另一个表创建列。

如果行和列都是任意列表，则不能这样做。如果列是任意列表但行是固定的，则您也不能在 MySQL 中执行此操作，因为 MySQL 没有 PIVOT 函数。

如果认为有一定数量的列不会更改(或者每次更改其中一个时都可以编辑 SQL 语句)，那么您可以通过从聚合函数手动构建每个列来完成此操作, 但它在查询中变得非常困惑，你真的必须考虑你正在做什么来实现它。

在您的具体示例中，类似这样的方法就是:

SELECT
    office_name,
    COUNT(*) as count_recipients,
    SUM(service.id = 1) AS service_1,
    SUM(service.id = 2) AS service_2,
    ...
    SUM(service.id = n) AS service_n
FROM
    ...etc...
GROUP BY
    office_name

如您所见，您不能让它自动创建那些服务列 - 您必须为每个 service.id 创建一个。充其量，您的软件可以获得所有服务 ID 的完整列表，并以编程方式为每个服务 ID 使用一列构建查询，但是您可以在查询中返回的列数是有限制的，所以这只会如此远。

更好的解决方案是在您的演示代码中处理显示，而不是在数据库查询中(正如上面对您的问题的评论中名为 Strawberry 的人所建议的那样)。