This topic has been archived. It cannot be replied.
-
工作学习 / 专业技术讨论 / a SQL问题, 与大侠们共同探讨. I'm thinking about this question for a while, couldn't get a One-Statement solution. With loop structure, it's very easy, but is there a One-Statement solution? Please take a look. Thanks.
-brother-pooh(Pooh哥哥);
2006-11-10
{1404}
(#3313166@0)
-
tested on Oracle, but should be generic.update sample_table t
set t.Col2 =
(SELECT t2.Col2
FROM sample_table t2
where t2.Col1 = t.Col1 - 1
)
where t.Col2 IS NULL;
-zdq(zdq);
2006-11-10
{130}
(#3313200@0)
-
Seems something wrong....
-zdq(zdq);
2006-11-10
(#3313204@0)
-
Correctedupdate sample_table t
set t.t_value = (SELECT t2.t_value
FROM sample_table t2
where t2.Col1 = (select max(t3.Col1)
from sample_table t3
where t3.t_value IS NOT NULL
AND t3.Col1 < t.Col1))
where t.Col2 IS NULL;
-zdq(zdq);
2006-11-10
{383}
(#3313216@0)
-
me too……but I don't like it
-newkid(newkid);
2006-11-10
(#3313226@0)
-
?
-brother-pooh(Pooh哥哥);
2006-11-10
(#3313262@0)
-
The query is fine. What is the problem?
-looi500(looi);
2006-11-10
(#3313314@0)
-
Great answer! but a misspelling: t_value and Col2 should be the same column
-digitworm(digitworm);
2006-11-10
(#3313324@0)
-
You are right, I used t_value for testing, forgot to change all of them back to Col2. Thanks!
-zdq(zdq);
2006-11-11
(#3314344@0)
-
This answer is straightforward, and I thought it was not efficient enough. But after looking at the explain plan I had to change my mind, the optimizer is surely smarter than I am.
-newkid(newkid);
2006-11-10
(#3313348@0)
-
Check it outSELECT COALESCE (t1.col2,
SELECT t2.col2
FROM ts t2
WHERE t2.col2 IS NOT NULL
AND t2.col1 < t1.col1
ORDER BY col1 DESC
FETCH FIRST 1 ROWS ONLY,
-99999)
FROM ts t1;
-886xyz(cqcq);
2006-11-10
{321}
(#3313212@0)
-
what about updates? brother bear wants to update his table.
-newkid(newkid);
2006-11-10
(#3313351@0)
-
See #3313843
-886xyz(cqcq);
2006-11-11
(#3314385@0)
-
It's possible in a read-consistent DB like Oracle. The answer I come up with now is pretty ugly so I won't post it here.
-newkid(newkid);
2006-11-10
(#3313217@0)
-
mine .....test in MS SQL2000.....And I do not like it either.Select t1.[id],max(t2.[type])
from test1 t1 , (select [id], [type] from test1
where [type] is not null
) t2
where t1.[id] > = t2.[id]
group by t1.[id]
order by t1.[id]
-hard20(hard20);
2006-11-10
{189}
(#3313286@0)
-
logically wrong: where t1.[id] > = t2.[id]. in LZ's sample data, you will get 'TDF' for all rows
-newkid(newkid);
2006-11-10
(#3313368@0)
-
why make it so complicated? it would be hard to maintain it.
-sunday8(sunday8);
2006-11-10
(#3313801@0)
-
My try:update T
set col2 = (select top 1 t2.col2 from T t2 where t2.col1 < t.col1 and t2.col2 is not null order by t2.col1 desc)
where t.col2 is null
-shz(shz);
2006-11-10
{144}
(#3313843@0)
-
Excellent answer to MS-SQL!
-brother-pooh(Pooh哥哥);
2006-11-13
(#3317111@0)
-
用游标做只要扫描一遍，用SQL实现则要反复扫描表, 这就是为什么我觉得不满意的地方
-newkid(newkid);
2006-11-11
(#3314853@0)
-
可以用OLAP函数，在Oracle有First_Value,这些对查询make sense,对update意义不大.
-886xyz(cqcq);
2006-11-11
(#3315264@0)
-
在11/12月的Oracle杂志上Tom有解答，用的是Merge和Last_value ignore nulls.对于空值太多的表,这样也行,但没必要全更新.
-886xyz(cqcq);
2006-11-15
(#3322078@0)
-
有URL吗？谢谢！
-newkid(newkid);
2006-11-15
(#3322484@0)
-
看完了，实在是高! 我以前不知道有 IGNORE NULLS
-newkid(newkid);
2006-11-15
(#3322612@0)
-
使用SQL Update比使用CURSOR好. SQL UPDATE也只要扫描一遍. 它的SUB QUERIES都会用到INDEX(假设COL1有INDEX,COL2没有INDEX). 用CURSOR就会把整个TABLE的COL1和COL2读到程序里, 这就有TRAFFIC OVERHEAD.
-looi500(looi);
2006-11-13
(#3317715@0)
-
SQL UPDATE效率最高，但内部也是用游标实现的。SUB QUERY虽然用到INDEX,但相当于每一行都要作一次SELECT, 既扫描了索引又扫描了表。如果CURSOR是在存储过程里实现的，我们可以认为数据都没有离开DB的BUFFER，就没有NETWORK TRAFFIC了。
-newkid(newkid);
2006-11-15
(#3322475@0)
-
挺撅的
-looi500(looi);
2006-11-15
(#3322512@0)
-
Thanks to DX: zdq, newkid, looi500, 886xyz, hard20, shz, digitworm and sunday8,etc. Excellent answers and discussions! Just one more question to discuss... ?SHZ gave an excellent answer to MS-SQL! I really like the idea as it solved with only one level sub query.
Unfortunately, "ORDER BY" is not allowed in Oracle sub-query, is there a similar sql in Oracle? Update the table with only one level inner select? (like shz's)
The following is SHZ's solution to MS-SQL:
-- --
update T
set col2 = (select top 1 t2.col2 from T t2 where t2.col1 < t.col1 and t2.col2 is not null order by t2.col1 desc)
where t.col2 is null
;
-brother-pooh(Pooh哥哥);
2006-11-13
{475}
(#3317155@0)
-
In Oracle, you can't do ORDER BY and rownum < xx in one level queryYou have to use order by first, then use rownum < xx to get the TOP xx sorted records
-zdq(zdq);
2006-11-13
{85}
(#3317225@0)