枢轴的SQL唯一值编号顺序 [英] SQL Unique Values Numbering Sequence for Pivot

问题描述

出于报告的目的，我需要透视每个记录上唯一的查询结果.我目前的说法是:

For reporting purcposes, I need to Pivot results of a query that is unique on each record. My current statement is:

SELECT *
FROM Sales AS x
WHERE (select count(*) from Sales where customer_name=x.customer_name 
        and order_date>=x.order_date)<=5
ORDER BY customer_name, order_date, price;

查询输出的示例是:

customer_name    order_date        price
Company A        2009-02-01        800 
Company A        2009-03-01        100
Company A        2009-04-01        200
Company A        2009-05-01        300
Company A        2009-06-01        500
Company B        2009-02-01        100
Company B        2009-02-01        800
Company B        2009-04-01        200
Company B        2009-05-01        300
Company B        2009-06-01        500

最终，信息需要看起来像这样:

Ultimately, the information needs to look like this:

Customer_Name     order_date1  price1    order_date2  price2    order_date3  price3    order_date4  price4    order_date5  price5   
Company A         2009-02-01   800       2009-03-01   100       2009-04-01   200       2009-05-01   300       2009-06-01   500
Company B         2009-02-01   100       2009-02-01   800       2009-04-01   200       2009-05-01   300       2009-06-01   500

我认为我需要为"pivot_id"添加一列，以便每个分组都有一个共同的记录，以便在透视之前的查询结果如下所示:

I'm thinking that I need to add a column for "pivot_id" so that there is a common record for each grouping so the query result before the pivot will look like:

pivot_id  customer_name    order_date        price
1         Company A        2009-02-01        800 
2         Company A        2009-03-01        100
3         Company A        2009-04-01        200
4         Company A        2009-05-01        300
5         Company A        2009-06-01        500
1         Company B        2009-02-01        100
2         Company B        2009-02-01        800
3         Company B        2009-04-01        200
4         Company B        2009-05-01        300
5         Company B        2009-06-01        500

哪种SQL语句将为每次购买生成一个自动记录号，而且还会从每个customer_name重新开始?

What SQL statment will generate an automatic record number for each purchase, but also start over with each customer_name??

使用SQL代码生成器在Access 2007中工作.如果解决方案与Access兼容，那就太好了.

Working in Access 2007 with SQL code builder. Would be great if solution is compatible with Access.

很抱歉，长度不正确.预先感谢.

Sorry for the length. Thanks in advance.

推荐答案

另一个相关子查询如何?将您的查询保存为Query1(您可能要从此中间Query中删除ORDER BY子句-只需对最终结果集进行排序):

How about another correlated subquery? Save you Query, say as Query1 (you probably want to remove the ORDER BY clause from this intermediate Query -- only need to sort the final resultset):

SELECT Q1.customer_name, Q1.order_date, Q1.price, 
       (
        SELECT COUNT(*) + 1
          FROM MyQuery AS Q2
         WHERE Q2.customer_name = Q1.customer_name
               AND Q2.order_date < Q1.order_date
       ) AS pivot_id
  FROM MyQuery AS Q1;

这篇关于枢轴的SQL唯一值编号顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！