Mysql查询，使用字段作为列

I have the following table structure

我有以下的表格结构

+-------+------------+-----------+---------------+
| id    |assigned_to | status    |  group_id     |              
+-------+------------+-----------+---------------+
| 1     | 1001       | 1         |    19         |
+-------+------------+-----------+---------------+
| 2     | 1001       | 2         |    19         |
+-------+------------+-----------+---------------+
| 3     | 1001       | 1         |    18         |
+-------+------------+-----------+---------------+
| 4     | 1002       | 2         |    19         |
+-------+------------+-----------+---------------+
| 5     | 1002       | 2         |    19         |
+-------+------------+-----------+---------------+

I would like to get the information in the following format

我想要以下格式的信息

+-------+------------+-----------+
|       |   1001     |   1002    |          
+-------+------------+-----------+
| 1     |    1       |    0      |   
+-------+------------+-----------+
| 2     |    1       |    2      |   
+-------+------------+-----------+

So basically I am looking to use the assigned to field as the column names. Then the rows represent the status. So for example in the table we have two rows where user 1002 has a status of 2, therefore the sum is shown on that particular status row.

因此，基本上我希望使用指定的to字段作为列名。然后行表示状态。例如，在表中，我们有两行用户1002的状态为2，因此这个和显示在这个特定的状态行上。

Please note that the group_id must be 19. Hence why I left out the row with id 3 on my table.

请注意group_id必须为19。因此，我将id 3放在我的表中。

Can someone point me in the right direction. Im sure there is a name for this type of query, but I can't for the life of me put this into words. I have tried various other queries, but none of them even come close to this.

谁能给我指出正确的方向吗?我确信这种类型的查询有一个名称，但我无法用语言来描述我的一生。我尝试过各种各样的查询，但没有一个查询能达到这个程度。

2 个解决方案

#1

Marc B is right, there is no way to pivot a table -i.e. converting the content of a field into columns- unless you make some assumptions, like supossing that the values of assigned_to are somewhat fixed.

Marc B是对的，没有办法对表进行透视——比如将字段的内容转换为列——除非你做一些假设，比如对assigned_to的值进行某种程度的固定。

On the other hand, this is the kind of problems that can be solved by a program. It is not an easy program, but it can do the job.

另一方面，这是程序可以解决的问题。这不是一个简单的程序，但它可以做到这一点。

I recently made a program similar to this in java, if you are interested I can post the core of it here.

我最近在java中做了一个类似的程序，如果您感兴趣的话，我可以在这里发布它的核心。

#2

you might want to read this article http://www.artfulsoftware.com/infotree/qrytip.php?id=523

您可能想要阅读本文http://www.artfulsoftware.com/infotree/qrytip.php?id=523

i'd be something like

我很喜欢的东西

SELECT 
  assigned_to, 
  COUNT( CASE assigned_to WHEN '1001' THEN 1 ELSE 0 END ) AS '1001', 
  COUNT( CASE assigned_to WHEN '1002' THEN 1 ELSE 0 END ) AS '1002'
FROM table
WHERE group_by = 19
GROUP BY assigned_to WITH ROLLUP;

or something like that (i haven't tested this code.. )

或者类似的东西(我还没有测试过这段代码)

in the article, he does it using SUM() you'd have to do it with COUNT() and add a WHERE constraint for the group_id

在本文中，他使用SUM()来做它，您必须使用COUNT()来完成它，并为group_id添加一个约束。

Hope this helps

希望这有助于

#1