MySQL查询获取动态列及其值 [英] MySQL query get dynamic column and its value

问题描述

我有一个复杂的数据库结构.我试图解决它，但不能完全正确.

I've got a complex DB structure. I tried to solve it, but couldn't quite get it right.

我有五个表:

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

1. User table for getting office name and office_id
2. Service_list, which contains all service names
3. Service, which contains all service info based on office id
4. Service_trasaction, from which we get all services based on id (service.service_id = service_transaction.id).

现在我需要这样的输出:

Now I need output like this:

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

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

Currently, I'm only able to get office name and total recipients , using this query:

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

here is the sql dump file link : https://file.town/download/cez6u43ikr84by4xrzm34pjrf

推荐答案

听起来您正在尝试透视SQL查询的结果，或者生成一个矩阵，其中一个表创建行，另一个表创建列.

It sounds like you're trying to pivot the results of an SQL query, or generate a matrix where one table creates the rows and the other creates the columns.

如果行和列都是任意列表，则无法执行此操作.如果列是任意列表，但行是固定的，则您也无法在MySQL中执行此操作，因为MySQL没有PIVOT函数.

If both of the rows and columns are arbitrary lists, you can't do this. If the columns are an arbitrary list but the rows are fixed, you also can't do this in MySQL, as MySQL doesn't have a PIVOT function.

如果存在一定数量的认为不会更改的列(或者每次更改其中的一个都可以编辑SQL语句)，则可以通过从聚合函数中手动构建每个列来进行此操作，但是它在查询中的确非常混乱，您真的必须考虑要实现它的目的.

If there's a set number of columns thought that won't change (or the SQL statement can be edited every time one of those changes), then you can sort of do this by manually building up each of the columns from aggregate functions, but it gets really really messy in the query and you really have to think about what you're doing to achieve it.

在您的特定示例中，将采用以下方法:

In your specific example, something like this would be the approach for that:

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创建带有列的查询，但是您可以在查询中返回的列数受到限制，因此只能这样做远.

As you can see, you can't have it automatically create those service columns - you have to do one for each service.id. At best, your software could get the full list of all service id's, and programmatically build the query with a column for each service id, but there are limits to the number of columns you can return in a query, so this will only go so far.

更好的解决方案是处理显示代码中的显示，而不处理数据库查询中的显示(如上面问题中的注释中可取的草莓名称所示).

Much better solution is to handle the display in your presentation code, not in the database query (as suggested by the delectably named Strawberry in the comments on your question above).

这篇关于MySQL查询获取动态列及其值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！