如何在 SQLite 中透视或即选择宽格式存储长格式的表?

2021-12-08 00:00:00 pivot sql sqlite

我想要一个表格，以长格式和他们在一次查询中获得的所有科目的分数.

I'd like to get a table which stores students data in long format and the marks they receive for all of their subjects in one query.

这是我的表结构:

表格:markdetails

## studid ## ## subjectid ##  ## marks ##
     A1            3                50
     A1            4                60
     A1            5                70
     B1            3                60
     B1            4                80
     C1            5                95

表格:学生信息

实际结构:

## studid ##  ## name ##
      A1          Raam
      B1          Vivek
      c1          Alex

我希望结果集具有以下宽格式结构作为结果透视:

I want the result set to have the following wide format structure as result of the pivotization:

表格:学生信息

## studid ## ## name## ## subjectid_3 ## ## subjectid_4 ## ## subjectid_5 ##
      A1        Raam        50                60                 70
      B1        Vivek       60                80                null
      c1        Alex       null              null                95

如何在 SQLite 中完成此操作?

How can I accomplish this in SQLite?

推荐答案

首先需要将当前表改为临时表:

First you need to change the current table to a temp table:

alter table student_info rename to student_name

然后，您需要重新创建student_info:

Then, you'll want to recreate student_info:

create table student_info add column (
    stuid VARCHAR(5) PRIMARY KEY,
    name VARCHAR(255),
    subjectid_3 INTEGER,
    subjectid_4 INTEGER,
    subjectid_5 INTEGER
)

然后，填充student_info:

insert into student_info
select
    u.stuid,
    u.name,
    s3.marks as subjectid_3,
    s4.marks as subjectid_4,
    s5.marks as subjectid_5
from
    student_temp u
    left outer join markdetails s3 on
        u.stuid = s3.stuid
        and s3.subjectid = 3
    left outer join markdetails s4 on
        u.stuid = s4.stuid
        and s4.subjectid = 4
    left outer join markdetails s5 on
        u.stuid = s5.stuid
        and s5.subjectid = 5

现在，只需删除您的临时表:

Now, just drop your temp table:

drop table student_temp

这就是您可以快速更新表格的方法.

And that's how you can quickly update your table.

SQLite 缺少 pivot 函数，所以你能做的最好的事情就是硬编码一些左连接.left join 将匹配其连接条件中的任何行，并为第一个或左表中不满足连接条件的任何行返回 null第二张桌子.

SQLite lacks a pivot function, so the best you can do is hard-code some left joins. A left join will bring match any rows in its join conditions and return null for any rows from the first, or left, table that don't meet the join conditions for the second table.

相关文章