如何在SQLite中透视或者以宽格式选择以长格式存储的表？
如何在SQLite中透视或者以宽格式选择以长格式存储的表？
提问于 2009-08-06 05:05:12
我想要一个表，它以long format格式存储学生数据，并在一个查询中存储他们所有科目的分数。
这是我的表结构：
表：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我希望结果集具有以下wide format结构作为透视的结果：
表：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中实现这一点？
回答 5
Stack Overflow用户
回答已采纳
发布于 2009-08-06 05:14:16
首先，您需要将当前表更改为临时表：
alter table student_info rename to student_name然后，您需要重新创建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现在，删除您的临时表：
drop table student_temp这就是您可以快速更新表的方法。
SQLite缺少pivot函数，所以你能做的最好的事情就是硬编码一些左连接。left join将匹配其联接条件中的任何行，并为第一个表或左表中不满足第二个表的联接条件的任何行返回null。
Stack Overflow用户
发布于 2011-11-24 10:52:18
由于作者没有给出SQL来创建模式，所以这里是为任何想要尝试@Eric解决方案的人准备的。
create table markdetails (studid, subjectid, marks);
create table student_info (studid, name);
insert into markdetails values('A1', 3, 50);
insert into markdetails values('A1', 4, 60);
insert into markdetails values('A1', 5, 70);
insert into markdetails values('B1', 3, 60);
insert into markdetails values('B1', 4, 80);
insert into markdetails values('C1', 5, 95);
insert into student_info values('A1', 'Raam');
insert into student_info values('B1', 'Vivek');
insert into student_info values('C1', 'Alex');这是一个使用case和group by的替代解决方案。
select
si.studid,
si.name,
sum(case when md.subjectid = 3 then md.marks end) subjectid_3,
sum(case when md.subjectid = 4 then md.marks end) subjectid_4,
sum(case when md.subjectid = 5 then md.marks end) subjectid_5
from student_info si
join markdetails md on
md.studid = si.studid
group by si.studid, si.name
;为了进行比较，下面是@Eric的解决方案中相同的select语句：
select
u.stuid,
u.name,
s3.marks as subjectid_3,
s4.marks as subjectid_4,
s5.marks as subjectid_5
from
student_info 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
;当有大量数据时，哪一个会表现得更好，这将是一件有趣的事情。
Stack Overflow用户
发布于 2014-02-25 05:52:49
很棒的附录！帮助我以较低的工作量和系统负载解决了类似的问题。我使用Raspberry Pi获取1wire-interface DS18B20温度传感器数据，如下所示：
CREATE TABLE temps (Timestamp DATETIME, sensorID TEXT, temperature NUMERIC);示例：
sqlite> .headers on
sqlite> .mode column
sqlite> select * from temps where timestamp > '2014-02-24 22:00:00';
Timestamp sensorID temperature
------------------- --------------- -----------
2014-02-24 22:00:02 28-0000055f3f10 19.937
2014-02-24 22:00:03 28-0000055f0378 19.687
2014-02-24 22:00:04 28-0000055eb504 19.937
2014-02-24 22:00:05 28-0000055f92f2 19.937
2014-02-24 22:00:06 28-0000055eef29 19.812
2014-02-24 22:00:07 28-0000055f7619 19.625
2014-02-24 22:00:08 28-0000055edf01 19.687
2014-02-24 22:00:09 28-0000055effda 19.812
2014-02-24 22:00:09 28-0000055e5ef2 19.875
2014-02-24 22:00:10 28-0000055f1b83 19.812
2014-02-24 22:10:03 28-0000055f3f10 19.937
2014-02-24 22:10:04 28-0000055f0378 19.75
2014-02-24 22:10:04 28-0000055eb504 19.937
2014-02-24 22:10:05 28-0000055f92f2 19.937使用SUBSTR()命令，我将时间戳“规范化”为10分钟周期。使用JOIN，使用查找表‘SensorName’将传感器更改为传感器
CREATE VIEW [TempsSlot10min] AS
SELECT SUBSTR(datetime(timestamp),1,15)||'0:00' AS TimeSlot,
SensorName,
temperature FROM
temps JOIN sensors USING (sensorID, sensorID);示例：
sqlite> select * from TempsSlot10min where timeslot >= '2014-02-24 22:00:00';
TimeSlot SensorName temperature
------------------- ---------- -----------
2014-02-24 22:00:00 T1 19.937
2014-02-24 22:00:00 T2 19.687
2014-02-24 22:00:00 T3 19.937
2014-02-24 22:00:00 T4 19.937
2014-02-24 22:00:00 T5 19.812
2014-02-24 22:00:00 T6 19.625
2014-02-24 22:00:00 T10 19.687
2014-02-24 22:00:00 T9 19.812
2014-02-24 22:00:00 T8 19.875
2014-02-24 22:00:00 T7 19.812
2014-02-24 22:10:00 T1 19.937
2014-02-24 22:10:00 T2 19.75
2014-02-24 22:10:00 T3 19.937
2014-02-24 22:10:00 T4 19.937
2014-02-24 22:10:00 T5 19.875现在，魔术发生在上面提到的案例指令中。
CREATE VIEW [PivotTemps10min] AS
SELECT TimeSlot,
AVG(CASE WHEN sensorName = 'T1' THEN temperature END) AS T1,
AVG(CASE WHEN sensorName = 'T2' THEN temperature END) AS T2,
...
AVG(CASE WHEN sensorName = 'T10' THEN temperature END) AS T10
FROM TempsSlot10min
GROUP BY TimeSlot;示例：
select * from PivotTemps10min where timeslot >= '2014-02-24 22:00:00';
TimeSlot T1 T2 T10
------------------- ---------- ---------- ... ----------
2014-02-24 22:00:00 19.937 19.687 19.687
2014-02-24 22:10:00 19.937 19.75 19.687
2014-02-24 22:20:00 19.937 19.75 19.687
2014-02-24 22:30:00 20.125 19.937 19.937
2014-02-24 22:40:00 20.187 20.0 19.937
2014-02-24 22:50:00 20.25 20.062 20.062
2014-02-24 23:00:00 20.25 20.062 20.062这里剩下的唯一问题是sensorName 'T1‘...'T10‘现在被硬编码到视图PivotTemps10min中，而不是从查找表中获取。
尽管如此，非常感谢你在这篇文章中的回答！
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/1237068
复制相关文章
相似问题
腾讯云开发者
