SQLite:从多个表和合并列中选择语句 [英] SQLite : Select statement from multiple table and merging column

问题描述

我有另一种情况，我想合并一些列，每个列都有相同的父 ID，但每个组的子 ID 不同，

i have another case , i want to merging some column that have a same parent id but different child id each every group,

这是我的table1;

here is my table1;

+---------+----------+
|id_table1| value    | 
+---------+----------+
|   1     | value1   | 
|   2     | value2   | 
|   3     | value3   | 
|   4     | value4   | 
|   5     | value5   |  
|   6     | value6   | 
|   7     | value7   | 
|   8     | value8   |  
|   9     | value9   | 
+---------+----------+ 

表 2:

+---------+----------+
|id_table1| value    | 
+---------+----------+
|   P1    | valueP1  | 
|   P2    | valueP2  | 
+---------+----------+ 

这是我的关系表:

+---------+----------+---------+
|id_boss  | id_child |  answ   |  
+---------+----------+---------+
|   1     |     2    |    T    |
|   1     |     6    |    F    |
|   2     |     P1   |    T    |
|   2     |     4    |    F    |
|   6     |     P2   |    T    | 
|   6     |     8    |    F    |
+---------+----------+ --------+

我正在寻找一种组合列 id_child 的方法

and i'm looking a way to combine column id_child

结果是这样的:

:: 已编辑 ::

+---------+-----------+-----------+
|id_boss  | child_T   | child_F   |     
+---------+-----------+-----------+
| value1  |  value2   |  value6   |
| value2  |  valueP1  |  value4   |
| value6  |  valueP2  |  value8   |
+---------+-----------+-----------+ 

id_child 第一行组 id_boss 在 child_T
第二行组 id_boss 在 child_F

id_child first row group id_boss is in child_T
and second row group id_boss is in child_F

推荐答案

这是您想要的吗?

SELECT (SELECT value FROM table1 WHERE table1.id_table1 = r1.id_boss) AS id_boss,
       COALESCE( (SELECT value FROM table1 WHERE table1.id_table1 = r1.id_child),
                 (SELECT value FROM table2 WHERE table2.id_table2 = r1.id_child)
               ) AS child_T,
       COALESCE( (SELECT value FROM table1 WHERE table1.id_table1 = r2.id_child),
                 (SELECT value FROM table2 WHERE table2.id_table2 = r2.id_child)
               ) AS child_F
  FROM r_table AS r1 JOIN r_table AS r2
    ON r1.id_boss = r2.id_boss AND r1.id_child <> r2.id_child AND r1.answ = 'T'
;

测试此处

这篇关于SQLite:从多个表和合并列中选择语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！