sqlite 如何编写查询以从另一个表输出列

41ik7eoe 于 2023-03-03 发布在 SQLite

关注(0)|答案(2)|浏览(138)

I have three regular table.
Table Author
| AuthorID | FirstName | LastName |
| ------------ | ------------ | ------------ |
| 1 | Alex | Popov |
| 2 | Serg | Smit |
| 3 | John | Paulse |
Table NameStatie
| NameStatieID | NameStatie | URLStatie |
| ------------ | ------------ | ------------ |
| 1 | POPULATION OF TRNA-DERIVED | /item001 |
| 2 | AGENT-BASED SIMULATION MODEL | /item002 |
| 3 | CHAGAS' DISEASE DIAGNOSIS: | /item003 |
Table KeyWord
| KeyWordID | NameKeyWord |
| ------------ | ------------ |
| 1 | VIRTUAL LEARNING ENVIRONMENT |
| 2 | EDUCATIONAL OBJECTIVES |
| 3 | ACADEMIC PROGRESS EVALUATION |
I have a linked table by id Table Statie
| StatieID | NameStatieID | AuthorID |
| ------------ | ------------ | ------------ |
| 1 | 1 | 1 |
| 2 | 1 | 3 |
| 3 | 2 | 2 |
The problem is that if I add a column with KeyWordID to the Statie table, the number of rows will increase several times according to the following formula: Row = a * k where a is Author, k are keywords.
I decided to create another intermediate table to avoid unnecessary padding.
Table NameStatieAndKeyWord
| NameStatieAndKeyWordID | NameStatieID | KeyWordID |
| ------------ | ------------ | ------------ |
| 1 | 1 | 1 |
| 2 | 1 | 3 |
| 3 | 2 | 2 |
Question. How to generate an sql query to get this result?
| NameStatie | KeyWords | Author |
| ------------ | ------------ | ------------ |
| POPULATION OF TRNA-DERIVED | VIRTUAL LEARNING ENVIRONMENT, ACADEMIC PROGRESS EVALUATION | Alex Popov, John Paulse |
P.S. Sorry for my English, I'm from another country and use a translator. I will be glad if you criticize me. I just started learning
I couldn't do it in any way, errors were constantly being issued, but I have such a request that works

SELECT NameStatie, group_concat(LastName || " " || FirstName || "", ", ") as "Authors"
FROM Statie
JOIN NameStatie ON Statie.NameStatieID = NameStatie.NameStatieID
JOIN Author ON Statie.AuthorID = Author.AuthorID
GROUP by NameStatie.NameStatieID

NameStatie	Authors
POPULATION OF TRNA-DERIVED	Alex Popov, John Paulse

sqlite

来源：https://stackoverflow.com/questions/75568890/how-to-write-a-query-to-output-columns-from-another-table

2条答案

按热度按时间

wd2eg0qa1#

诀窍在于如何使用DISTINCT值执行GROUP_CONCAT，如SQLite论坛的以下主题所讨论：https://sqlite.org/forum/info/221c2926f5e6f155.
质询：

SELECT
  NameStatie,
  REPLACE(GROUP_CONCAT(DISTINCT NameKeyWord), ',', ', ') AS KeyWords,
  REPLACE(GROUP_CONCAT(DISTINCT Author), ',', ', ') AS Authors
FROM (
  SELECT
    NameStatie.NameStatie,
    KeyWord.NameKeyWord,
    FirstName || " " || LastName AS Author
  FROM Statie
  JOIN NameStatie ON Statie.NameStatieID = NameStatie.NameStatieID
  JOIN Author ON Author.AuthorID = Statie.AuthorID
  JOIN NameStatieAndKeyWord ON Statie.NameStatieID = NameStatieAndKeyWord.NameStatieID
  JOIN KeyWord ON NameStatieAndKeyWord.KeyWordID = KeyWord.KeyWordID
  WHERE NameStatie.NameStatieID = 1
) GROUP BY NameStatie;

输出：
| 名称状态|关键词|作者|
| - ------|- ------|- ------|
| TRNA衍生物的群体|虚拟学习环境与学业评价|亚历克斯·波波夫约翰·保尔斯|

赞(0）回复(0）举报 2023-03-03

nmpmafwu2#

它应该如下所示：

SELECT ns.NameStatie
,x.Authors
,y.Keywords
FROM NameStatie ns
left outer join(select STRING_AGG(CONCAT(FirstName,' ',LastName), ', ') AS  Authors,s.NameStatieID from Statie s 
inner join Author a on a.AuthorID = s.AuthorID GROUP by s.NameStatieID) as x on x.NameStatieID=ns.NameStatieID
left outer join(select STRING_AGG(NameKeyWord, ', ') AS  Keywords,nk.NameStatieID from NameStatieAndKeyWord nk
inner join KeyWord k on k.KeyWordID=nk.KeyWordID GROUP by nk.NameStatieID) as y on y.NameStatieID=ns.NameStatieID
GROUP by ns.NameStatieID,ns.NameStatie,x.Authors,y.Keywords
order by ns.NameStatieID

注意：当且仅当您使用SQL Server 2017或更高版本时，才可以使用内置SQL Server函数STRING_AGG创建逗号分隔列表。

赞(0）回复(0）举报 2023-03-03

我来回答

sqlite 如何编写查询以从另一个表输出列

2条答案

相关问题

热门标签

最新问答