sql不能对包含聚合或子查询的表达式执行聚合函数。

Question

本帖最后由 qq_16624883 于 2014-08-19 13:53:01 编辑

Answer 1

试一下:;
WITH    cte
          AS ( SELECT   companyid ,
                        year ,
                        et ,
                        SUM(totalprice) [sum]
               FROM     t_order
               WHERE    companyid IS NOT NULL
               GROUP BY companyid ,
                        year ,
                        et
             )
    SELECT  a.companyid ,
            a.[year] ,
            a.et ,
            b.[max]
    FROM    cte a
            INNER JOIN ( SELECT [year] ,
                                et ,
                                MAX([sum]) [max]
                         FROM   cte
                         GROUP BY [year] ,
                                et
                       ) b ON a.[year] = b.[year]
                              AND a.et = b.et

Answer 2

year    et                       totalprice       companyid
1 本地 6 6
1 本地 11 1
1 本地 16 2
1 本地 22 1
1 本地 26 4
1 本地 36 5
2 本地 5 2
2 本地 10 3
2 本地 15 1
2 本地 19 2
2 本地 20 3
2 本地 29 42 区域 9 1
2 区域 10 3
2 区域 15 4
2 区域 13 6
2 区域 14 5
2 区域 20 2

Answer 3

year    et                       totalprice       companyid
1        本地                                6                 6
1        本地                               11                 1
1        本地                                16                 2
1        本地                                22                 1
1        本地                                26                 4
1        本地                                36                 5
2        本地                               5                 2
2        本地                                10                 3
2        本地                                15                 1
2        本地                                19                 2
2        本地                                20                 3
2        本地                                29                 42        区域                                9                 1
2        区域                                10                 3
2        区域                                15                 4
2        区域                                13                 6
2        区域                                14                 5
2        区域                                20                 2

Answer 4

...
                      ) b ON a.[year] = b.[year]
                              AND a.et = b.et
                              AND a.[sum] = b.[max] -- 1楼少了这个条件

Answer 5

正解!!!!
完整为WITH    cte
          AS ( SELECT   companyid ,
                        year ,
                        et ,
                        SUM(totalprice) [sum]
               FROM     t_order
               WHERE    companyid IS NOT NULL
               GROUP BY companyid ,
                        year ,
                        et
             )
    SELECT  a.companyid ,
            a.[year] ,
            a.et ,
            b.[max]
    FROM    cte a
            INNER JOIN ( SELECT [year] ,
                                et ,
                                MAX([sum]) [max]
                         FROM   cte
                         GROUP BY [year] ,
                                et
                       ) b ON a.[year] = b.[year]
                              AND a.et = b.et
                              AND a.[sum] = b.[max]

Answer 6

是少了----------------------------------------------------------------
-- Author  :DBA_HuangZJ(發糞塗牆)
-- Date    :2014-08-19 14:57:09
-- Version:
--      Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
-- May 14 2014 18:34:29 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[t_order]
if object_id('[t_order]') is not null drop table [t_order]
go 
create table [t_order]([year] int,[et] varchar(4),[totalprice] int,[companyid] int)
insert [t_order]
select 1,'本地',6,6 union all
select 1,'本地',11,1 union all
select 1,'本地',16,2 union all
select 1,'本地',22,1 union all
select 1,'本地',26,4 union all
select 1,'本地',36,5 union all
select 2,'本地',5,2 union all
select 2,'本地',10,3 union all
select 2,'本地',15,1 union all
select 2,'本地',19,2 union all
select 2,'本地',20,3 union all
select 2,'本地',29,4 union all
select 2,'区域',9,1 union all
select 2,'区域',10,3 union all
select 2,'区域',15,4 union all
select 2,'区域',13,6 union all
select 2,'区域',14,5 union all
select 2,'区域',20,2
--------------开始查询--------------------------
;
WITH    cte
          AS ( SELECT   companyid ,
                        year ,
                        et ,
                        SUM(totalprice) [sum]
               FROM     t_order
               WHERE    companyid IS NOT NULL
               GROUP BY companyid ,
                        year ,
                        et
             )
    SELECT  a.companyid ,
            a.[year] ,
            a.et ,
            b.[max]
    FROM    cte a
            INNER JOIN ( SELECT [year] ,
                                et ,
                                MAX([sum]) [max]
                         FROM   cte
                         GROUP BY [year] ,
                                et
                       ) b ON a.[year] = b.[year]
                              AND a.et = b.et AND a.sum=b.max----------------结果----------------------------
/* 
companyid   year        et max
----------- ----------- ------ -----------
2           2           区域     20
3           2           本地     30
5           1           本地     36
*/

Answer 7

 select companyID,year,et,[MAX] from 
 (select companyID,year,et,sum(price) [max],rn=ROW_NUMBER() over (partition by year,et order by sum(price) desc)
 from t_order group by company,year,et) a where rn=1

Answer 8

--最后一行，company为companyid
 select companyID,year,et,[MAX] from 
 (select companyID,year,et,sum(price) [max],rn=ROW_NUMBER() over (partition by year,et order by sum(price) desc)
 from cte group by companyID,year,et) a where rn=1