提问人：Peter Smith 提问时间：11/16/2023 最后编辑：egleasePeter Smith 更新时间：11/16/2023 访问量：56
列中显示 2 个单独的结果
2 separate results displayed in columns
问：
我是我的 SQL 初学者，我需要知道如何显示行数不同的 3 个查询的结果。他们将查询同一个表。示例表：
Scores
NAME | SCORE
jack | 1
mike | 3
kate | 2
able | 1
jill | 2
seth | 1
3 个 SINGLE SELECT 语句如下所示：
SELECT name as Ones
FROM scores
WHERE score = 1
SELECT name as Twos
FROM scores
WHERE score = 2
SELECT name as Threes
FROM scores
WHERE score = 3
每个查询的结果分别会给出不同的行计数，但我希望它们在列中，如下所示：
Ones | Twos | Threes
______________________________
jack | mike | kate
able | jill |
seth | |
无论间隙中有空格还是空格，我都不会介意。
答：
1赞
PippoZucca
11/16/2023
#1
由于您甚至可以接受空格或空值，因此您可以像这样使用 UNION ALL：
SELECT [name] as Ones, '' as Twos, '' as Threes FROM scores WHERE score = 1
union all
SELECT '' as Ones, [name] as Twos, '' as Threes FROM scores WHERE score = 2
union all
SELECT '' as Ones, '' as Twos, [name] as Threes FROM scores WHERE score = 3
以获得您要求的东西。 您只能将 union all 与具有相同结构的表一起使用，在本例中为 3 列，每列具有相同的名称。
最终结果是
Ones Twos Threes
------------ ------------ ------------
jack
able
seth
kate
jill
mike
评论
0赞
jarlh
11/16/2023
我不确定MySQL是否接受方括号作为分隔符。反正根本不需要划定，不是保留的。（en.wikipedia.org/wiki/List_of_SQL_reserved_wordsname)
1赞
jarlh
11/16/2023
#2
用于每个分数类。row_number()
具有返回总行数的 cnt 派生表。
LEFT JOIN分数类。
select s1.ones, s2.twos, s3.threes
from
(select row_number() over () rn FROM scores) cnt
left join
(SELECT name as ones, row_number() over () rn FROM scores WHERE score = 1) s1
on cnt.rn = s1.rn
left outer join
(SELECT name as twos, row_number() over () rn FROM scores WHERE score = 2) s2
on cnt.rn = s2.rn
left outer join
(SELECT name as threes, row_number() over () rn FROM scores WHERE score = 3) s3
on cnt.rn = s3.rn
where s1.rn is not null or s2.rn is not null or s3.rn is not null
演示：https://dbfiddle.uk/CQiMcs6v
评论
0赞
Peter Smith
11/17/2023
谢谢，这非常有效，正是我需要的结果。
评论
试案例陈述。jacktwosthreesnull