多列的SQL查询取决于另一个表的同一列 [英] SQL queries for multiple columns depends on same column of another table

问题描述

请参阅以下情形:

问题表:

See the following scenario:

Tables for the question:

Table : T1
c10  c12
---  ----
e1   A
e2   B
e3   C

Table: T2

c1  c2  c3  c4  c5   C6
--- -- --- ---  ---  ---
1   P  e1  e2   null  X
2   Q  e3  null null  Y
3   R  e2  e1   null  X
4   S  e3  e1   e2    Z


The c3, c4 and c5 columns of T2 table depends on c12 column of T1 table.

I want the following output:


c1  c2  c3  c4  c5
--- -- --- ---  ---
1   P   A   B   null
2   Q   C  null null
3   R   B   A   null
4   S   C   A   B

您能帮我写一个SQL来检索表T1和T2的上述输出吗?

SQL可能类似于:

Can you help me to write an SQL to retrieve the above output from the table T1 and T2?

The SQL may be like:

Select c1, c2, t1.c12 as c3....... from t1, t2 where join condition

但是我没有想到真正的SQL.
因此，请对此提供帮助.

But I am not getting the actual sql in my mind.
So please help me regarding this.

推荐答案

SELECT c1,c2,(SELECT TOP 1 c12 FROM T1 WHERE T1.c10=T2.c3) AS c3,
    (SELECT TOP 1 c12 FROM T1 WHERE T1.c10=T2.c4) AS c4,
    (SELECT TOP 1 c12 FROM T1 WHERE T1.c10=T2.c5) AS c5
FROM T2
-- If multiple values are expected in T1 table say
-- e1,A  e1,M  then ORDER BY clause can be used for greater
-- control on the value returned by sub query
SELECT c1,c2,(SELECT TOP 1 c12 FROM T1 WHERE T1.c10=T2.c3 ORDER BY T1.c12 DESC) AS c3,
    (SELECT TOP 1 c12 FROM T1 WHERE T1.c10=T2.c4 ORDER BY T1.c12) AS c4,
    (SELECT TOP 1 c12 FROM T1 WHERE T1.c10=T2.c5 ORDER BY T1.c10) AS c5
FROM T2

这篇关于多列的SQL查询取决于另一个表的同一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！