with temp as( select max(num) num,milk,butter,bread from( select count(B#) num,milk,butter,bread from( select B#,milk,butter,bread from( select B#,max(decode(item,'MILK',item,null)) MILK, max(decode(item,'BUTTER',item,null)) BUTTER, max(decode(item,'BEER',item,null)) BEER, max(decode(item,'BREAD',item,null)) BREAD from basket group by B# order by B# ) where milk is not null or butter is not null or bread is not null ) group by cube(milk,butter,bread) ) where milk is not null or butter is not null or bread is not null group by milk ,butter,bread ) select case when milk is not null and butter is null and bread is null then milk when butter is not null and milk is null and bread is null then butter when bread is not null and milk is null and butter is null then bread end item1, case when milk is not null and butter is not null and bread is null then milk||','||butter when milk is not null and bread is not null and butter is null then milk||','||bread when bread is not null and butter is not null and milk is null then bread||','||butter end item2, case when milk is not null and butter is not null and bread is not null then milk||','||butter||','||bread end item3,num count# from temp order by item1,item2,item3 看一看是否符合你的要求
--应该用rollup可以解决,按照ITEM来依次汇总 --试下,因为没环境没测试过 with tab as( select b#, max(decode(item,'BREAD','BREAD',null))item1, max(decode(item,'BUTTER','BUTTER',null))item2, max(decode(item,'MILK','MILK',null))item3 from basket group by b#) select wm_concat(item1,item2,item3) item,sum(1) counter from tab group by rollup(item1,item2,item3) order by 1;
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
select max(num) num,milk,butter,bread from(
select count(B#) num,milk,butter,bread from(
select B#,milk,butter,bread from(
select B#,max(decode(item,'MILK',item,null)) MILK,
max(decode(item,'BUTTER',item,null)) BUTTER,
max(decode(item,'BEER',item,null)) BEER,
max(decode(item,'BREAD',item,null)) BREAD
from basket group by B# order by B#
) where milk is not null or butter is not null or bread is not null
) group by cube(milk,butter,bread)
) where milk is not null or butter is not null or bread is not null
group by milk ,butter,bread
)
select
case
when milk is not null and butter is null and bread is null then milk
when butter is not null and milk is null and bread is null then butter
when bread is not null and milk is null and butter is null then bread
end item1,
case
when milk is not null and butter is not null and bread is null then milk||','||butter
when milk is not null and bread is not null and butter is null then milk||','||bread
when bread is not null and butter is not null and milk is null then bread||','||butter
end item2,
case
when milk is not null and butter is not null and bread is not null then milk||','||butter||','||bread
end item3,num count# from temp order by item1,item2,item3
看一看是否符合你的要求
--应该用rollup可以解决,按照ITEM来依次汇总
--试下,因为没环境没测试过
with tab as(
select b#,
max(decode(item,'BREAD','BREAD',null))item1,
max(decode(item,'BUTTER','BUTTER',null))item2,
max(decode(item,'MILK','MILK',null))item3
from basket group by b#)
select wm_concat(item1,item2,item3) item,sum(1) counter
from tab
group by rollup(item1,item2,item3)
order by 1;