需要 sql 或存储过程根据标志获取数据?

Question

Ex- 我有表格Name_Type如下-

+--------------+----------+
| Name_Type_Id |  Code    |
+--------------+----------+
|            1 |   AN     |
|            2 |   BN     |
|            3 |   CN     |
|            4 |   DN     |
|            5 |   EN     |
|            6 |   FN     |
+--------------+----------+

第二张表Table_A如下 -

+-------+---------+--------------+------------------+
|  T_ID | T_Value | Name_Type_Id | Name_Type_Value  |
+-------+---------+--------------+------------------+
|  T1   |  A      |            1 |               1  |
|  T1   |  B      |            3 |               0  |
|  T2   |  A      |            2 |               1  |
|  T2   |  B      |            5 |               1  |
|  T2   |  C      |            6 |               1  |
|  T3   |  A      |            1 |               0  |
|  T3   |  B      |            6 |               1  |
|  T4   |  A      |            1 |               0  |
|  T5   |  A      |            2 |               1  |
+-------+---------+--------------+------------------+

需要结果集如下 -

+-------+---------+------+------+-------+-----+
| T_ID  | T_Value | AN   |  BN  |   EN  |  FN |
+-------+---------+------+------+-------+-----+
|    T1 |  A      |  1   |   0  |   0   |   0 |
|    T1 |  B      |  0   |   0  |   0   |   0 |
|    T2 |  A      |  0   |   1  |   0   |   0 |
|    T2 |  B      |  0   |   0  |   1   |   0 |
|    T2 |  C      |  0   |   0  |   0   |   1 |
|    T3 |  A      |  0   |   0  |   0   |   0 |
|    T3 |  B      |  0   |   0  |   0   |   1 |
|    T4 |  A      |  0   |   0  |   0   |   0 |
|    T5 |  A      |  0   |   1  |   0   |   0 |
+-------+---------+------+------+-------+-----+

德斯克 -在第二张表中，Table_A T_ID和T_Value是唯一的。 我只需要name_type_code作为结果中的列名，table_A中存在值"1"。 前任。就像name_type_code"DN"一样，任何T_ID都没有价值 CN 也是如此，就像我们有值"0"，所以我们不想要。

Answer 1

您可能更喜欢使用decode作为：

select a.t_id, a.T_Value,
decode(n.code,'AN',a.value,0) as "AN",
decode(n.code,'BN',a.value,0) as "BN",
decode(n.code,'EN',a.value,0) as "EN",
decode(n.code,'FN',a.value,0) as "FN"
from Table_A a
left join Name_Type n 
on ( n.name_type_id = a.name_type_id )    
group by a.t_id, a.T_Value, a.value, n.code  
order by a.t_id, a.T_Value;
T_ID  T_VALUE AN  BN  EN  FN
T1       A   0   0   0   0
T1       B   1   0   0   0
T2       A   0   1   0   0
T2       B   0   0   1   0
T2       C   0   0   0   1
T3       A   0   0   0   0
T3       B   0   0   0   1
T4       A   0   0   0   0
T5       A   0   1   0   0