我想知道是否有一种规范的方法可以在 SQLite 中将数据从长格式转换为宽格式(该操作通常在关系数据库领域吗?)。我尝试按照 MySQL 的 这个示例 进行操作,但我猜是 SQLite没有相同的 IF 结构...谢谢!
I wonder if there is a canonical way to convert data from long to wide format in SQLite (is that operation usually in the domain of relational databases?). I tried to follow this example for MySQL but I guess SQLite does not have the same IF construct... Thanks!
发布评论
评论(2)
IF
是一个非标准 MySQL 扩展。最好始终使用 CASE,它是标准 SQL,适用于所有兼容的数据库,包括 SQLite 和 MySQL(以及 MSSQL、Oracle、Postgres、Access、Sybase...等等)。下面是如何使用
CASE
执行相同查询的示例:这是使用联接表示相同查询的另一种方法。我认为这可能更有效,但它假设每个组中的每个键值只有一条记录(
CASE
版本也是如此,但如果不是这样,不会导致额外的行,只是更少- 超出预期的结果)。作为记录,以下是我使用的 DDL 和测试数据:
IF
is a non-standard MySQL extension. It's better to always useCASE
which is standard SQL and works in all compliant databases, including SQLite and MySQL (and MSSQL, Oracle, Postgres, Access, Sybase... and on and on).Here's an example of how to do the same query with
CASE
:Here's another way to represent the same query using joins. I think this is probably more efficient, but it assumes there's only one record for each key value within each group (the
CASE
version does too, but will not result in extra rows if that's not true, just less-than-predictable results).And for the record, here's the DDL and test data I was using:
作为上面优秀答案的更新(作为通用解决方案),并引用 介绍了
3.32.0版本中的IIF()
函数:使用接受的答案的 DDL,以下查询:
... 应将 长 格式重塑:
... 为 宽 格式:
这里我们转置 行和列。
As an update to the excellent answer above (being a generalizable solution), and referencing the example cited in the OP, SQLite introduced the
IIF()
function in version 3.32.0:Using the accepted answer's DDL, the following query:
... should reshape the long format:
... to the wide format:
Here we have transposed rows and columns.