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) */
多谢:唐诗三百首,可以用了,但是我还要求OrderId总数,我改成下面的,但发现即使没符合查询要求,也不会是0,最少也是1,应该怎么改?请赐教。select a.GoodsId, count(distinct case when b.AddTime>c.StartDate then a.OrderId else 0 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
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
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)
*/
count(distinct case when b.AddTime>c.StartDate then a.OrderId
else 0 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
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