如何从一个MySQL列输出多个列？(How to output multiple columns from one MySQL column?)

如何从一个MySQL列输出多个列？(How to output multiple columns from one MySQL column?)

请考虑下表

tweets --------------------------------- tweet_id nyse_date class type --------------------------------- 1 2011-03-12 2 0 2 2011-03-12 1 1 3 2011-03-12 1 0 4 2011-03-12 1 0 5 2011-03-12 0 0 6 2011-03-12 1 0 7 2011-03-12 3 2 8 2011-03-12 3 0

每条推文都分配了一个'class'，它是1,2或3，'type'是0,1或2.我想概述每个类和类型中的推文数量，如下所示：

nyse_date class1 class2 class3 type0 type1 type2 ------------------------------------------------------- 2011-03-12 3 1 2 6 1 1

我开始使用以下查询（按日期分组，因为真实表有许多不同的日期）：

SELECT nyse_date, COUNT(class) AS class1 FROM tweets WHERE class = 1, COUNT(class) AS class2 FROM tweets WHERE class = 2 GROUP BY nyse_date ASC

预期的产出是

nyse_date class1 class2 -------------------------- 2011-03-12 3 1

但我得到了一个错误。 我相信这与我每个查询只能使用一个WHERE子句（我使用的是2）这一事实有关，但我不知道替代方案是什么。 你做？

任何帮助是极大的赞赏 ：-）

Consider the following table

tweets --------------------------------- tweet_id nyse_date class type --------------------------------- 1 2011-03-12 2 0 2 2011-03-12 1 1 3 2011-03-12 1 0 4 2011-03-12 1 0 5 2011-03-12 0 0 6 2011-03-12 1 0 7 2011-03-12 3 2 8 2011-03-12 3 0

Each tweet has assigned a 'class', which is either 1, 2 or 3 and a 'type', being 0, 1 or 2. I want to have an overview of the number of tweets in each class and type, as follows:

nyse_date class1 class2 class3 type0 type1 type2 ------------------------------------------------------- 2011-03-12 3 1 2 6 1 1

I started with the following query (grouping by date because the real table has many different dates):

SELECT nyse_date, COUNT(class) AS class1 FROM tweets WHERE class = 1, COUNT(class) AS class2 FROM tweets WHERE class = 2 GROUP BY nyse_date ASC

The expected output was

nyse_date class1 class2 -------------------------- 2011-03-12 3 1

but I got an error instead. I believe this has to do with the fact that I can only use one WHERE clause per query (and I'm using 2) but I don't know what the alternative would be. Do you?

Any help is greatly appreciated :-)

最满意答案

尝试这个：

SELECT nyse_date, SUM(class = 1) AS class1, SUM(class = 2) AS class2, SUM(class = 3) AS class3 FROM tweets GROUP BY nyse_date ASC

class = 1是一个布尔表达式，它的值为TRUE或FALSE 。 TRUE等于1 。 因此，使用SUM实际上意味着“计算条件保持的所有行”

Try this:

SELECT nyse_date, SUM(class = 1) AS class1, SUM(class = 2) AS class2, SUM(class = 3) AS class3 FROM tweets GROUP BY nyse_date ASC

The class = 1 is a boolean expression, which evalues to TRUE or FALSE. A TRUE equals 1. Hence using SUM practically means "count all rows for which condition holds"