基本 sql ：在一个查询中多次选择同一列，当每次出现都依赖于不同的 where 子句时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4097266/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
basic sql : selecting the same column multiple times in one query, when each occurrence is dependent on different where clause
提问by NimChimpsky
What is the best way to to perform this query. I have the following table
执行此查询的最佳方法是什么。我有下表
mytable with columns
带列的 mytable
x y
1 a
2 b
3 c
and I would like to (in pseudo sql)
我想（在伪 sql 中）
select x as x1 ,x as x2, x as x3 from mytable where ????
when
什么时候
x1 is x where y=a
x2 is x where y=b
x3 is x where y=c
so I would like as a result
所以我希望结果
1, 2, 3
I am currently using cte's and and a very large dataset, I am trying to reduce the query time, is it always necessary to have 3 table scans ?
我目前正在使用 cte 和一个非常大的数据集，我正在尝试减少查询时间，是否总是需要进行 3 次表扫描？
回答by Silver Light
You should use 3 queries. It will be a lot faster with proper indexing when self joins. Additionally it will be more readable.
您应该使用 3 个查询。当自连接时，使用适当的索引会快很多。此外，它将更具可读性。
If you would like one query call, it might be this :)
如果你想要一个查询电话，它可能是这样的:)
SELECT
(SELECT x FROM table WHERE y=1) AS x1,
(SELECT x FROM table WHERE y=2) AS x2,
(SELECT x FROM table WHERE y=3) AS x3
回答by aqm
I would of done this :
我会这样做：
SELECT
tableRowA.x as x1
tableRowB.x as x2
tableRowC.x as x3
FROM
table as tableRowA,
table as tableRowB,
table as tableRowC
WHERE
tableRowA.y = 1
tableRowB.y = 2
tableRowC.y = 3
Bit easier to understand, and pull out more info if you need multiple columns from each row
更容易理解，如果您需要每行中的多列，请提取更多信息
回答by aqm
In the example given, there are only 3 rows of input and one row of output. I assume that there is going to be at least one other column involved, such that input data:
在给出的示例中，只有 3 行输入和 1 行输出。我假设至少会涉及其他一列，以便输入数据：
w x y
---------
w1 1 a
w1 2 b
w1 3 c
w2 4 a
w2 5 b
w2 6 c
.
.
.
is to become output:
是成为输出：
w x1 x2 x3
-----------
w1 1 2 3
w2 4 5 6
.
.
.
This can be done in a single pass using a query like:
这可以使用如下查询一次性完成：
select w,
max(case when y = 'a' then x end) x1,
max(case when y = 'b' then x end) x2,
max(case when y = 'c' then x end) x3
from datatable
where y in ('a','b','c')
group by w
回答by PatrickP61
From your question it appears you would like the last three instances of a condition, or tie three different conditions together. Would the following example satisfy your question:
从您的问题来看，您似乎想要一个条件的最后三个实例，或者将三个不同的条件联系在一起。以下示例是否可以满足您的问题：
mytable:
(unique keys 1..n) (col1)
student-id | course-id | grade
s1 gen101 g1
s1 cmp202 g2
s1 psy303 g3
s1 c4 g4
s2 c1 g5
Lets say we only want the students which have three specific courses (gen101, cmp202, and psy303) and show those grades ignoring anyone else.
假设我们只想要拥有三门特定课程（gen101、cmp202 和 psy303）的学生，并显示这些成绩而忽略其他任何人。
select gen.student-id as student-id
, gen.grade as gen101-gr
, cmp.grade as cmp202-gr
, psy.grade as psy303-gr
from mytable gen
, mytable cmp
, mytable psy
where gen.course-id = 'gen101'
and gen.student-id = cmp.student-id
and cmp.course-id = 'cmp202'
and cmp.studnet-id = psy.student-id
and psy.course-id = 'psy303'
This should give one row:
这应该给一行：
student-id gen101-gr cmp202-gr psy303-gr
s1 g1 g2 g3
Hope that gives you enough to work on.
希望能给你足够的工作。
回答by Silver Light
Another solution:
另一种解决方案：
SELECT x, y FROM table WHERE y IN ('a', 'b')
You will have a result set:
您将获得一个结果集：
x | y
-----
1 | a
2 | b
This result set can then be used in an application to get desired result.
然后可以在应用程序中使用此结果集以获得所需的结果。
回答by SilverSkin
My recommendation would be to select the results ordered or grouped by column y and the use that information to split the resultset into several lists for an application to process. If you want to do this only in the database I'm afraid multiple table scans (or joins) are necessary.
我的建议是选择按 y 列排序或分组的结果，并使用该信息将结果集拆分为多个列表以供应用程序处理。如果您只想在数据库中执行此操作，恐怕需要多次表扫描（或连接）。
Another fix is to migrate the information in column y to another table (with a foreign key reference), to be able to join more effectively to that.
另一个解决方法是将 y 列中的信息迁移到另一个表（带有外键引用），以便能够更有效地加入该表。
回答by dotariel
SELECT Case When y = 1 Then x1 When y = 2 Then x2 Else x3 End FROM mytable
回答by Fabien TheSolution
MySQL 5.5.32 Schema Setup:
MySQL 5.5.32 架构设置：
CREATE TABLE Table1
(`x` int, `y` varchar(1))
;
INSERT INTO Table1
(`x`, `y`)
VALUES
(1, 'a'),
(2, 'b'),
(3, 'c')
;
CREATE TABLE mytable
(`x` int, `y` varchar(1))
;
INSERT INTO mytable
(`x`, `y`)
VALUES
(1, 'a'),
(2, 'b'),
(3, 'c')
;
Query 1:
查询 1：
SELECT x1.x as x1, x2.x as x2, x3.x as x3
FROM mytable x1
INNER JOIN mytable x2 ON x2.y='b'
INNER JOIN mytable x3 ON x3.y='c'
WHERE x1.y='a'
结果：
| X1 | X2 | X3 |
|----|----|----|
| 1 | 2 | 3 |
