doufei8691
doufei8691
2012-06-02 13:20

在mysql查询中计算订单位置

已采纳

I am currently designing an invoicing system where invoices can be allocated to seperate projects. An invoice will only ever be allocated to one project.

At present I have a mysql table which I want to query with the invoice number, retrieve all of the invoices for the respective project that invoice is for, order them by the creation date and then(this is the bit im stuck on) based on the invoice number I will have queried with, calculate the number that invoice is out of the total invoices for that project.

To add some clarity I mean the following

URN_ID        URN_Project_ID    URN_Date
1             1                 2012-03-17
2             3                 2012-03-18
3             2                 2012-03-21
4             1                 2012-03-21
5             2                 2012-04-05
6             1                 2012-04-08
7             3                 2012-04-15
8             2                 2012-04-24
9             1                 2012-05-02
10            3                 2012-05-10

Lets say that I have queried with invoice number 6 and therefore the project is project 1. In essence I would be narrowing the information to the following

URN_ID        URN_Project_ID    URN_Date
1             1                 2012-03-17
4             1                 2012-03-21
6             1                 2012-04-08
9             1                 2012-05-02

We can see from above that I have a total of 4 invoices however I want a way to return that invoice 6 is number 3 out 4 invoices. The purpose of this is so that if someone prints a copy invoice in the future it is clear to the person viewing it was invoice 3 on that project. Finally I don't need to detail the total number of invoices on my document only the location within the total number.

Any help with this would be much appreciated as I have been searching most of the morning for a solution I can piece together with no avail. As a fall-back I can always add another column to my table and at input stage do a mysql count and then add the total number to date into the column but I would far rather learn something new and keep my table to a minimum.

Thanks in advance for any solutions or help.

Alan

EDIT

OK some additional information.

  • My table I am pulling the data from is called: urn
  • At present my table has 5 columns called URN_ID, URN_PROJECT_ID, URN_Date, URN_Due_Terms, URN_Doc_Type

N.B. I have updated the column data above to reflect my actual column Names

So far I have tried:

$query2 = "SELECT  @curRow := @curRow + 1 AS row_number, iv.* FROM urn iv JOIN (SELECT @curRow := 0) r WHERE iv.URN_Project_ID='$urn_project_id'";
$res2 = mysql_query($query2);
$data2 = mysql_fetch_array($res2);
echo $data2['row_number'];

This however only returns 1 as the value regardless of which URN_ID I have displayed.

I have no real knowledge of statements using @ sign and as such am at a loss how to debug it.

Any help would be good.

Cheers. Al.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • doubi4491 doubi4491 9年前

    You basically want to add a row number onto your result set, so something like:

    SELECT  @curRow := @curRow + 1 AS row_number,
            iv.*
    FROM    invoices iv
    JOIN    (SELECT @curRow := 0) r
    WHERE   iv.project_id=1;
    

    This should return:

    row_number invoice id    project_id    invoice_date
    1          1             1             2012-03-17
    2          4             1             2012-03-21
    3          6             1             2012-04-08
    4          9             1             2012-05-02
    
    点赞 评论 复制链接分享
  • duanquanzhi5560 duanquanzhi5560 9年前

    Although it may look a little uncomfortable at first, this is probably the best way.

    mysql> SET @rank=0;
    
    mysql> SELECT @rank:=@rank+1 AS rank,OtherColumns as Column FROM Invoices .....
    

    The @rank increment will do what you want. You should set the @rank variable before hand; there may be a way to do a case then end if rank is set or not like (not tested):

    mysql> SELECT case @rank when NULL then @rank=0 else @rank:=@rank+1 end as rank FROM ....
    
    点赞 评论 复制链接分享

为你推荐