如何在没有数据的情况下计算子查询使用组？

I need to create query that has below result.
I add my table for example for better understanding.

我需要创建具有以下结果的查询。我添加我的表格以便更好地理解。

QTable:
           QID Name
           -------
            1    x  
            1    y   
            2    z     
FTable:
         QID FID 
         -------
          1    1  
          1    2   
          2    3   


Inner join with :


           FID Type
           -------
            1    1       
            1    2        
            1    3        
            1    4        
            2    2        
            2    3        
            2    3 
            3    1        
            3    3        
            3    3   

I need to result like this:

我需要这样结果:

 result:
     QID FID-Count Type(1)-count Type(2)-count Type(3)-count Type(4)-count
     -----------------------------------------------------------------
      1     2           1             2              3             1
      2     1           1             0              2             0

Thank u for help.

谢谢你的帮助。

1 个解决方案

#1

Ususally you can emulate a pivot with GROUP BY and SUM+CASE:

通常,您可以使用GROUP BY和SUM + CASE模拟一个数据透视表:

SELECT 
   T1.QID,
   COUNT(DISTINCT T1.FID) as FIDCOUNT,
   SUM(CASE WHEN Type=1 THEN 1 ELSE 0 END) as Type1Cnt,
   SUM(CASE WHEN Type=2 THEN 1 ELSE 0 END) as Type2Cnt,
   SUM(CASE WHEN Type=3 THEN 1 ELSE 0 END) as Type3Cnt,
   SUM(CASE WHEN Type=4 THEN 1 ELSE 0 END) as Type4Cnt


FROM QFTable as T1
JOIN T2 ON 
   T1.FID=T2.FID
GROUP BY T1.QID
ORDER BY QID

SQLFiddle demo