1,
select s.customer_id,Sum(s.buy_number) as SumCount from sellinfo s
WHERE s.sellday>'2008-01-13' and s.sellday<'2008-05-31'
group by s.customer_id having Sum(s.buy_number) >8
2,
sellinfo表
id customer_id buy_number
1 3 4
2 3 5
3 3 1
4 4 9
5 5 10
我想得到总购买数量>8的人数
结果:3
sql应该怎么写?
select count(*) as adfs from
(
select s.customer_id from sellinfo s
WHERE s.sellday>'2008-01-13' and s.sellday<'2008-05-31'
group by s.customer_id having Sum(s.buy_number) >8
) as sell
3,
select s.customer_id ,count(s.customer_id) from sellinfo s
WHERE s.sellday>'2008-01-13' and s.sellday<'2008-05-31'
group by s.customer_id with rollup having Sum(s.buy_number) >8
改SQL比上面多了 with rollup 可以统计出符合Where 条件的所有customer_id的数量。但是不过滤后面的Having条件
select s.customer_id,Sum(s.buy_number) as SumCount from sellinfo s
WHERE s.sellday>'2008-01-13' and s.sellday<'2008-05-31'
group by s.customer_id having Sum(s.buy_number) >8
2,
sellinfo表
id customer_id buy_number
1 3 4
2 3 5
3 3 1
4 4 9
5 5 10
我想得到总购买数量>8的人数
结果:3
sql应该怎么写?
select count(*) as adfs from
(
select s.customer_id from sellinfo s
WHERE s.sellday>'2008-01-13' and s.sellday<'2008-05-31'
group by s.customer_id having Sum(s.buy_number) >8
) as sell
3,
select s.customer_id ,count(s.customer_id) from sellinfo s
WHERE s.sellday>'2008-01-13' and s.sellday<'2008-05-31'
group by s.customer_id with rollup having Sum(s.buy_number) >8
改SQL比上面多了 with rollup 可以统计出符合Where 条件的所有customer_id的数量。但是不过滤后面的Having条件