按订购日期和产品区分SQL Server 2008 [英] Distinct by order date and products SQL Server 2008

问题描述

这是我在这里的第一篇文章，因此，如果我误认为自己的文章和英文，我会说声抱歉:)

This is my first post here so if I've been mistaken for my post and English I say sorry :)

我已经在Google做过研究，很遗憾，直到现在我还是无法解决这个问题.

I already do research in google and unfortunately until now I can't solve this.

这是我的问题，请按日期显示每种产品的总订单，我想按产品区分

This my problem, show the total orders by date on each product and I want to distinct by products

这是我的查询示例

SELECT Orders.OrderDate
          ,SUM(OrderDetails.OrderDetailQuantity) AS totalOrdered
          ,Products.ProductId 
FROM Orders 
INNER JOIN OrderDetails ON Orders.OrderId = OrderDetails.OrderId 
INNER JOIN Products ON OrderDetails.ProductId = Products.ProductId 
GROUP BY Orders.OrderId
          ,Orders.OrderDate
          ,Products.ProductId 
HAVING (CONVERT(VARCHAR,  Orders.OrderDate, 23) BETWEEN @from AND @to)

现在，我想根据OrderDate之间的产品进行区分，我知道这里最大的问题是DATE，但我不知道如何通过此查询区分日期.

Now, I want to distinct by product according to the between OrderDate, I know the big problem here is the DATE but I don't have any idea on how to distinct the date by this query.

请帮助我.谢谢.

P.S .:如果您想在linq表达式中解决此问题，将被广泛接受:)

P.S.: if do you want to solve this in linq expression it would be highly accepted :)

推荐答案

采样数据和所需结果会有所帮助.当您说与...不同"时，我想您的意思是分组依据".请注意，如果您确保@from&的时间部分，则在WHERE子句中无需强制转换Order.OrderDate.正确设置了@to参数(包括每一天).在比较的左侧应用强制转换操作绝不是一个好主意.

Sample data and desired results would help. When you say "distinct by" I assume you mean group by. Note, in the WHERE clause you dont need to cast Order.OrderDate if you ensure that the time component of your @from & @to params are set correctly (to include each entire day). Its never a good idea to apply a cast operation to the left side of a comparison.

SELECT  --cast(Orders.OrderDate as date),
        Products.ProductId 
        SUM(OrderDetails.OrderDetailQuantity) AS totalOrdered,
FROM Orders 
INNER JOIN OrderDetails ON Orders.OrderId = OrderDetails.OrderId 
INNER JOIN Products ON OrderDetails.ProductId = Products.ProductId 
where Orders.OrderDate between cast(@from as date) AND cast(@to as date)
GROUP 
BY      --cast(Orders.OrderDate as date),
        Products.ProductId 


-- to illustrate:
declare @From datetime = '1/1/2000 10:30:22',
        @To datetime = '1/3/2000 9:11:31'

declare @orders table (i int, dt datetime)
insert into @orders
    values(1, '1/1/2000 8:00'),(2, '1/2/2000 10:00'), (3, '1/4/2000 3:00')


-- only returns i=2
select *
from    @orders
where dt between @From and @To

-- returns i=1 & i=2
select *
from    @orders
where dt between cast(@From as date) and cast(@To as date)

这篇关于按订购日期和产品区分SQL Server 2008的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！