SQL查询建议 - 最新项目

I have a table where I store customer sales (on periodicals, like newspaper) data. The product is stored by issue. Example

我有一张表,用于存储客户销售(期刊,如报纸)数据。产品按问题存储。例

custid  prodid  issue   qty     datesold
1       123     2       12      01052008
2       234     1       5       01022008
1       123     1       5       01012008
2       444     2       3       02052008

How can I retrieve (whats a faster way) the get last issue for all products, for a specific customer? Can I have samples for both SQL Server 2000 and 2005? Please note, the table is over 500k rows.

对于特定客户,我如何检索(以更快的方式)所有产品的最后一期问题?我可以为SQL Server 2000和2005提供样本吗?请注意,该表超过500k行。

Thanks

5 个解决方案

#1

Assuming that "latest" is determined by date (rather than by issue number), this method is usually pretty fast, assuming decent indexes:

假设“最新”是由日期(而不是发行号)决定的,这个方法通常非常快,假设有合适的索引:

SELECT
     T1.prodid,
     T1.issue
FROM
     Sales T1
LEFT OUTER JOIN dbo.Sales T2 ON
     T2.custid = T1.custid AND
     T2.prodid = T1.prodid AND
     T2.datesold > T1.datesold
WHERE
     T1.custid = @custid AND
     T2.custid IS NULL

Handling 500k rows is something that a laptop can probably handle without trouble, let alone a real server, so I'd stay clear of denormalizing your database for "performance". Don't add extra maintenance, inaccuracy, and most of all headaches by tracking a "last sold" somewhere else.

处理500k行是笔记本电脑可以毫无问题地处理的事情,更不用说真正的服务器了,所以我不会为了“性能”而对数据库进行非规范化处理。不要通过跟踪其他地方的“最后售出”来增加额外的维护,不准确以及最重要的麻烦。

EDIT: I forgot to mention... this doesn't specifically handle cases where two issues have the same exact datesold. You might need to tweak it based on your business rules for that situation.

编辑:我忘了提...这不是专门处理两个问题具有相同确切日期的情况。您可能需要根据您针对该情况的业务规则进行调整。

#2

Generic SQL; SQL Server's syntax shouldn't be much different:

通用SQL; SQL Server的语法应该没有太大区别:

SELECT prodid, max(issue) FROM sales WHERE custid = ? GROUP BY prodid;

#3

Is this a new project? If so, I would be wary of setting up your database like this and read up a bit on normalization, so that you might end up with something like this:

这是一个新项目吗?如果是这样,我会担心像这样设置你的数据库并阅读一些关于规范化的内容,这样你最终会得到这样的结果:

CustID LastName FirstName
------ -------- ---------
1      Woman    Test
2      Man      Test

ProdID ProdName
------ --------
123    NY Times
234    Boston Globe

ProdID IssueID PublishDate
------ ------- -----------
123    1       12/05/2008
123    2       12/06/2008

CustID OrderID OrderDate
------ ------- ---------
1      1       12/04/2008

OrderID ProdID IssueID Quantity
------- ------ ------- --------
1       123    1       5
2       123    2       12

I'd have to know your database better to come up with a better schema, but it sound like you're building too many things into a flat table, which will cause lots of issues down the road.

我必须更好地了解你的数据库,以便提出更好的架构,但这听起来好像你在平板表中构建了太多东西,这将导致很多问题。

#4

If you're looking for most recent sale by date maybe that's what you need:

如果您正在寻找按日期销售的最新产品,那么您可能需要:

SELECT prodid, issue
  FROM Sales 
WHERE custid = @custid 
      AND datesold = SELECT MAX(datesold) 
                       FROM Sales s 
                      WHERE s.prodid = Sales.prodid
                         AND s.issue = Sales.issue
                        AND s.custid = @custid

#5

To query on existing growing historical table is way too slow!

查询现有的增长历史表太慢了!

Strongly suggest you create a new table tblCustomerSalesLatest which stores the last issue data of each customer. and select from there.

强烈建议您创建一个新表tblCustomerSalesLatest,它存储每个客户的最后一期数据。并从那里选择。

#1