mysql、sqlServer 实现单元格分组、计算分组后count和sum行列倒置

表结构：

CREATE TABLE [dbo].[CellMerge_Test](
    [Flight_No] [nvarchar](50) NULL,
    [Mawb_No] [nvarchar](50) NULL,
    [Hawb_No] [nvarchar](50) NULL,
    [Volume] [decimal](18, 2) NULL,
    [Weight] [int] NULL
) ON [PRIMARY]
GO

表数据：

SELECT * FROM CellMerge_Test

INSERT INTO CellMerge_Test(Flight_No, Mawb_No, Hawb_No, Volume, Weight)VALUES
('F001','M001','H001',2.5,3),
('F001','M001','H002',2.5,3),
('F001','M002','H003',2.5,3),
('F001','M002','H004',2.5,3),
('F002','M003','H005',2.5,3),
('F002','M004','H006',2.5,3),
('F003','M005','H007',2.5,3),
('F003','M005','H008',2.5,3)

SqlServer语句：

select (case dType when 1 then Flight_No else CONVERT(varchar(20),CountFlight_No) end ) Flight_No
    ,(case dType when 1 then Mawb_No else CONVERT(varchar(20),CountFlight_No) end ) Mawb_No
    ,Hawb_No
    ,Volume
    ,Weight
    from (
    select *,ROW_NUMBER() over(order by Flight_No,Mawb_No,dType) num  from (
    SELECT Flight_No, Mawb_No, Hawb_No, Volume, Weight,1 'dType',0 CountFlight_No,0 CountMawb_No FROM CellMerge_Test
    union all
    -- Flight_No，Mawb_No 进行分组,并计算Count, Volume和Weigh列计算Sum
    -- group by 的字段在结果集中唯一
    SELECT Flight_No,Mawb_No,'',  SUM(Volume)SumVolume,SUM(Weight)SumWeight ,0 'dType',COUNT(Flight_No) CountFlight_No,COUNT(Mawb_No)CountMawb_No
    FROM CellMerge_Test 
    GROUP BY Flight_No,Mawb_No
    ) t 
    ) t1 order by num   

效果图：

 

MySql语句：

 

    select 
     (case dType when 1 then Flight_No else CONVERT(CountFlight_No,CHAR) end) Flight_No
    ,(case dType when 1 then Mawb_No else CONVERT(CountMawb_No,CHAR) end) Mawb_No
    ,Hawb_No
    ,Volume
    ,Weight
    from (
    SELECT * FROM (
    SELECT Flight_No, Mawb_No, Hawb_No, Volume, Weight,1 'dType',0  CountFlight_No,0 CountMawb_No FROM CellMerge_Test
    union all
    -- Flight_No，Mawb_No 进行分组,并计算Count，Volume和Weigh计算Sum
    -- group by 的字段在结果集中唯一
    SELECT Flight_No,Mawb_No,'',  sum(Volume)SumVolume,SUM(Weight)SumWeight ,0 'dType',COUNT(Flight_No) CountFlight_No,COUNT(Mawb_No)CountMawb_No
    FROM CellMerge_Test 
    GROUP BY Flight_No,Mawb_No
    ) t order by Flight_No,Mawb_No,dType 
    ) t1

效果图：