查询销售日期为各产品2008年9月1日到2008年10月1日的销售数量的SQL数量怎样写表1product 字段 prod_no(产品名称)
表2inv_main 字段list_no(销售单号) sell_date(销售日期)
表3inv_sub 字段list_no(销售单号) prod_no(产品名称) sell_num(销售数量)
表2inv_main 字段list_no(销售单号) sell_date(销售日期)
表3inv_sub 字段list_no(销售单号) prod_no(产品名称) sell_num(销售数量)
FROM product AS p
JOIN inv_sub AS is
ON p.prod_no=is.prod_no
JOIN inv_main AS im
ON is.list_no=im.list_no
WHERE im.sell_date BETWEEN '2008-9-1' AND '2008-10-1'
表2inv_main 字段list_no(销售单号) sell_date(销售日期)
表3inv_sub 字段list_no(销售单号) prod_no(产品名称) sell_num(销售数量) select sum (s.sell_num) from inv_sub s join
(select list_no from inv_main where sell_date between '2008-9-1' and '2008-10-1')
as l on l.list_no=s.list_no
where a.prod_no=c.prod_no and b.list_no=c.list_no
and b.sell_date between '2008-9-1' and '2008-10-1'
group by a.list_no
SUM(sell_num) as totalSellnum FROM product AS p
JOIN inv_sub AS is
ON p.prod_no=is.prod_no
JOIN inv_main AS im
ON is.list_no=im.list_no
WHERE im.sell_date BETWEEN '2008-9-1' AND '2008-10-1'
group by P.prod_no
表2inv_main 字段list_no(销售单号) sell_date(销售日期)
表3inv_sub 字段list_no(销售单号) prod_no(产品名称) sell_num(销售数量)
--select a.*,d.sell_num as sum_sell
from product a
left join
(
select b.prod_no,sum(sell_num) as sellnum
from inv_main b
left join inv_sub c
on b.prod_no = c.prod_no
where selldate BETWEEN '2008-9-1' AND '2008-10-1'
group by b.prod_no
) d
on a.prod_no = d.prod_no
select inv_sub.prod_no,sum(inv_sub.sell_num),product.prod_name
from inv_sub,inv_main,product
where inv_sub.list_no = inv_main.list_no and inv_sub.list_no = product.prod_no
and sell_date between '2008-09-01' and '2008-10-01'
group by inv_sub.prod_no,product.prod_name
where a.prod_no=c.prod_no and b.list_no=c.list_no
and b.sell_date between '2008-9-1' and '2008-10-1'
group by a.prod_no
select prod_no,sum(sell_num)
from inv_main,inv_sub
where inv_main.list_no=inv_sub.list_no
and inv_main.sell_date between '2008-9-1'and '2008-10-1'
group by prod_no
FROM
inv_sub ISB
INNER JOIN inv_main IM
ON
ISB.list_no = IM.list_no
WHERE
IM.sell_date BETWEEN '2008-9-1' AND '2008-10-1'