s**********e
发帖数: 326 | 1 我来贴个general的code
void diagnalPattern(char* str, int rowNum){
int charNum = 0;
for(int i = 0; i < strlen(str); i++){
if(isValidChar(str[i])){
charNum++;
}
}
int colNum = (charNum + rowNum - 1) / rowNum;
char** arr;
arr = new char*[rowNum];
for(int i = 0; i < rowNum; i++)
arr[i] = new char[colNum];
for(int i = 0 ; i < rowNum; i++)
for(int j = 0; j < colNum; j++)
arr[i][j] = ' ';
int curPos = 0;
... 阅读全帖 |
|
m********u
发帖数: 14 | 2 A recursive solution.
public static void createMatrix(
string s,
int startPosInOriginalString,
char[,] data,
int curX,
int curY,
int rowNum,
int colNum)
{
curX = curX - rowNum + 1;
if (curX < 0)
{
curX = 0;
}
if (curY >= rowNum)
{
curY = rowNum - 1;
}
for (int ... 阅读全帖 |
|
B*****n
发帖数: 135 | 3 rownum is a psudocolumn in the result set, it will only be
incremented after a row has been actually selected (using
all the conditions except the one involving rownum
itself).
What i'm saying is that you cannot directly do a query
like this:
select "what you need" from "your table"
where rownum between 10 and 20;
because you never actully selected anything into the
result set, the rownum will never be greater than 0.
In other words, you want to select something based on the
rownum, while the ro |
|
n*********u
发帖数: 1030 | 4 SELECT state, loan_amount
FROM (
SELECT t1.loan_amount, t1.state, t1.rank, t2.ct
FROM (
SELECT loan_amount, state, (CASE state WHEN @currState THEN @rownum:
= @rownum + 1 ELSE @rownum := 1 AND @currState=state END) + 1 as rank, @
ctnum:=@ctnum+1 ac ct
FROM table t, (SELECT @rownum := 0, @currState := '', @ctnum:=0) r
ORDER BY state, loan_amount DESC
) t1 WHERE t1.rank = t1.ct * 0.1
) tt
order by tt.rank, tt.ct DESC
limit 10; |
|
p********l
发帖数: 279 | 5 You can try to use CTE and row_number:
WITH TableRN AS
(
SELECT ROW_NUMBER() OVER(ORDER BY col1) AS rownum,
col1,
col2
FROM Table1
)
SELECT rownum, col1, col2
FROM TableRN
WHERE rownum BETWEEN n1 AND n2
ORDER BY rownum; |
|
D******n
发帖数: 2836 | 6 assuming your data is sorted by v1:
data _temp_;set yourdata;by v1;retain rownum 0;
if first.v1 then rownum=0;rownum=rownum+1;run;
proc sort ;by v1 descending v3;run;
data _temp_;set _temp_;by v1;if first.v1;drop v2;run; |
|
y********o
发帖数: 2565 | 7 The one you gave below gives an error:
SQL> select salary from emp order by salary desc where rownum=2;
select salary from emp order by salary desc where rownum=2
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
I tried this:
SQL> select salary from emp where rownum = 2 order by salary desc ;
Which returns:
no rows selected
__Note__: emp is just a synonym of hr.employee. |
|
s**o
发帖数: 584 | 8 Actually it is not. Rownum are a pseudocolumn (not a real column). It will
be available after the query statement is processed. You may try this query
to get your result.
select *
from ( select e.*, rownum rnum
from ( select emp.*
from emp
order by salary desc ) e
where rownum <= 2
)
where rnum = 2; |
|
k*n
发帖数: 150 | 9
肯定是nxn吗？有可能是nxm吗？后者似乎要麻烦一些
比如这样的
1 2 3 4 5 6
14 15 16 17 18 7
13 12 11 10 9 8
input是什么？如果是一维数组表示法的话，还得转换一下坐标，虽然也不算什么麻烦事
void printMatrix(const int* matrix, int colNum, int rowNum) {
int direction = 0; // 0:>; 1:v; 2:<; 3:^;
int hLen = colNum - 1;
int vLen = rowNum - 1;
int x = 0;
int y = 0;
while (hLen > 0 && vLen > 0) {
printVector(matrix, colNum, x, y, direction, (direction % 2 == 0) ? hLen
: vLen);
direction = (direction + 1) % 4;
if (direction == 0) {
hL... 阅读全帖 |
|
a*********e
发帖数: 35 | 10 多谢各位的指点,偶在一个newsgroup得到了一个答案,已经验证过了,
可以用应该还不错,贴出来大家看看,但是里面的实现好象没有在Oral
ce里见过. 麻烦那位帮我解释一下.
/***********
The answer is:
select "what you need" from (
select rownum line, "what you need" from "your table" )
where line between "start of subset" and "end of subset"
.....
E.g.:
select name, firstname from (
select rownum line, name, firstname from names)
where line between 10 and 20
order by name;
/*********************** |
|
q**1
发帖数: 193 | 11 这个我就不知道了：-）...重来没摸过Oracle，只是在我的
SQL Cookbook上看到一个例子，觉得可能你可以用的上：Page-8
In Oracle, place a restriction on the number of rows
returned by restricting ROWNUM in the WHERE clause:
select *
from emp
where rownum <= 5 |
|
B*****g
发帖数: 34098 | 12 除了oracle好像都可以用top。
oracle先排序再用rownum。
比赛应该比其他容易，因为基本上不会有同时比赛的。
oracle：
SELECT MAX(score)
FROM (SELECT score
FROM (SELECT NVL(score,0)
FROM table
WHERE NVL(playtime, 0) > 0
AND playdate IS NOT NULL
ORDER BY playdate DESC)
ROWNUM <= 10) |
|
B*****g
发帖数: 34098 | 13 So here you suppose when you run
"SELECT A FROM table"
the return will be
1
2
3
...
???
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT NVL ((SELECT A
FROM table
WHERE ((B != 0) OR (B IS NULL))
AND ROWNUM = 1), (SELECT A
FROM (SELECT ROWNUM seqno,
A
FROM table)
WHERE seqno = (SELECT |
|
B*****g
发帖数: 34098 | 14 SELECT a.*
FROM (SELECT ROWNUM rn,
c1.*
FROM tab1 c1) a,
(SELECT CEIL (DBMS_RANDOM.VALUE (0, 4427396)) rn
FROM tab1
WHERE ROWNUM <= 4000000) b
WHERE a.rn = b.rn
4427396 record in tab1
sample 4000000
total 6 mins in develop environment, as for the production db, usually 10X
faster, so time should be around 1 min. |
|
s******s
发帖数: 508 | 15 select
CusipCol
, case when ValueCol is null and nextValue is not null then nextID else
IDCol end IDCol
, case when ValueCol is null and nextValue is not null then nextValue else
ValueCol end ValueCol
from
(
select CusipCol, IDCol, ValueCol
,lead(ValueCol) over (partition by CusipCol order by IDCol) NextValue
,lead(ValueCol) over (partition by CusipCol order by IDCol) NextID
,row_number() over (partition by CusipCol order by IDCol) rownum
from mytable
) x
where rownum = |
|
B*****g
发帖数: 34098 | 16 主要是处理不是number的比较烦，oracle 11g写了一个，纯属娱乐
***只有一列***
WITH tmp AS
(SELECT XMLTYPE (
DBMS_XMLGEN.getxml ('SELECT 1, ''2'', ''3.0'', '' 4.2 '',
sysdate, ''abcd'' FROM DUAL'
)
) myxml
FROM DUAL)
SELECT SUM(v)
FROM tmp a,
XMLTABLE ('/ROWSET/ROW/*' PASSING myxml
COLUMNS v VARCHAR2(1000) PATH '.')
WHERE REGEXP_LIKE(TRIM(v), '^[0-9]\d{0,2}(\.\d{1,2})?%?$')
GROUP BY 1
***整个table***
WITH tmp AS
(SELECT ROWNUM rn,
... 阅读全帖 |
|
M***0
发帖数: 1180 | 17 如果是存在sql table里，那最简单的，如果你的数据量小的话
e.g. Oracle: select xxx from ttt where xxx like ‘fa%’ where rownum <5
返回前5条fa开头的记录（在xxx上建个Index）
如果要按字母顺序，在rownum<5前加order by xxx
如果是要按popularity来排，加order by popularity
不用sql table的话，就是自己用代码建b-tree或binary-tree，把output写到硬盘，实
际上这个查询会是一直用的，所以是长期在内存里的，我们的内存是100GB
这两天我又想了想，今天和全组的人讨论了一下，决定用建几棵树分别记录单字母，双
子母，三字母,etc， up to五字母表或六字母表
单字母表
prefix（key） top_5_search
a apple, aol, american airline, amazon, applebees
b bestbuy, boa, bbc, bi... 阅读全帖 |
|
p*a
发帖数: 592 | 18 这个应该不难吧。我只做过取前100个row的，忘了是用rownum还是top 100了。象你要
取300-350也许取了350再reverse取50？ |
|
z****e
发帖数: 54598 | 19 不会吧，你连这个都没做过？
你们数据量才rownum=100就够用了？
对，因为sqlserver当年就是比较恶心的分页
先取出所有的结果，然后删减，一直处理不太好
这还只是top350，如果是top35000怎么办？
我先取出35000再删除前面的35000-350？这个……
为此我还特意看了hibernate的源代码
发现大牛写的代码也是这样折腾
唯一能解决好的是开源的mysql，一个子句搞定
当时我还特意问了做dba的同学
结果同学给我的答复都不是很让我高兴
从那以后我一直没觉得sqlserver好用 |
|
d**e
发帖数: 6098 | 20 来自主题: JobHunting版 - 一道sql select id, name, amount
from (select rownum as index, id, name amount
from c order by amount desc)
where index = 7; |
|
h*******e
发帖数: 1377 | 21 如果 m n 中有一个小于３０的话
for(int rowI = 0; rowI < rowNum; ++ rowI)
for(int dpI = 0; dpI < 1 << colNum; ++ dpI)
for(int colI = 0; colI < colNum; ++ colI)
{
//然后自己转移状态就行了。
３ 种情况 １ 本格占了 。
２ 本格没占 右边突出
３ 本格没占 下边突出
然后相加可能的各种情况就行了。
}
|
|
h*******e
发帖数: 1377 | 22 如果 m n 中有一个小于３０的话
for(int rowI = 0; rowI < rowNum; ++ rowI)
for(int dpI = 0; dpI < 1 << colNum; ++ dpI)
for(int colI = 0; colI < colNum; ++ colI)
{
//然后自己转移状态就行了。
３ 种情况 １ 本格占了 。
２ 本格没占 右边突出
３ 本格没占 下边突出
然后相加可能的各种情况就行了。
}
|
|
B*****n
发帖数: 135 | 23 In oracle:
suppose you already have a table EMP(name Varchar2(30), salary Number);
and you want to add a column called 'id', just do:
SQL> ALTER TABLE EMP ADD (id Number);
if you now want to populate this id column from 1 to the number of existing
employees in the EMP table, just do:
SQL> UPDATE EMP SET id = rownum;
To rename table 'EMP' to 'EMPLOYEE', just do:
SQL> RENAME EMP TO EMPLOYEE; |
|
s***y
发帖数: 1 | 24 我用ROWNUM实现了ORACLE查询结果的分页显示，可是只能实现
页内排序，可是网站上的查询结果肯定是及分页有全排序的，
请教各位大虾如何实现，在MYSQL里有LIMITS语句很容易实现
可在ORACLE里呢？ |
|
j****s
发帖数: 271 | 25 select column
from tab
where your_conditions and ROWNUM = 1; |
|
j***y
发帖数: 87 | 26 oracle:
select rownum, a from table XXX order by a |
|
a****o
发帖数: 37 | 27 rownum does not work with order by, it is the internal row number
try sequence.
but that is not standard sql.
and if it is not standard sql, there are many ways to do this. |
|
n********a
发帖数: 68 | 28 The question can be interpreted by different ways.
I took it mean that find the lastest four dates and
give me every position.
In Oracle 8.1.6 upper, you could do:
select pName from (
select position.pName,
dense_rank() over (order by EP.tDate desc nulls last) dr
from EP, Position
where EP.pID=position.pID and Position.state='Illinois'
) where dr <= 4;
If your database supports inline view and rownum, you could do
select pName from (
select position.pName
from EP, Position
where EP.pID=position. |
|
h******i
发帖数: 133 | 29 这样的话，我能想到的就是每个TABLE 单独弄，然后设 WHERE ROWNUM<1
你最好去ORACLE 的网站问一下，那里都是专家。。。
test
created
index
project
tables. |
|
h******i
发帖数: 133 | 30 it is easy, do like this:
select sss,bbb,...
from
(select sss,bbb,...., a.rownum rown
from M_table) b
where b.rown between 1000 and 2000 |
|
c******j
发帖数: 87 | 31 SQL server doesn't support rownum:( |
|
j**i
发帖数: 419 | 32 Looks like this is not what he means, he wants
everything be returned twice, even count(*) may >2,
right?
in oracle you can use rownum to do this bah.
2 |
|
n********a
发帖数: 68 | 33 Which database are you using?
Different databases will have different answers.
If Oracle, its
select rownum, t.*
from t |
|
q**1
发帖数: 193 | 34 I dont have Oracle, but can you use a query like:
select salary from employees
order by salary desc
where rownum = 2; |
|
y********o
发帖数: 2565 | 35 Yes, yes, this one worked. I think I understand
the logic of rownum now, given your explanation.
Thanks.
Question: How is this one compared with the one
using the row_number() built-in functioin with
regard to performance?
query |
|
B*****g
发帖数: 34098 | 36 NND, 写错了
update A x
set F3 = F3 - NVL((select y.F3 from A y where x.F1=y.F1 and y.F2='y' AND
ROWNUM = 1)， 0）
where x.F2='x'
there
AND |
|
s******r
发帖数: 1524 | 37 thanks,
"top n" does not work. where rownum |
|
B*****g
发帖数: 34098 | 38 Got you, don't know much about mysql.
below is my solution for oracle.
CREATE OR REPLACE
TYPE IDCOLLECTION AS TABLE OF NUMBER;
SELECT t1.*
FROM table t1,
(SELECT COLUMN_VALUE id,
ROWNUM seqno
FROM TABLE (idcollection (1,2,5,4,3))) t2
WHERE t1.id = t2.id
ORDER BY t2.seqno; |
|
B*****g
发帖数: 34098 | 39 why CHOOSECost for (1) is more than (2)?
SELECT *
FROM c
WHERE EXISTS (SELECT 1
FROM a
WHERE a.key = c.key
AND ROWNUM = 1)
Plan
SELECT STATEMENT CHOOSECost: 670,429 Bytes: 87,120,580 Cardinality: 215,
645
4 FILTER
1 TABLE ACCESS FULL C Cost: 23,494 Bytes: 87,120,580 Cardinality:
215,645
3 COUNT STOPKEY
2 INDEX RANGE SCAN UNIQUE INDEX1(***table A***) Cost: 3 B |
|
B*****g
发帖数: 34098 | 40 nod.
Try 730k with 650k sample, insert takes 4.5 mins.
Also tried below, even slower than my procedure, hehe. No idea le.
SELECT *
FROM (SELECT *
FROM tab1
ORDER BY DBMS_RANDOM.VALUE)
WHERE ROWNUM <= 1000 |
|
c*****d
发帖数: 6045 | 41 哪种数据库？ms sql, mysql, oracle?
ms sql: insert into A select top 500 * from B
mysql: insert into A select * from B limit 500
oracle: insert into A select * from B where rownum <=500
first |
|
c**t
发帖数: 2744 | 42 Oracle doesn't have top n query.
select x.salary
from (
select salary, rank() over (order by salary desc null last) as rnk fro
m payroll
) x
where x.rnk < 10;
or
select x.salary from (select salary from payroll order by salary desc)
where rownum < 10; |
|
|
c*****t
发帖数: 1879 | 44 没 rownum，我是用 postgresql :( |
|
B*****g
发帖数: 34098 | 45 oracle:
SELECT (SELECT col_b
FROM test
WHERE col_a = 4
AND rownum = 1) col_b
FROM DUAL
sql server
SELECT (SELECT TOP 1 col_b
FROM test
WHERE col_a = 4) col_b
FA BAO ZI !!!!!!!!!!!!!!!!!!!!!! |
|
B*****g
发帖数: 34098 | 46 it has, the value is NULL.
SELECT NVL((SELECT col_b
FROM test
WHERE col_a = 4
AND rownum = 1), 'fail') col_b
FROM DUAL |
|
y****9
发帖数: 144 | 47 Sorry, don't understand your reply.Don't know how to do it without upper
limit, but if to find next 10 biz days in 2012, I can do this as follows:
SQL>select mydate
2 from
3 (
4 select to_char(sysdate + level, 'YYYY-Mon-DD') mydate, level
5 from dual
6 where mod(to_number(to_char(sysdate + level, 'YYYYMMDD')),3) = 0 --
equivalent to isBIzDay(sysdate+level) =1
7 connect by level < to_date('2012-12-31','YYYY-MM-DD') - sysdate
8 )
9 where rownum <=10;
MYDATE
-----------
2... 阅读全帖 |
|
y****9
发帖数: 144 | 48
Thanks. Learned sth new today.
SQL>select to_char(sysdate + level, 'YYYY-Mon-DD') mydate
2 from dual
3 where mod(to_number(to_char(sysdate + level, 'YYYYMMDD')),3) = 0 --
equivalent to isBIzday(sysdate+level) =1
4 connect by rownum <=15
5 ;
MYDATE
-----------
2012-Jan-09
2012-Jan-12
2012-Jan-15
2012-Jan-18
2012-Jan-21
2012-Jan-24
2012-Jan-27
2012-Jan-30
2012-Feb-02
2012-Feb-05
2012-Feb-08
2012-Feb-11
2012-Feb-14
2012-Feb-17
2012-Feb-20
15 rows selected. |
|
B*****g
发帖数: 34098 | 49 是不是就是说不能用count所有的？
SELECT /*+ First_Rows(1) */ 1
FROM tab a
WHERE ROWNUM = 1
12c的top－N没用过 |
|
l******b
发帖数: 39 | 50 Workaround for Oracle earlier than 12.1
CREATE OR REPLACE TYPE int_row_table AS TABLE OF NUMBER;
/
-- Table Function is actually a parameter view.
CREATE OR REPLACE FUNCTION get_int_table
(
p_start IN NUMBER,
p_num in number
) RETURN int_row_table IS
l_result int_row_table ;
CURSOR C IS
WITH KK AS
(SELECT ROWNUM AS nD FROM dual CONNECT BY LEVEL <=10)
SELECT nD FROM KK WHERE nD BETWEEN p_start AND p_start + p_num
- 1;
BEGIN
... 阅读全帖 |
|
