gpt4 book ai didi

sql - 如何从一个JOIN返回多个记录作为列?

转载 作者:行者123 更新时间:2023-12-03 19:00:32 25 4
gpt4 key购买 nike

我有下面的SQL语句(SQLite 3.0),它连接两个表,结果是我每天有一条记录,其中有一天的总数,但是将其连接到语言表,它每天就给我5条记录,其中一条每种语言。

SELECT 
re.day,
re.flashcardsAdded,
re.flashcardsTested,
re.flashcardsLearned,
re.totalPoints AS totalForDay,
la.language,
la.total AS totalForLanguage

FROM dpod_site_reportDays AS re
JOIN dpod_site_reportDayLanguageTotals AS la ON re.day=la.day
ORDER BY re.day DESC


但是,我每天只需要一个记录,而不是例如“法语”是数据,我希望它是一列名称,该语言的总数作为该列的数据。

如何更改SQL语句,以便在具有适当字段名称的同一记录中获得语言总数?例如。:

day | flashcardsAdded | flashcardsTested | flashcardsLearned | totalForDay | french | german | italian | russian | german




表格如下:

CREATE TABLE dpod_site_reportDayLanguageTotals(
day VARCHAR(50),
language VARCHAR(1024),
total INT(12),
extras VARCHAR(1024),
idCode VARCHAR(1024),
whenCreated VARCHAR(50),
whenChanged VARCHAR(50),
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE)

CREATE TABLE dpod_site_reportDays(
day VARCHAR(50),
flashcardsAdded INT(12),
flashcardsTested INT(12),
flashcardsLearned INT(12),
journalPoints INT(12),
readingPoints INT(12),
correctionPoints INT(12),
extrasData TEXT,
totalPoints INT(12),
extras VARCHAR(1024),
idCode VARCHAR(1024),
whenCreated VARCHAR(50),
whenChanged VARCHAR(50),
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE)

最佳答案

SELECT  
re.day,
re.flashcardsAdded,
re.flashcardsTested,
re.flashcardsLearned,
re.totalPoints AS totalForDay,
Sum(CASE la.language WHEN 'french' THEN la.total ELSE 0 END) AS french,
Sum(CASE la.language WHEN 'german' THEN la.total ELSE 0 END) AS german,
Sum(CASE la.language WHEN 'italian' THEN la.total ELSE 0 END) AS italian,
Sum(CASE la.language WHEN 'russian' THEN la.total ELSE 0 END) AS russian


FROM dpod_site_reportDays AS re
JOIN dpod_site_reportDayLanguageTotals AS la ON re.day=la.day
GROUP BY re.day,
re.flashcardsAdded,
re.flashcardsTested,
re.flashcardsLearned,
re.totalPoints
ORDER BY re.day DESC

关于sql - 如何从一个JOIN返回多个记录作为列?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/11737820/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com