group by C.category 这一句不要应该就可以了 这样如果category有多个值的话查出来就不会是一个值了
select S.sale_time,C.category from sale_fact S,product P,category C,customer U where S.product_id=P.product_id and P.category_id=C.category_id and S.customer_id=U.customer_id and datediff(yy,U.birthday,getdate()) between 30 and 39 and /* 问题出现处 */ EXISTS(select count(*)*100/@amount from sale_fact S,product P,category C,customer U where S.product_id=P.product_id and P.category_id=C.category_id and S.customer_id=U.customer_id and datediff(yy,U.birthday,getdate()) between 30 and 39 group by C.category Having count(*)*100/@amount>15 )
select S.sale_time,C.category from sale_fact S,product P,category C,customer U where S.product_id=P.product_id and P.category_id=C.category_id and S.customer_id=U.customer_id and datediff(yy,U.birthday,getdate()) between 30 and 39 and exists(select count(*)*100/@amount from sale_fact ss,product Pp,category Cc,customer Uu where Ss.product_id=Pp.product_id and Pp.category_id=Cc.category_id and Ss.customer_id=Uu.customer_id and datediff(yy,Uu.birthday,getdate()) between 30 and 39 and Cc.category_id=C.category_id )>15
select S.sale_time,C.category from sale_fact S,product P,category C,customer U where S.product_id=P.product_id and P.category_id=C.category_id and S.customer_id=U.customer_id and datediff(yy,U.birthday,getdate()) between 30 and 39 and exists(select count(*)*100/@amount from sale_fact ss,product Pp,category Cc,customer Uu where Ss.product_id=Pp.product_id and Pp.category_id=Cc.category_id and Ss.customer_id=Uu.customer_id and datediff(yy,Uu.birthday,getdate()) between 30 and 39 and Cc.category_id=C.category_id group by Cc.category_id having count(*)*100/@amount>15 )
declare @amount int select @amount=count(*) from sale_fact where customer_id in (select customer_id from customer where datediff(yy,C.birthday,getdate()) between 30 and 39)select S.sale_time,C.category from sale_fact S join product P on S.product_id=P.product_id join category C on P.category_id=C.category_id join customer U on S.customer_id=U.customer_id where datediff(yy,U.birthday,getdate()) between 30 and 39 and C.category in ( select C.category from sale_fact S join product P on S.product_id=P.product_id join category C on P.category_id=C.category_id join customer U on S.customer_id=U.customer_id where datediff(yy,U.birthday,getdate()) between 30 and 39 group by C.category having sum(1)*100/@amount>15)
select * from sale_fact where category in ( select C.category from sale_fact S,product P,category C,customer U where S.product_id=P.product_id and P.category_id=C.category_id and S.customer_id=U.customer_id and datediff(yy,U.birthday,getdate()) between 30 and 39 group by C.category having count(*)*100/ (select count(*) from sale_fact S,customer C where S.customer_id=C.customer_id and datediff(yy,C.birthday,getdate()) between 30 and 39) >15 )
这一句不要应该就可以了
这样如果category有多个值的话查出来就不会是一个值了
from sale_fact S,product P,category C,customer U
where S.product_id=P.product_id
and
P.category_id=C.category_id
and
S.customer_id=U.customer_id
and
datediff(yy,U.birthday,getdate()) between 30 and 39
and /* 问题出现处 */
EXISTS(select count(*)*100/@amount
from sale_fact S,product P,category C,customer U
where S.product_id=P.product_id
and
P.category_id=C.category_id
and
S.customer_id=U.customer_id
and
datediff(yy,U.birthday,getdate()) between 30 and 39
group by C.category
Having count(*)*100/@amount>15
)
sale_fact 通过这两个表联合查询找到产品的种类(如:手机)因为sale_fact销售事实表只有product_id,其他信息要联表查询
from sale_fact S,product P,category C,customer U
where S.product_id=P.product_id
and
P.category_id=C.category_id
and
S.customer_id=U.customer_id
and
datediff(yy,U.birthday,getdate()) between 30 and 39
and exists(select count(*)*100/@amount
from sale_fact ss,product Pp,category Cc,customer Uu
where Ss.product_id=Pp.product_id
and
Pp.category_id=Cc.category_id
and
Ss.customer_id=Uu.customer_id
and
datediff(yy,Uu.birthday,getdate()) between 30 and 39 and Cc.category_id=C.category_id
)>15
from sale_fact S,product P,category C,customer U
where S.product_id=P.product_id
and
P.category_id=C.category_id
and
S.customer_id=U.customer_id
and
datediff(yy,U.birthday,getdate()) between 30 and 39
and exists(select count(*)*100/@amount
from sale_fact ss,product Pp,category Cc,customer Uu
where Ss.product_id=Pp.product_id
and Pp.category_id=Cc.category_id
and Ss.customer_id=Uu.customer_id
and datediff(yy,Uu.birthday,getdate()) between 30 and 39 and Cc.category_id=C.category_id group by Cc.category_id
having count(*)*100/@amount>15 )
select @amount=count(*) from sale_fact where customer_id in (select customer_id from customer where datediff(yy,C.birthday,getdate()) between 30 and 39)select S.sale_time,C.category from sale_fact S
join product P on S.product_id=P.product_id
join category C on P.category_id=C.category_id
join customer U on S.customer_id=U.customer_id
where datediff(yy,U.birthday,getdate()) between 30 and 39
and C.category in (
select C.category from sale_fact S
join product P on S.product_id=P.product_id
join category C on P.category_id=C.category_id
join customer U on S.customer_id=U.customer_id
where datediff(yy,U.birthday,getdate()) between 30 and 39
group by C.category having sum(1)*100/@amount>15)
select * from sale_fact where category in
( select C.category
from sale_fact S,product P,category C,customer U
where S.product_id=P.product_id and P.category_id=C.category_id and S.customer_id=U.customer_id
and datediff(yy,U.birthday,getdate()) between 30 and 39
group by C.category
having count(*)*100/
(select count(*) from sale_fact S,customer C where S.customer_id=C.customer_id
and datediff(yy,C.birthday,getdate()) between 30 and 39) >15 )