怎么写查询..？ [英] how to write query..?

问题描述

 fscode count areatype 
 ASM001  11  EX 
 ASM001  37  HQ 
 ASM001  5  OS 
 ASM002  6  EX 
 ASM002  37  HQ 
 ASM002  6  OS 
 ASM003  9  EX 
 ASM003  40  HQ 
 ASM003  2  OS 
 ASM004  2  EX 
 ASM004  31  HQ 
 ASM004  14  OS 
 ASM005  1  EX 
 ASM005  39  HQ 
 ASM005  8  OS 
 ASM006  51 总部

在此表中，我想选择以下格式的数据请告诉我

代码HQ EX OS 
 ASM001  37   11   5  
 ASM002  37   6   6  
 ASM003  40   9   2  
 ASM004  31   2   14  
 ASM005  39   1   8  
 ASM006  51   0   0  

像这样怎么做..

解决方案

看起来像 pivot ，从这里开始： http://sqlhints.com/2014/03/ 10 /枢轴和 - UNPIVOT-在-SQL服务器/ [<啊ref =http://sqlhints.com/2014/03/10/pivot-and-unpivot-in-sql-server/target =_ blanktitle =New Window> ^ ]

尝试：

  SELECT  FSCode，
 SUM（  CASE   WHEN  AreaType = '  HQ' 那么 [COUNT]  ELSE   0   END ） AS  HQ，
 SUM（  CASE   WHEN  AreaType = '  EX' 那么 [COUNT]  ELSE   0   END ） AS  EX，
 SUM（  CASE   WHEN  AreaType = '  OS' 那么 [COUNT]  ELSE   0   END ） AS  OS 
  FROM  MyTable 
  GROUP   BY  FSCode 

尝试：

  SELECT  * 
  FROM （
  SELECT  fscode 
 ，areatype 
，ISNULL（SUM（[count]）， 0 ） AS  C 
  FROM  MyTable 
  GROUP   BY  fscode 
，areatype 
） AS 数据
 PIVOT（SUM（C） FOR  areatype  IN （
   HQ  
，  EX 
，  OS 
）） AS  [pivot ] 

fscode	count	areatype
ASM001	11	EX
ASM001	37	HQ
ASM001	5	OS
ASM002	6	EX
ASM002	37	HQ
ASM002	6	OS
ASM003	9	EX
ASM003	40	HQ
ASM003	2	OS
ASM004	2	EX
ASM004	31	HQ
ASM004	14	OS
ASM005	1	EX
ASM005	39	HQ
ASM005	8	OS
ASM006	51	HQ

In this table I want to select data like in below format Pls tell me out

code   HQ   EX   OS
ASM001 37   11	  5
ASM002 37   6     6
ASM003 40   9     2
ASM004 31   2    14
ASM005 39   1     8
ASM006 51   0     0

like this how it can be done..

解决方案

Looks like a pivot, start here : http://sqlhints.com/2014/03/10/pivot-and-unpivot-in-sql-server/[^]

Try:

SELECT FSCode,
       SUM(CASE WHEN AreaType = 'HQ' THEN [COUNT]  ELSE 0 END) AS HQ,
       SUM(CASE WHEN AreaType = 'EX' THEN [COUNT]  ELSE 0 END) AS EX,
       SUM(CASE WHEN AreaType = 'OS' THEN [COUNT]  ELSE 0 END) AS OS
       FROM MyTable
GROUP BY FSCode

TRY:

SELECT *
FROM (
    SELECT fscode
        ,areatype
        ,ISNULL(SUM([count]), 0) AS C
    FROM MyTable
    GROUP BY fscode
        ,areatype
    ) AS data
PIVOT(SUM(C) FOR areatype IN (
            "HQ"
            ,"EX"
            ,"OS"
            )) AS [pivot]

这篇关于怎么写查询..？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！