如何使许多列成行

Question

​

​

像上面这样的桌子

​

​

我想让它变成这样。

提供的表是用户每次访问时累积用户ID和访问时间的表。

在这个表中，从第一次约会到现在，如果用户每天访问，我想做O，如果不是，则做X。

一开始，我认为我可以用大小写，因为我必须简单地将行分成列，但是很难把所有的日期都写下来。

接下来要考虑的是让它成为一个递归的CTE，但是递归的CTE并不是一个连接，而是一个联合。

(我是个初学者，所以我不知道.)

是否需要使用递归CTE创建结果值并将其转换回行为列？我不确定..。

我该怎么接近它？

如果你告诉我你是否可以不用写查询就能使用任何想法或概念，我真的很感激。

祝您今天愉快。

Answer 1

实际上没有很多好的方法来做到这一点，而且它是高度可变的，这取决于您的用例。mySQL非常需要其他DBMS中存在的枢轴功能。一种方法是使用CONCAT和GROUP_CONCAT动态生成SQL，然后用准备好的语句执行它。看看这把小提琴：

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=4697dadf0ebf2eaadcf98b4205b42f67

因此，这里首先是如何生成带有concat和GROUP_CONCAT的语句：

select concat(
  'select userID, ',
  group_concat(distinct "sum(dt='", dt, "') as '", dt, "'" order by dt),
  ' from sample group by userID'
) from sample;

我们将使用带有DISTINCT的GROUP_CONCAT为每个不同的日期生成一列。如果格式化困扰您，可以清理它，添加换行符，并在group_concat中使用'，‘分隔符，但这不会使它运行得更好。

一旦可以生成语句，就需要将其放入一个变量中，这非常简单：

select concat(
  'select userID, ',
  group_concat(distinct "sum(dt='", dt, "') as '", dt, "'" order by dt),
  ' from sample group by userID'
) into @stmt from sample;
select @stmt;

然后，您所需要做的就是从该变量创建一个准备好的语句并执行它：

prepare stmt from @stmt;
execute stmt;

还可以选择释放它，尽管它将在会话结束后自动释放iirc。

deallocate prepare stmt;

这只会生成1s和0s，但您可以轻松地让它生成Xs和Os。我只是觉得这是一个抽象的简单例子，而不是你的实际用例。

如果这对你有帮助，请告诉我。另外，我也不能百分之百确定你要用它来解决什么样的安全问题。您将创建动态SQL语句并执行它们，因此，如果恶意用户可以操作数据，则需要小心。我认为在datetime字段中没有太大的危险，并且id是一个int，所以也没有太多，但是我也不知道这是否是您的实际用例。