如何在SQLite中进行透视或以宽格式选择以长格式存储的表? [英] How to pivot in SQLite or i.e. select in wide format a table stored in long format?
问题描述
我想得到一个表格,用于以长格式存储学生数据以及他们在一次查询中获得的所有科目的分数.
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
表格:student info
实际结构:
## 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:
表格:Student Info
## 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.
这篇关于如何在SQLite中进行透视或以宽格式选择以长格式存储的表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!