query 求助 - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

query 求助

query 求助# Database - 数据库

cd2009-02-06 08:02

1 楼

假设表test内容如下，
col_A col_B

B*g2009-02-06 08:02

2 楼

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 !!!!!!!!!!!!!!!!!!!!!!

【在 cd 的大作中提到】

: 假设表test内容如下，
: col_A col_B

d*h2009-02-06 08:02

3 楼

Thought you can do it, this is still a very stupid requirement, in relative
database, row sequence is not relevant.

【在 cd 的大作中提到】

: 假设表test内容如下，
: col_A col_B

n*62009-02-06 08:02

4 楼

我以前遇到过类似的问题，不敢用select top。高人指点一下select top如何产生？
msdn说top在order by时按顺序产生.高人解释一下。
If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.
MSDN说top在insert, update时是随机的。
The rows referenced in the TOP expression used with INSERT, UPDATE, MERGE,
or DELETE are not arranged in any order. TOP n returns n random rows.
http://msdn.microsoft.com/e

【在 B*****g 的大作中提到】

: 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

B*g2009-02-06 08:02

5 楼

we have this kind of requirement many times in oracle.

relative

【在 d*h 的大作中提到】

: Thought you can do it, this is still a very stupid requirement, in relative
: database, row sequence is not relevant.

cd2009-02-06 08:02

6 楼

谢谢beijing，是oracle。
但是你的query在col_a里面没有4出现的时候没有返回值。

【在 B*****g 的大作中提到】

: 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

B*g2009-02-06 08:02

7 楼

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

【在 cd 的大作中提到】

: 谢谢beijing，是oracle。
: 但是你的query在col_a里面没有4出现的时候没有返回值。

cd2009-02-06 08:02

8 楼

Yes you are right. The requirment actually is asking to return any col_b
just for one row if there is any . Maybe I didn't say it clearly. thank you!

relative

【在 d*h 的大作中提到】

: Thought you can do it, this is still a very stupid requirement, in relative
: database, row sequence is not relevant.

cd2009-02-06 08:02

9 楼

nice!
好，马上奉上包子请稍等。。。。

【在 B*****g 的大作中提到】

: 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

B*g2009-02-06 08:02

10 楼

赫赫，还是大包子

【在 cd 的大作中提到】

: nice!
: 好，马上奉上包子请稍等。。。。

cd2009-02-06 08:02

11 楼

不知道行情，不嫌少就成赫赫

【在 B*****g 的大作中提到】

: 赫赫，还是大包子

b*e2009-02-06 08:02

12 楼

Beijing is always hungry. ;-)

【在 B*****g 的大作中提到】

: 赫赫，还是大包子

c*e2009-02-06 08:02

13 楼

does this work for you?

【在 cd 的大作中提到】

: nice!
: 好，马上奉上包子请稍等。。。。

B*g2009-02-06 08:02

14 楼

不work他能发包子吗

【在 c*******e 的大作中提到】

: does this work for you?

B*g2009-02-06 08:02

15 楼

千千姐是谁？

c*e2009-02-06 08:02

16 楼

你没有看错，就是她，哈哈哈

B*g2009-02-06 08:02

17 楼

kick。05才开始学db

接触
个女

【在 c*******e 的大作中提到】

: 你没有看错，就是她，哈哈哈

h*n2009-02-06 08:02

18 楼

expression percent of rows, ordered by the ORDER BY clause are returned. If
the query has no ORDER BY clause, the order of the rows is arbitrary.
你觉得insert/update的时候顺序重要吗？order by的时候呢？

【在 n********6 的大作中提到】

: 我以前遇到过类似的问题，不敢用select top。高人指点一下select top如何产生？
: msdn说top在order by时按顺序产生.高人解释一下。
: If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.
: MSDN说top在insert, update时是随机的。
: The rows referenced in the TOP expression used with INSERT, UPDATE, MERGE,
: or DELETE are not arranged in any order. TOP n returns n random rows.
: http://msdn.microsoft.com/e