SQL查询表中的许多关系

问题描述：

表1

id name col1 col2 col3 col4 
----------------------------------- 
1 test 1.1 1.2 1.3  1.4 
2 test2 2.1 2.2 2.3  2.4

表2

id fk_table1 amt type(fk_table3) 
----------------------------------- 
1  1   2  1 
2  1   3  1 
3  1   9  2 
4  2   1  1

，我想查询这样的，我有得到以下结果

id | name | total_type1_amt |total_type2_amt | col1 col2 col3 col4 
----------------------------------------------------------------------- 
1 test  5 (2+3)   9     1.1 1.2 1.3 1.4 
2 test2  1    0     2.1 2.2 2.3 2.4

基本上在结果我想通过table1.id为total_typeX_amt添加列，在table1和table2中会有数百万行，所以基本上寻找优化的方式来做到这一点。

为什么做'table2'的前两行得到他们的'amt'求和，'table2'的第三行将被转换成另一列？为什么'table2'的第四行为'total_type2_amt'导致'0'？很混乱。 – Smutje

非常直的轴心，但你有什么尝试？为什么不认为你当前的解决方案没有优化？ – qxg

所有这些在哪里？你提到该表的FK，它似乎需要创建不同的'类型'... – Gallus

答

SELECT t1.id, 
     t1.name, 
     t2.total_type1_amt, 
     t2.total_type2_amt 
FROM table1 t1 
INNER JOIN 
(
    SELECT fk_table1, 
      SUM(CASE WHEN type = 1 THEN amt END) AS total_type1_amt, 
      SUM(CASE WHEN type = 2 THEN amt END) AS total_type2_amt 
    GROUP BY fk_table1 
) t2 
    ON t1.id = t2.fk_table1

如果你需要这个跑得快，你可以尝试创建一个使用子查询（我叫上面t2）的图，在fk_table1列的索引。假设table1也有id索引，那么联接应该运行得相当快。

答

这不是100％你想要的结果，但你可以尝试像

select fk_table1, type, sum(amt) 
from table1 
inner join table2 on table1.id = table2.fk_table1 
group by fk_table1, type

这应该引起类似

fk_table1 | type | sum 
1   1  5 
1   2  9 
2   1  1

答

尝试DIS获得总计total_type1_amt

select table1.id, table2.name ,(select count(table2.amt) as total_type1_amt where table1.id = table2.fk_table1 from table.1) from table1 
    inner join table2 on table1.id = table2.fk_table1 
    group by table.id

答

SELECT 
    T1.id, 
    T1.name, 
    SUM(CASE T2.type WHEN 1 THEN T2.amt ELSE 0 END) AS total_type1_amt, 
    SUM(CASE T2.type WHEN 2 THEN T2.amt ELSE 0 END) AS total_type2_amt 
FROM @tbl1 T1 
LEFT JOIN @tbl2 T2 ON T1.id=T2.fk_table1 
GROUP BY T1.id,T1.name

输出：

这适用于问题中提到的特定小案例，但不可扩展。当tbl2中有两个以上的TypeID时会发生什么？ – Gallus

答

有至少2种方式：

SELECT t1.id, 
     t1.name, 
     COALESCE(SUM(CASE WHEN [type] = 1 THEN amt END),0) AS total_type1_amt, 
     COALESCE(SUM(CASE WHEN [type] = 2 THEN amt END),0) AS total_type2_amt, 
     col1, 
     col2, 
     col3, 
     col4 
FROM table1 t1 
LEFT JOIN table2 t2 
    ON t1.id = t2.fk_table1 
GROUP BY t1.id, t1.name, col1, col2, col3, col4

另：

SELECT * 
FROM ( 
SELECT t1.id, 
     t1.name, 
     t2.[type], 
     SUM(t2.amt) as sum 
FROM table1 t1 
LEFT JOIN table2 t2 
    ON t1.id = t2.fk_table1 
GROUP BY t1.id, t1.name, t2.[type] 
) as t 
PIVOT (
    MAX(sum) FOR type IN ([1],[2]) 
) as pvt

所以第一种方法，我不得不做所有我想要选择的t1的颜色，在我的情况下，大约有10个这样的颜色，这意味着我需要添加更多的颜色分组？ –

您的意思是10种类型？或者像'id'，'name'等列？如果你有很多'types' - 使用动态SQL，如果你有更多的列到GROUP BY - 只是提到他们两次 - 在SELECT和GROUP BY部分。 – gofr1

我修改了我的问题，为table1添加了更多的颜色，所以在那种情况下，我需要添加很多组右边的组？ –

答

你可以尝试这样的

;WITH cte 
AS (SELECT 
    fk_table1, SUM([1]) total_type1_amt, COALESCE(SUM([2]), 0) total_type2_amt 
FROM #table1 PIVOT (MAX(amt) FOR type IN ([1], [2])) p 
GROUP BY fk_table1) 
SELECT 
    t.id, t.name, c.total_type1_amt, c.total_type2_amt 
FROM #table1 t 
LEFT JOIN cte c 
    ON t.id = c.fk_table1

SQL查询表中的许多关系

相关推荐