誰來幫我開來開悄? Interesting SQL query# Database - 数据库
a*t
1 楼
I don't understand how this query is able to return a row number for
repeating IDs.
Can big cow explain how?
table:
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 的大作中提到】
: 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吗？
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
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" ;
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?
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
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?
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 ', ' +
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 ', ' +
a*t
11 楼
yeah, the author mentioned is this not good for big tables without
modification
http://www.thescripts.com/forum/thread580103.html
【在 n*w 的大作中提到】
: 这种用法简直是竭泽而渔，如果table大的话。
modification
http://www.thescripts.com/forum/thread580103.html
【在 n*w 的大作中提到】
: 这种用法简直是竭泽而渔，如果table大的话。
相关阅读
银行用啥数据库?请问ASP如何DSN-less连接Oracle数据库INDEX请教怎么自动UPDATE SYBASE: truncation point 问题请问那里有PLSQL的学习文档的下载地址？how to get rid of the inquiry window[转载] 求教：怎样自动记录聊天室的聊天内容。急！各位大侠，请教一个问题这几天Oracle 92I经常出现ORA-00600的错误informix 和oracle数据库的比较求助。I forget my oracle admin password in XPSQLServer2000 Database Structure Populat[转载] Popular Software Titles for FREE请问一个SQL语句的优化问题oracle websites-welcome BuChong急急问个弱问题请教传数据问题[转载] 一个排序的问题有没有Server Performance Tuning 的人才？