I don't understand how this query is able to return a row number for repeating IDs. Can big cow explain how? table:
B*g
2 楼
这个query能用group by吗?
【在 a*******t 的大作中提到】 : I don't understand how this query is able to return a row number for : repeating IDs. : Can big cow explain how? : table:
a*t
3 楼
it works I just don't understand how...
【在 B*****g 的大作中提到】 : 这个query能用group by吗?
c*d
4 楼
1. which database? Oracle? 2. The sql statement cannot be executed in Oracle
c*d
5 楼
In Oracle, it should be, select orderid,customername, rank() over(partition by orderid order by customername) from "order" ;
j*n
6 楼
it looks like T-SQL statement... However, where is the ProductID from? you did not give all of the code ba?
B*g
7 楼
should be sql server as table name is [Order] But when I count ( and ), there are 2 ( and 3 ), can not believe it can compile. SELECT OrderID , CustomerName , RowNumber = (SELECT COUNT(*) FROM [Order] b WHERE b.OrderID = a.OrderID AND b.ProductID < a.ProductID) FROM [Order] a) GROUP BY OrderID ORDER BY OrderID
【在 c*****d 的大作中提到】 : 1. which database? Oracle? : 2. The sql statement cannot be executed in Oracle
n*w
8 楼
这种用法简直是竭泽而渔,如果table大的话。
a*t
9 楼
yeah, M$ SQL server sorry, replace productID with customer name
【在 j*****n 的大作中提到】 : it looks like T-SQL statement... : However, where is the ProductID from? you did not give all of the code ba?
a*t
10 楼
OK, here is a full working example: CREATE TABLE [dbo].[testing]( [OrderID] [varchar](50) NULL, [CustName] [varchar](50) NULL ) and then insert some sample data into the table 123, John 567, Joe 123, Mary 123, Peter and then here is the query select OrderID , max(case rowno when 0 then CustName end) + coalesce(max(case rowno when 1 then ', ' + CustName end), '') + coalesce(max(case rowno when 2 then ', ' + CustName end), '') + coalesce(max(case rowno when 3 then ', ' +