使用sql从多个字段中选择不同的字段 [英] Select distinct from multiple fields using sql

问题描述

我有5列对应于琐事游戏数据库中的答案-对，错1，错2，错3，错4

I have 5 columns corresponding to answers in a trivia game database - right, wrong1, wrong2, wrong3, wrong4

我想返回所有可能的答案而不重复.我希望在不使用临时表的情况下完成此任务.可以使用类似的东西吗?:

I want to return all possible answers without duplicates. I was hoping to accomplish this without using a temp table. Is it possible to use something similar to this?:

select c1, c2, count(*)
from t
group by c1, c2

但是这将返回3列.我想要一列不同的答案.

But this returns 3 columns. I would like one column of distinct answers.

感谢您的时间

推荐答案

这应该为您提供表中所有不同的值.我想您想添加where子句以仅针对特定问题进行选择.但是，此解决方案需要5个子查询，如果您的表很大，则可能会很慢.

This should give you all distinct values from the table. I presume you'd want to add where clauses to select only for a particular question. However, this solution requires 5 subqueries and can be slow if your table is huge.

SELECT DISTINCT(ans) FROM (
    SELECT right AS ans FROM answers
    UNION
    SELECT wrong1 AS ans FROM answers
    UNION
    SELECT wrong2 AS ans FROM answers
    UNION
    SELECT wrong3 AS ans FROM answers
    UNION
    SELECT wrong4 AS ans FROM answers
) AS Temp

这篇关于使用sql从多个字段中选择不同的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！