注意,不能用 cube ,1 条sql语句大侠帮忙具体的就是, 有个数据库表 sales 有4个fields(Location,Time,Item,Quantity)有以下数据:Sydney,2004,ps2,1400
Sydney,2005,ps2,1500
Melbourne,2004,ps2,1100
Melbourne.2005,XBox,1700 怎么可以得到数据(Location,Time,Item,SUM(Quantity)):
Sydney,2004,ps2,1400
Sydney,2004,XBox,0
Sydney,2005,ps2,1500
Sydney,2005,XBox,0
Melbourne,2004,ps2,1100
Melbourne,2004,XBox,0
Melbourne.2005,XBox,1700
Melbourne.2005,ps2,0
Sydney,2005,ps2,1500
Melbourne,2004,ps2,1100
Melbourne.2005,XBox,1700 怎么可以得到数据(Location,Time,Item,SUM(Quantity)):
Sydney,2004,ps2,1400
Sydney,2004,XBox,0
Sydney,2005,ps2,1500
Sydney,2005,XBox,0
Melbourne,2004,ps2,1100
Melbourne,2004,XBox,0
Melbourne.2005,XBox,1700
Melbourne.2005,ps2,0
from sales
group by location, time, item你是要这个么,感觉简单了点
insert into @sales select 'Sydney ',2004,'ps2 ',1400
insert into @sales select 'Sydney ',2005,'ps2 ',1500
insert into @sales select 'Melbourne',2004,'ps2 ',1100
insert into @sales select 'Melbourne',2005,'XBox',1700select
a.Location,b.Time,c.Item,isnull(d.Quantity,0) Quantity
from
(select distinct Location from @sales) a
cross join
(select distinct Time from @sales) b
cross join
(select distinct Item from @sales) c
left join
@sales d
on
a.Location=d.Location and b.Time=d.Time and c.Item=d.Item
order by
a.Location desc,b.Time,c.Item/*
Location Time Item Quantity
-------------------- ----------- ---------- -----------
Sydney 2004 ps2 1400
Sydney 2004 XBox 0
Sydney 2005 ps2 1500
Sydney 2005 XBox 0
Melbourne 2004 ps2 1100
Melbourne 2004 XBox 0
Melbourne 2005 ps2 0
Melbourne 2005 XBox 1700
*/
insert into @sales select 'Sydney ',2004,'ps2 ',1400
insert into @sales select 'Sydney ',2005,'ps2 ',1500
insert into @sales select 'Melbourne',2004,'ps2 ',1100
insert into @sales select 'Melbourne',2005,'XBox',1700select
a.Location,b.Time,c.Item,isnull(sum(d.Quantity),0) Quantity
from
(select distinct Location from @sales) a
cross join
(select distinct Time from @sales) b
cross join
(select distinct Item from @sales) c
left join
@sales d
on
a.Location=d.Location and b.Time=d.Time and c.Item=d.Item
group by
a.Location,b.Time,c.Item
order by
a.Location desc,b.Time,c.Item/*
Location Time Item Quantity
-------------------- ----------- ---------- -----------
Sydney 2004 ps2 1400
Sydney 2004 XBox 0
Sydney 2005 ps2 1500
Sydney 2005 XBox 0
Melbourne 2004 ps2 1100
Melbourne 2004 XBox 0
Melbourne 2005 ps2 0
Melbourne 2005 XBox 1700
*/
(SELECT SUM(quantity)
FROM sales
WHERE location = a.location AND time = b.time AND item = c.item)
AS SUM_Quantity
FROM (SELECT location
FROM TABLE1) a CROSS JOIN
(SELECT time
FROM TABLE1) b CROSS JOIN
(SELECT item
FROM TABLE1) c
ORDER BY a.location DESC
(SELECT isnull(SUM(quantity), 0)
FROM sales
WHERE location = a.location AND time = b.time AND item = c.item)
AS SUM_Quantity
FROM (SELECT location
FROM sales) a CROSS JOIN
(SELECT time
FROM sales) b CROSS JOIN
(SELECT item
FROM sales) c
ORDER BY a.location DESC