SQL 如何在 SQLite 中透视或以宽格式选择以长格式存储的表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1237068/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to pivot in SQLite or i.e. select in wide format a table stored in long format?
提问by arams
I'd like to get a table which stores students data in long formatand the marks they receive for all of their subjects in one query.
我想得到一个表格，它以长格式存储学生数据以及他们在一个查询中为所有科目获得的分数。
This is my table structure:
这是我的表结构：
Table: markdetails
桌子： markdetails
## studid ## ## subjectid ## ## marks ##
A1 3 50
A1 4 60
A1 5 70
B1 3 60
B1 4 80
C1 5 95
Table: student info
桌子： student info
Actual Structure:
实际结构：
## studid ## ## name ##
A1 Raam
B1 Vivek
c1 Alex
I want the result set to have the following wide formatstructure as result of the pivotization:
作为数据透视的结果，我希望结果集具有以下宽格式结构：
Table: Student Info
桌子： 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
How can I accomplish this in SQLite?
如何在 SQLite 中完成此操作？
采纳答案by Eric
First you need to change the current table to a temp table:
首先，您需要将当前表更改为临时表：
alter table student_info rename to student_name
Then, you'll want to recreate student_info:
然后，您需要重新创建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
)
Then, populate student_info:
然后，填充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 lacks a pivotfunction, so the best you can do is hard-code some left joins. A left joinwill bring match any rows in its join conditions and return nullfor any rows from the first, or left, table that don't meet the join conditions for the second table.
SQLite 缺少一个pivot函数，所以你能做的最好的事情就是硬编码一些左连接。Aleft join将匹配其连接条件中的任何行，并返回null第一个或左侧表中不满足第二个表的连接条件的任何行。
回答by haridsv
Since the author was not kind enough to give the SQL to create the schema, here it is for anyone who wants to try the solution from @Eric.
由于作者不够友善，无法提供 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');
Here is an alternative solution using casewith group by.
这是使用casewith的替代解决方案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
;
For comparison, here is the same select statement from @Eric's solution:
为了进行比较，这里是@Eric 解决方案中的相同选择语句：
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
;
It will be interesting to see which one would perform better when there is a lot of data.
当有大量数据时，看看哪个表现更好会很有趣。
回答by McPeppr
great appendix! helped me to solve a similar problem with low effort and system load. I am using a Raspberry Pi to obtain 1wire-interface DS18B20 temperature sensor data as follows:
很棒的附录！帮助我以较低的工作量和系统负载解决了类似的问题。我正在使用 Raspberry Pi 获取 1wire-interface DS18B20 温度传感器数据，如下所示：
CREATE TABLE temps (Timestamp DATETIME, sensorID TEXT, temperature NUMERIC);
example:
例子：
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
using the SUBSTR() command I am "normalizing" the Timestamps to 10 minutes periods. With JOIN the sensorID is changed into a SensorName using the lookup-table 'sensors'
使用 SUBSTR() 命令，我将时间戳“标准化”为 10 分钟。通过 JOIN，使用查找表“sensors”将 sensorID 更改为 SensorName
CREATE VIEW [TempsSlot10min] AS
SELECT SUBSTR(datetime(timestamp),1,15)||'0:00' AS TimeSlot,
SensorName,
temperature FROM
temps JOIN sensors USING (sensorID, sensorID);
example:
例子：
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
now, the magic happens with the above mentioned CASE instruction.
现在，神奇发生在上面提到的 CASE 指令中。
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;
example:
例子：
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
The only problem remaining here is that the sensorName 'T1' ... 'T10' is now hardcoded into the VIEW [PivotTemps10min] and not taken from the lookup table.
这里剩下的唯一问题是 sensorName 'T1' ... 'T10' 现在被硬编码到 VIEW [PivotTemps10min] 中，而不是从查找表中获取。
Nonetheless, thank you very much for the answers in this thead!
尽管如此，非常感谢您在这篇文章中的回答！
回答by Jeff Heon
If you have a simpler requirement of bundling together the children in the same field, group_concat is your friend.
如果您有将同一领域的孩子捆绑在一起的更简单的要求，group_concat 是您的朋友。
Huge thanks to Simon Slaver from this thread: http://sqlite.1065341.n5.nabble.com/Howto-pivot-in-SQLite-tp26766p26771.html
非常感谢来自此线程的 Simon Slaver：http://sqlite.1065341.n5.nabble.com/Howto-pivot-in-SQLite-tp26766p26771.html
回答by Radek Daniluk
Thanks to @pospec4444's linkhere is modified version of @haridsv's awesome answer. It uses filterclause to be little more concise
感谢@pospec4444 的链接，这里是@haridsv 很棒的答案的修改版本。它使用filter子句更简洁
select
si.studid,
si.name,
sum(md.marks) filter(where md.subjectid = 3) subjectid_3,
sum(md.marks) filter(where md.subjectid = 4) subjectid_4,
sum(md.marks) filter(where md.subjectid = 5) subjectid_5
from student_info si
join markdetails md on
md.studid = si.studid
group by si.studid, si.name
;
