mysql - MySQL从多列中选择但返回一行
问题描述
我想从多行中选择数据,但要在一行中返回结果。这是一些示例数据
baseWord word case
run ran past
run runners plural
talk talked past
talk talkers plural
如果我想选择单词 run 的过去和复数时态,那么我可以做一个联合选择,但如果我说
SELECT baseWord, word, case FROM myTable WHERE baseWord = 'run';
,那么我会得到
baseWord word case
run ran past
run runners plural
但我不想要这个。我想要一个将返回的选择语句
baseWord, word, case, plural
run ran past runners
所以基本上我需要将单词 runners 放在两个 baseWords 相同的复数列中
解决方案
您可以使用条件聚合来做到这一点:
SELECT baseWord,
MAX(CASE WHEN `case` = 'past' THEN word END) word,
'past' `case`,
MAX(CASE WHEN `case` = 'plural' THEN word END) plural
FROM myTable
WHERE baseWord = 'run'
或者对于所有的话:
SELECT baseWord,
MAX(CASE WHEN `case` = 'past' THEN word END) word,
'past' `case`,
MAX(CASE WHEN `case` = 'plural' THEN word END) plural
FROM myTable
GROUP BY baseWord
请参阅演示。
推荐阅读
- apple-musickit - MusicKit API - Playlist Update, Delete, Song Remove
- c# - 将联合表中的值放入 C# 中的类中
- sql-server - 将键值对转换为列
- php - Delete queries(deleting all information about an id) from different database is slow
- r - How to customise dates (years) on the x-axis in R
- groovy - Replace node text with Mule & groovy
- c# - 带括号的 HttpClient 自定义标头 - C#
- c# - 调用 WebApi 时出错
- spring - 为什么我在 jsp 文件中看不到所需的数据
- vb.net - Visual Basic Randomize() 与新随机