多列的SQL查询取决于另一个表的同一列 [英] SQL queries for multiple columns depends on same column of another table
本文介绍了多列的SQL查询取决于另一个表的同一列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
请参阅以下情形:
问题表:
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屋!
查看全文