This topic has been archived. It cannot be replied.
-
工作学习 / IT技术讨论 / 闲来无事, 出道ORACLE SQL的题考考大家. 这是在下几个月前工作中遇到的问题, 看似简单, 其实不然.Table T has a few columns. Column A in T is not unique. Write a SQL statement returns all the rows in T where column A value is non-unique, and group by A. Please note, you can write a complex SQL statement, but no functions, procedures, and triggers are allowed.
For instance, I have table T:
A B
1 B1
2 B2
3 B3
2 B4
3 B5
3 B6
4 B7
after running the SQL, the result is
A B
2 B2
2 B4
3 B3
3 B5
3 B6
-contact(Contact);
2001-11-13
{429}
(#260222@0)
-
Correction"no functions, procedures, and triggers are allowed" should be "no user defined functions, procedures, and triggers are allowed". Built in functions like AVG, MAX, DECODE, COUNT etc. are allowed.
-contact(Contact);
2001-11-13
{200}
(#260228@0)
-
为什么只能用一句STATEMENT?用PROCEDURE是很好做的
-bigrice(大米);
2001-11-13
(#260233@0)
-
That is the difficult part. Because the stupid third party software only takes statement.
-contact(Contact);
2001-11-13
(#260241@0)
-
answer!SELECT * FROM T
WHERE A IN (
SELECT A FROM T
GROUP BY A HAVING COUNT(A)>1)
-cloud2001(坐看云起);
2001-11-13
{78}
(#260246@0)
-
哈哈, 和我的一样. 其实也不太难.
-contact(Contact);
2001-11-13
(#260249@0)
-
a piece of cake!
-cloud2001(坐看云起);
2001-11-13
(#260252@0)
-
But since you used sub-query, you lose the benefits of index.
-contact(Contact);
2001-11-13
(#260258@0)
-
DX, is that possible to get the data set without sub-query? That is beyond my ablity, please tell me. Thanks.
-cloud2001(坐看云起);
2001-11-13
(#260268@0)
-
Sorry about late. I was answering a phone call.This one seems working:
select T2.A, T1.B from T T1, T T2
where T1.A = T2.A group by T2.A, T1.B having count(T2.A)>1
-contact(Contact);
2001-11-13
{121}
(#260300@0)
-
Graceful!I tried this way this evening. But after several minutes I gave up. I find it difficult for me to get focused at home. Yeah, the performance could be bad when using sub-query, especially when the data volume is large.
By the way, I like to write join conditions in the JOIN clause, instead of WHERE. I feel that things are clearer in this way.
Thanks! I've certainly learnt something today.
-abba(Spitfire);
2001-11-14
{397}
(#260691@0)
-
Thank you! We all learned something! I am really glad to see that.
-contact(Contact);
2001-11-15
(#262832@0)
-
So do I. : )
-abba(Spitfire);
2001-11-15
(#262892@0)
-
How come you are named as "walk to the edge of fortune" -- "XING DAO SHUI JIN SHU"? passing away from a luck of falling into love?
-lottery(©_©);
2001-11-14
(#260738@0)
-
it is amazinghi,nice to meet you,we have the same name,can you tell me why you want to use this name,you can send email to zhenghao@gosympatico.ca.
-zuokanyunqi(坐看云起);
2001-11-15
{134}
(#263049@0)
-
不过是一般的SQL的问题罢了。
-checkme(checkme);
2001-11-13
(#260254@0)
-
对, 但在当时下一开始让我们的DBA写, 居然写不出. 所以在下只能自己写. 在此出题纯属好玩.
-contact(Contact);
2001-11-13
(#260262@0)
-
让DBA写SQL有点象让网管写程序，有点不相干。
-checkme(checkme);
2001-11-13
(#260273@0)
-
在北美做DBA这么简单的东东都不知道哪行!. 不过你也能看出去年硅谷雇人多么机不择食, 什么烂人都能当然DBA.
-contact(Contact);
2001-11-13
(#260305@0)
-
不敢苟同。如果他写不出这个SQL STATEMENT，我会怀疑他对数据库的理解，进而影响他对数据库结构的设计。
-abba(Spitfire);
2001-11-13
(#260308@0)
-
我的见解一看就是没有开发经验，这是最最基本的东西，数据转换的时候经常用。加拿大这地总养些动嘴皮子的人，不过这也难怪，前几年IT工作太好找了
-sameway(sameway);
2001-11-13
{128}
(#260617@0)
-
同意
-benbendan(笨笨蛋);
2001-11-18
(#266311@0)
-
Contact, Can you help me explain the following question? Thank you.select T2.A, T1.B from T T1, T T2
where T1.B = T2.B group by T2.A, T1.B having count(T2.A)>1
I changed 'where' , before the 'having' the result is same as the old one, but after 'having', the result is totally different. So what's the function of 'HAVING'? I think it just process the data before it, right?
-hongri(hongri);
2001-11-14
{322}
(#260982@0)
-
我觉的可以用一个减操作，但好久未用oracle,忘了名字。取出全部记录，然后减A为单一值的记录，行不？云MM的SQL嵌套速度有很大影响，如果记录数一多，虽然简洁.
-rabbitbug(兔八哥);
2001-11-14
{54}
(#260724@0)
-
Will this work?
select A, B from (select A, B, lag(A, 1) over (order by A) as A1, lead(A, 1) over (order by A) as A2 from T) where A = A1 or A = A2
-fromztoa(fromZtoA);
2001-11-14
(#260764@0)
-
Did you test the statement for so many rows?something wrong?I tried a table with 33 rows, almost no difference(20 msec--subquery, 40 msec--your statement).
I tried a table with 480184 rows using the sub-query one, it took 35 sec to get back data without index, 23 sec to get back with index, but I tried the one you provided, I waited for at least half an hour, still no data come back, I have to kill the process.
-cloud2001(坐看云起之山尽水穷);
2001-11-14
{356}
(#261113@0)
-
Can you answer my question?
-hongri(hongri);
2001-11-14
(#261165@0)
-
My answer are select * from T T1 where exists(select A from T where A=T1.A group by A having count(1)>1); or
select * from T T1 where exists(select A from T group by A having count(1)>1 and A=T1.A);But what's the difference?
-cyt(会飞的鹿);
2001-11-14
{27}
(#261114@0)
-
If you change into and B=a.B, you can find different ,but I don't know why
-hongri(hongri);
2001-11-14
(#261180@0)
-
select T.A, T.B from T join (select A,count(B) CNT from T group by A Having count(B) > 1 ) Z on T.A = Z.A
-tianqi21();
2001-11-14
(#261202@0)
-
Why you guys make it so complicated?
select * from t group by a having count(*) >1 order by a
-aabbcc(abc);
2001-11-14
(#261370@0)
-
哥们，你试过吗？其实cyt的答案，我最喜欢，性能最好， 两者并无不同。还可以：select * from T t1 where exists(select 1 from t t2 where t1.a=t2.a having count(0)>1)；
-onewayticket(onewayticket);
2001-11-14
(#261589@0)
-
aabbcc hasn't got the point yet. But,do you really think cyt's solution is better than that of #260300, in terms of performance? To my knowledge, join is much better than sub-query, especially when there is usable index.
-abba(Spitfire);
2001-11-14
{184}
(#261707@0)
-
I am pretty sure. 请看分析。
-onewayticket(onewayticket);
2001-11-14
{3083}
(#261831@0)
-
高人！老兄是真正的DBA吧。我是做开发的，用了4，5年SQL，也搞过performance tuning，但没有系统研究过理论。今天借这个机会请教一下：怎样分析execution plan？有没有好的网站介绍一下？先行谢过！
不过，我的经验仍然顽固地告诉我：#260300会用到‘T_I'，并且join好于sub_query。我现在没条件，你能不能弄几万条记录试一下？（我的经验以前都挺管用啊！）
-abba(Spitfire);
2001-11-14
{329}
(#261909@0)
-
再看看这个
-onewayticket(onewayticket);
2001-11-15
{2441}
(#262617@0)
-
谢指教！你的解释真详细，多谢！可惜我手头没有可用的系统，没法试。我一直用DB2，不知道有没有类似的分析工具，你是否碰巧也知道？：）
-abba(Spitfire);
2001-11-15
{119}
(#262662@0)
-
db2exfmt, db2expln, visual explain, db2vexp, etc
-onewayticket(onewayticket);
2001-11-15
(#262981@0)
-
Thanks!
-abba(Spitfire);
2001-11-15
(#263022@0)
-
谢谢你的论据，我也只是凭经验。很希望和大家多交流，特别是几种不同database 的比较。我在用Oracle 和SQL Server。希望了解一些DB2和Sybase. Please send me mail, I'd like to share you my phone number.
-cyt(会飞的鹿);
2001-11-14
(#261955@0)
-
YES. Is there any problem with that statement?
-aabbcc(abc);
2001-11-15
(#262875@0)
-
why no one answer my question. Thank you DXSselect T2.A, T1.B from T T1, T T2
where T1.B = T2.B group by T2.A, T1.B having count(T2.A)>1
I changed 'where' , before the 'having' the result is same as the old one, but after 'having', the result is totally different. So what's the function of 'HAVING'? I think it just process the data before it, right?
-hongri(hongri);
2001-11-15
{322}
(#262415@0)
-
能列出我你的sql语句吗?
-onewayticket(onewayticket);
2001-11-15
(#262622@0)
-
I answered part of your question in #262621. As far as HAVING, please get any SQL book and look into the chapter of GROUP BY for greater details.
-contact(Contact);
2001-11-15
(#262626@0)
-
WOW, IT IS MUCH MORE THAN I EXPECTED!
-contact(Contact);
2001-11-15
{1519}
(#262621@0)
-
Hi! Like to disscuss any questions with you.When I saw your question, I just want to answer you as soon as possible, yes, I admit at that time I did not even think about performance, so I am not a dba either. :) Although it seems a simple question, as you and abba said, it is more complicated in real life, but I really learn much from it. I really appreciate it. You make rolia IT more like a technical forum, which is the place I am looking for.
-cloud2001(坐看云起之山尽水穷);
2001-11-15
{404}
(#262630@0)
-
Absolutely
-contact(Contact);
2001-11-15
(#262673@0)
-
Do you know any bbs or community which discuss application server or java middleware solution, I can not find a good one! Thanks.
-cloud2001(坐看云起之山尽水穷);
2001-11-15
(#262702@0)
-
I don't think any is really good. Try google to search. We wrote our own framework at our workplace instead of using an application server. For J2EE, you may take a look Orion server.
-contact(Contact);
2001-11-15
(#262833@0)
-
要是T－SQL就太容易了：
"select T1.A,T1.B from T T1 join T T2 ON T1.A=T2.A anf T1.B <> T2.B Group By T1.A,T1.B"
-benbendan(笨笨蛋);
2001-11-18
(#266310@0)
-
更正一下：select T1.A,T1.B from T T1 join T T2 on T1.A=T2.A Group By T1.A , T1.B Having count(T1.A)>1
-benbendan(笨笨蛋);
2001-11-18
(#266339@0)