求個取值復雜和古怪的寫法，大家幫幫忙啊￣
表:
create table sales as
select t1.year,t2.area,t3.dept,rownum as profit
from
(select '2000 ' as year from dual union all
select '2001 ' as year from dual ) t1,
(select 'east ' as area from dual union all
select 'west ' as area from dual ) t2,
(select 'hard ' as dept from dual union all
select 'soft ' as dept from dual ) t3
;
－－－－－－－－－－－－－－
SQL> select * from sales;----create好後的表
YEAR AREA DEPT PROFIT
---- ---- ---- ---------
2000 east hard 1
2001 east hard 2
2000 west hard 3
2001 west hard 4
2000 east soft 5
2001 east soft 6
2000 west soft 7
2001 west soft 8
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
問：1。一個SQL 顯示以下資料：
說明：相同yaea,area,dept 的profit 合計
相同year,area 的profit合計
相同year的profit 合計
YEAR AREA DEPT sum(PROFIT)
---- ---- ---- ---------
2000 east hard 1
2000 east soft 2
2000 east 3
2000 west hard 3
2000 west soft 4
2000 west 7
2000 10
2001 east hard 5
2001 east soft 6
2001 east 11
2000 west hard 7
2001 west soft 8
2001 west 15
2001 26
==================
2.一個SQL句子：顯示三欄位各種可能情況之和計
結果和第一問想似，但還有更多的情況。
就是取值分別為 2000或2001 ,east 或west,
hard 或 soft 的排列組合.
[解决办法]
是对的拉
1+2+3+4+5+6+7+8 = 36 (这个是总的合计)
是对你的源数据看~~ 我刚才对了一下没错哦~~
------------------------------------------------
SQL> select * from sales;----create好後的表
YEAR AREA DEPT PROFIT
---- ---- ---- ---------
2000 east hard 1
2001 east hard 2
2000 west hard 3
2001 west hard 4
2000 east soft 5
2001 east soft 6
2000 west soft 7
2001 west soft 8