在MySQL中选择行作为列?
16lz
2021-01-22
Consider the following table "views"
考虑下面的表“视图”
user_id _date cnt
------------------------
1 2011-02-10 123
1 2011-02-11 99
1 2011-02-12 100
1 2011-02 13 12
2 2011-02-10 32
2 2011-02-11 433
2 2011-02-12 222
2 2011-02 13 334
3 2011-02-10 766
3 2011-02-11 654
3 2011-02-12 43
3 2011-02 13 27
...
100 2011-02-13 235
As you can see, the table holds pageviews (cnt) per day (_date) per user (user_id). I'm looking for a SELECT query that will output the user_ids as columns so the table-data will be in matrix form, as follows:
如您所见，该表保存了每个用户(user_id)每天的pageviews (cnt)。我正在寻找一个SELECT查询，它将以列的形式输出user_id，因此表数据将以矩阵形式出现，如下所示:
_date 1 2 3 ... 100
---------------------------------
2011-02-10 123 32 766
2011-02-11 99 433 654
2011-02-12 100 222 43
2011-02-13 12 334 27 235
Is this possible to do with a SELECT statement?
这可能与SELECT语句有关吗?
2 个解决方案
#1
4
If you're dealing with a finite set of user IDs, you could do something like this:
如果你处理的是有限的用户id，你可以这样做:
SELECT _date,
SUM(CASE WHEN _user_id = 1 THEN cnt ELSE 0 END) AS user1,
SUM(CASE WHEN _user_id = 2 THEN cnt ELSE 0 END) AS user2,
SUM(CASE WHEN _user_id = 3 THEN cnt ELSE 0 END) AS user3,
...
FROM views
GROUP BY _date
It's more of a hack than a good query, though.
不过，与其说这是一个好的查询，不如说是一个黑客。
更多相关文章
- Mysql 创建，授权，删除，修改用户
- 解决用户 'sa' 登录失败。原因: 未与信任 SQL Server 连接相关联
- 记一次无备份恢复Mysql误删用户数据
- 这被认为是正常形式的失败吗？
- MySQL root 用户密码重置
- Mysql5.7.10新加用户
- SQL查询 - 从表中查找不同的用户
- jsp+Mysql实现简单用户登陆
- DRP系统中用户增删改查（分页）Model1模式