求一句SQL
本帖最后由 gongshunkai2 于 2013-06-09 01:04:02 编辑 表 Goods（商品表）
字段 GoodsId StartDate
1 2013-5-1
2 2013-4-1
3 2013-6-3
表 OrderGoods（订单商品表）
字段 GoodsNumber GoodsId OrderId
1 1 1
2 2 1
1 3 1
2 3 2
1 1 2
5 2 3
1 3 4
表OrderInfo（订单详情表）
字段 OrderId AddTime
1 2013-5-3
2 2013-5-5
3 2013-5-8
4 2013-6-9
结果：
2
7
1
GoodsId1被购买过2次，总计GoodsNumber是2，GoodsId2被购买过2次，总计GoodsNumber是7，GoodsId3被购买过3次，总计GoodsNumber是4，但是前2次的AddTime<StartDate，所以只计算最后一次，是1
求SQL
[解决办法]
create table Goods
(GoodsId int, StartDate date)
insert into Goods
select 1, '2013-5-1' union all
select 2, '2013-4-1' union all
select 3, '2013-6-3'
create table OrderGoods
(GoodsNumber int, GoodsId int, OrderId int)
insert into OrderGoods
select 1, 1, 1 union all
select 2, 2, 1 union all
select 1, 3, 1 union all
select 2, 3, 2 union all
select 1, 1, 2 union all
select 5, 2, 3 union all
select 1, 3, 4
create table OrderInfo
(OrderId int, AddTime date)
insert into OrderInfo
select 1, '2013-5-3' union all
select 2, '2013-5-5' union all
select 3, '2013-5-8' union all
select 4, '2013-6-9'
select a.GoodsId,
sum(case when b.AddTime>c.StartDate then a.GoodsNumber
else 0 end) 'GoodsNumber'
from OrderGoods a
inner join OrderInfo b on a.OrderId=b.OrderId
inner join Goods c on a.GoodsId=c.GoodsId
group by a.GoodsId
/*
GoodsId GoodsNumber
----------- -----------
1 2
2 7
3 1
(3 row(s) affected)
*/
select a.GoodsId,
count(distinct case when b.AddTime>c.StartDate then a.OrderId
else null end) 'OrderId'
from OrderGoods a
inner join OrderInfo b on a.OrderId=b.OrderId
inner join Goods c on a.GoodsId=c.GoodsId
group by a.GoodsId
