select product.productid,Category.Cat_CategoryId,product.name,isnull(num1,0) as num from product
left join(
select orderproduct.productid as productid,count(orderproduct.productid)as num1 from orderproduct
join orders on orders.orderid=orderproduct.orderid
where orderStatusId = 3 and orderdate>='2008-1-1' and orderdate <'2008-2-1'
group by orderproduct.productid
) aa on product.productid=aa.productid
left join Category on Category.CategoryId = product.CategoryId
order by product.productid asc
我现在能查出'2008-1-1' 到'2008-2-1' 的没种产品的销售量,但是现在想要的结果是我想查出每一天每一种产品的销售请问一下我改怎么修改啊
left join(
select orderproduct.productid as productid,count(orderproduct.productid)as num1 from orderproduct
join orders on orders.orderid=orderproduct.orderid
where orderStatusId = 3 and orderdate>='2008-1-1' and orderdate <'2008-2-1'
group by orderproduct.productid
) aa on product.productid=aa.productid
left join Category on Category.CategoryId = product.CategoryId
order by product.productid asc
我现在能查出'2008-1-1' 到'2008-2-1' 的没种产品的销售量,但是现在想要的结果是我想查出每一天每一种产品的销售请问一下我改怎么修改啊
解决方案 »
- 不用游标,如何遍历SQL查询结果
- plglenn33,什么意思啊,被封杀了,郁闷啊,我没有发广告,没有发政治信息,没有发黄色信息,没有发暴力信息,谁有这个权限封杀一个良民的号呢?
- 我写的一条存储过程,用asp.net执行起来出错,不知道怎么原因???
- 菜鸟请教触发器写法
- 驱动程序的 SQLAllocHandle on SQL_HANDLE_ENV 失败
- 把mySql里面的数据导到MSSQL里面如何做
- 请达人解释说明下编写多语言程序Sql2000数据库设计的注意事项
- 又被注入了,能帮我找一下注入点吗?
- 谁有英文的技术 文章,
- 急求一条sql语句的写法!!在线等待。。
- SQL 2005的数据导出一般是什么格式?如何导入数据
- 关于Sql的查询问题
left join(
select convert(varchar(10),orderproduct.orderdate,120) as [day]
orderproduct.productid as productid,count(orderproduct.productid)as num1 from orderproduct
join orders on orders.orderid=orderproduct.orderid
where orderStatusId = 3
group by orderproduct.productid,convert(varchar(10),orderproduct.orderdate,120)
) aa on product.productid=aa.productid
left join Category on Category.CategoryId = product.CategoryId
order by product.productid asc
大致如下:select t1.* , isnull(tb.销售量,0) 销售量 from
(
select m.dt , n.产品 from
(
select '2008-01-01' dt union all
select '2008-01-02' dt union all
...
select '2008-02-01' dt union all
) m , (select distinct 产品 from tb) n
) t1 left join tb t2 on t1.dt = t2.dt and t1.产品 = t2.产品
product.productid,Category.Cat_CategoryId,product.name,aa.orderdate,isnull(num1,0) as num from product
left join
(
select
orderproduct.productid as productid,count(orderproduct.productid)as num1,convert(varchar(10),orderdate,120) as orderdate
from
orderproduct
join orders on
orders.orderid=orderproduct.orderid
where
orderStatusId = 3 and orderdate>='2008-1-1' and orderdate <'2008-2-1'
group by
orderproduct.productid,convert(varchar(10),orderdate,120)
) aa
on
product.productid=aa.productid
left join
Category on Category.CategoryId = product.CategoryId
order by
product.productid asc