GROUP BY WITH CUBE 或 WITH ROLLUP 语句中的列数或表达式数目 10 每个索引的列数 我想超过10个连怎么办就是 slelect qw,ww,www,wwe,rrr,tt,yy,uu,ii,oo,ff,gg,sum(efe) from ddd
group by qw,ww,www,wwe,rrr,tt,yy,uu,ii,oo,ff,gg比如这样 怎么解决
group by qw,ww,www,wwe,rrr,tt,yy,uu,ii,oo,ff,gg比如这样 怎么解决
group by qw,ww,www,wwe,rrr,tt,yy,uu,ii,oo,ff,gg
with rollup因为 我想要统计
大哥 来个语句
insert into #t values(1,2,3,4,5)
insert into #t values(1,2,3,4,6)
insert into #t values(1,2,3,4,7)
insert into #t values(1,2,3,4,8)
insert into #t values(1,3,3,4,5)
insert into #t values(1,3,3,4,6)
insert into #t values(1,3,3,4,8)
insert into #t values(1,3,3,4,7) insert into #t values(2,2,2,4,5)
insert into #t values(2,2,3,4,6)
insert into #t values(2,2,4,4,7)
insert into #t values(2,2,5,4,8)
insert into #t values(2,3,6,4,5)
insert into #t values(2,3,3,4,6)
insert into #t values(2,3,3,4,8)
insert into #t values(2,3,3,4,7)select
case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b, sum(c) as c,sum(d) as d,sum(e) as e
from
#t
group by
a,b
with rollup
having grouping(b)=0 or grouping(a)=1/*a b c d e
------------------------------ ----------- ----------- ----------- -----------
1 2 12 16 26
1 3 12 16 26
2 2 14 16 26
2 3 15 16 26
合计 NULL 53 64 104(所影响的行数为 5 行)select
case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b, c, sum(d) as d,sum(e) as e
from
#t
group by
a,b,c
with rollup
having grouping(c)=0 or grouping(a)=1
a b c d e
------------------------------ ----------- ----------- ----------- -----------
1 2 3 16 26
1 3 3 16 26
2 2 2 4 5
2 2 3 4 6
2 2 4 4 7
2 2 5 4 8
2 3 3 12 21
2 3 6 4 5
合计 NULL NULL 64 104(所影响的行数为 9 行)
select
case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b,
case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c as varchar) end c,
sum(d) as d,sum(e) as e
from
#t
group by
a,b,c
with rollup
having grouping(a)=1 or grouping(b)=0 a b c d e
------------------------------ ----------- ------------------------------ ----------- -----------
1 2 3 16 26
1 2 小计 16 26
1 3 3 16 26
1 3 小计 16 26
2 2 2 4 5
2 2 3 4 6
2 2 4 4 7
2 2 5 4 8
2 2 小计 16 26
2 3 3 12 21
2 3 6 4 5
2 3 小计 16 26
合计 NULL NULL 64 104(所影响的行数为 13 行)
select
case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
case when grouping(b)=1 and grouping(a)=0 then '小计' else cast(b as varchar) end b, sum(c) as c, sum(d) as d,sum(e) as e from #t
group by
a,b,c
with rollup
having grouping(a)=1 or grouping(b)=1 or grouping(c)=0drop table #ta b c d e
------------------------------ ------------------------------ ----------- ----------- -----------
1 2 12 16 26
1 3 12 16 26
1 小计 24 32 52
2 2 2 4 5
2 2 3 4 6
2 2 4 4 7
2 2 5 4 8
2 3 9 12 21
2 3 6 4 5
2 小计 29 32 52
合计 NULL 53 64 104(所影响的行数为 11 行)*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/fredrickhu/archive/2009/09/24/4587789.aspx
CAST(PO_PAGECNT AS INT)/(CAST(PO_PRINTCNT AS INT)*CAST(JO_ROUND AS INT))AS TOTAL_PP,C.JO_BLACKP+'%'AS JO_BLACKP,
CAST((CAST(PO_PAGECNT AS INT)/(CAST(PO_PRINTCNT AS INT)*CAST(JO_ROUND AS INT))*CONVERT(FLOAT,JO_BLACKP)/100) AS DECIMAL(38,0)) AS IC_PP,C.JO_COLORP+'%'AS JO_COLORP,
CAST((CAST(PO_PAGECNT AS INT)/(CAST(PO_PRINTCNT AS INT)*CAST(JO_ROUND AS INT))*CONVERT(FLOAT,JO_COLORP)/100) AS DECIMAL(38,0))AS CC_PP,
ISNULL(SUM(CASE WHEN C.CO_CODE = '0081'AND C.CO_CODE<>''THEN C.RE_2 ELSE C.RE_2 END),0) AS COUNTY_CNT,
C.JO_ROUND,C.JO_NUP
FROM
(
SELECT A.CO_CODE,A.CO_NAME,A.PU_MINNM,A.PU_COUNM,B.PO_TITLE,B.PO_ISSN,B.PO_PAGECNT,B.PO_PRINTCNT,B.JO_BLACKP,B.JO_COLORP,B.JO_ROUND,B.JO_NUP,A.RE_2 FROM IDC_READ AS A
INNER JOIN IDC_JOURNAL AS B ON A.DB_ISSN = B.PO_ISSN AND A.PO_PRINTCNT = B.PO_PRINTCNT
) AS C
GROUP BY C.CO_CODE,C.CO_NAME,C.PU_MINNM,C.PU_COUNM,C.PO_TITLE,C.PO_ISSN,C.PO_PAGECNT,C.PO_PRINTCNT,C.JO_BLACKP,C.JO_COLORP,C.JO_ROUND,C.JO_NUP
with rollup我想这样写怎么不行 显示 with rollup 超过10 个索引的列数
我有比如 group by a,b,c,d,e,f,g,h,j,k,l,p
我有12个左右 能不能实现阿
没有聚合的就 GROUP BY 进去啊
但是 with rollup 这个他提示 GROUPBY里不能超过10个字段
10个以内是可以但 超过10个就连不了