| Mark's profileElectron CloudPhotosBlogLists | Help |
|
June 11 Geeks: Oracle database query discussion前两天有位同学突然出了一个有关oracle database查询的问题,感觉很有趣:
问题嘛,就是一个DB的表,有
项目1 项目2 项目3 项目4
A 1 XXX1 AB
A 1 XXX2 AB
。。。
这样的项目,希望能够做出一个查询产生
项目1 项目2 项目3 项目4
A 1 XXX1 AB
XXX2
很有意思的问题,我先说个大概想法,抛砖引玉一把。需要先按照分组列进行group by然后选出每一个组里面的第一行的rowid,然后把这个结果集和原来的结果集进行join。
先做个oracle 测试:
drop table tab_test;
create table tab_test
( GRP VARCHAR2(10), COL1 VARCHAR2(10), COL2 VARCHAR2(10) ); insert into tab_test values('G1','R1', 'Q1');
insert into tab_test values('G1','R2', 'Q2'); insert into tab_test values('G1','R3', 'Q3'); insert into tab_test values('G2','R4', 'Q4'); insert into tab_test values('G2','R5', 'Q5'); insert into tab_test values('G3','R6', 'Q6'); commit;
SQL> select * from tab_test;
GRP COL1 COL2
---------- ---------- ---------- G1 R1 Q1 G1 R2 Q2 G1 R3 Q3 G2 R4 Q4 G2 R5 Q5 G3 R6 Q6 我们有G1,G2,G3三个组,选择每一组的第一行
select GRP,MIN(COL1) MCOL1,MIN(COL2) MCOL2
from tab_test group by GRP order by GRP; GRP MCOL1 MCOL2
---------- ---------- ---------- G1 R1 Q1 G2 R4 Q4 G3 R6 Q6 我们用这个结果集和原来的表内容进行join,有如下SQL语句: select tb.GRP,COL1,COL2
from tab_test ta, ( select GRP,MIN(COL1) MCOL1,MIN(COL2) MCOL2 from tab_test group by GRP order by GRP ) tb where ta.GRP = tb.GRP(+) and COL1 = MCOL1(+) and COL2 = MCOL2(+) order by ta.GRP,ta.COL1,ta.COL2; GRP COL1 COL2
---------- ---------- ---------- G1 R1 Q1 R2 Q2 R3 Q3 G2 R4 Q4 R5 Q5 G3 R6 Q6 我想这个就是想要的结果集吧。这个方案只是个初步设想,如果在生产系统上使用,还需要进一步优化。
如果结果集比较大,可以按照如下方案优化:
select tb.GRP, ta.COL1, ta.COL2 from tab_test ta, ( select GRP,min(rowid) KEEP (DENSE_RANK FIRST ORDER BY COL1 ASC,COL2 ASC) row_id from tab_test group by GRP ) tb where ta.rowid = tb.row_id (+) order by ta.GRP,ta.COL1,ta.COL2; February 27 Mark's Recipe 5: Carrot Brocoli and Mushroom Soup 什锦鲜蔬汤经过数次激烈的思想斗争和强烈的用户反馈,照顾到广大素食爱好者的需要,马克决定改改一向无荤不餐的原则,开辟一个素菜专题。其实开这个专栏还有个小插曲,马克它日在MSN上遇见一位故知,向我哭诉若干痛苦经历后,跪求一个素菜菜谱,情急之下一向在MU餐饮界独霸一方的马克居然哑口无言,惭愧之余在网上搜罗数日,终于得一减肥素菜谱……归纳整理如下:
1.原料:
2.做法:
February 23 和Helen同学的页面主题音乐马克在忙着做菜的时候也经常被别人说层次不够,心里不服气,于是在深更半夜偷偷到海伦同学的BLOG上面看了看,果然,人家的MTV都作的不同凡响……马克也决定在自己的主页上搞个MTV,叫做“和海伦同学”
February 15 Mark's Recipe 4: Stir-fried Green Beans with Pork Mince (干煸豆角)新年过后估计是因为从老家带回来的东西很多,就总是拿一些熟食加上米饭瞎凑合,没有时间好好自己做饭,继而导致马克做菜连载节目在春节后断档,热心观众怨声载道……今天看到“美丽酵母菌”和“没有雨的一天”对我的不懈支持,对自己的懒惰深深感到愧疚。到了写一些东西回馈热心读者的时候了,不然我电子云的blog估计真的要关张了……另外,此连载节目也是受人重托,不能不写,呵呵。做什么菜呢?马克看了看冰箱里的东东,半斤猪肉,10来个鸡蛋,四季豆还有不少,西红柿一个,土豆若干。土豆炖肉?鸡蛋西红柿?虽然说黄蓉当年也只是做了一道白菜就让老前辈吃得服服帖帖,我估计还没说出这种菜谱来就被大家一脚踹出门了……还是敬业一点吧,这么多东西排列组合也不少……有了
1。原料
2。做法
菜虽然用料简单,确比较好吃,曼城曾经多次给俺们的同学做。后来试着把鱼香调料加进来,便成鱼香干煸豆角,发现也很不错,有兴趣的同学可以参考马克做菜第2部鱼香茄子调料的方法改进本菜的口味……There is a surprise 4 u.
January 28 Mark's Recipe 3: Stewed Meatballs with Delicious Sauce -- 红烧狮子头Mark's Recipe 3: Stewed Meatballs with Delicious Sauce -- 红烧狮子头
春节快到拉,马克菜谱节目也该做一期和春节相关的特别节目了,本想做朝鲜冷面的食谱,思忖再三觉得不合适,毕竟正宗的冷面里要加入狗肉的……,似乎和今年的属相相克,呵呵。不如介绍个红烧狮子头给各位在国内的或者在国外奋斗的朋友,祝大家在狗年万事大吉,圆圆满满!
闲言碎语不要讲,直接进入正题为妙:
1.原料
2.做法
January 23 Mark's Recipe 2: Fried Aubergine with Fish Flavour Sauce (鱼香茄子)1.原料:
2.做法:
综合了我多次实践经验,应该还是可以在非四川同学面前露一手了,呵呵 在英国另一发现就是,其实把干煸豆角做成鱼香味道的似乎也不错~~~~ 不能再写了,不然就有要回去吃零食了。 January 19 Mark's Recipe 1 - Ribs with Sweet and Sour Sauce (糖醋排骨)没有什么浪漫故事好说,但不更新blog总觉得有些对不起观众。不多整没用的了。向来人是铁饭是钢写一些菜谱吧,也算是回忆回忆曼城做饭时的黄金时光。当然写这个东东更是受人重托,所以写起来更是任重道远……
Ribs with Sweet and Sour Sauce (糖醋排骨)
原料:
猪排骨25O克(大约够一顿吃的,不过如若您是海量的话,就另当别论了)
番茄酱少许(主要为了上颜色,不太喜欢番茄的人就少来点)
白糖25克(这样做出来的才够甜,江南风味~~~)
醋2O克(白醋为佳,如果没有的话镇江醋也好,英国醋……就免了吧)
料酒1O克(用于排骨去腥)
盐3克
葱姜末适量(调汁时依照个人喜好添加)
步骤:
1.去腥
Option 1:将一锅水烧开,放入排骨,稍稍煮一下,捞出排骨,将血沫水倒掉。
Option 2:将排骨用盐和料酒腌一下,半小时后就能达到酒和盐入味的效果。
2.炸排骨
炒锅上火,六成热(不要太热),将排骨炸成金黄色,切莫时间过长,否则肉就干枯无味了。本来是想做去油腻的效果,如果炸成干巴巴的,反而成了肉干……我就干过这样的傻事,各位人兄莫要重蹈覆辙才是。
3.做糖醋汁
白糖、番茄酱、醋、盐,搅匀成糖醋汁备用;
炒锅洗干净后重新加油少许,放入葱姜末,加入糖醋汁后不断搅拌,当汁已经粘稠后,加入炸好的排骨翻炒1分钟后起锅。
经多次试验,效果还算不错了……呵呵
September 04 Closer“Love is not the amount of air you take; it is the happiest moment that takes your breath away.” -- Quote from Hitch
On the street, they met each other. A romantic commence of the story…
Alice: Hello stranger!
In the hospital
Alice: Sorry, looking for cigarette. Dan: I’ve given up. (Dan passed the coffee to Alice.) Alice: Thank you. Alice: Gonna be somewhere? Dan: Work. Alice: Hum… Dan: Did you fancy with my sandwiches? Alice: I don’t eat fish. Dan: Why not? Alice: Fish piss in the sea. Dan: So do children. Alice: Don’t need children neither. (Dan smiled back) Alice: What’s your work? Dan: I’m sort of journalist. Alice: What sort? Dan: I write bitrism.(Not sure about the word I heard Alice: I wait here for a long week.
(Dan looked at the old lady sitting in front of him) Dan: She was 21 when she came here. Dan: Does it hurt? Alice: I live. Dan: Do you need me to put your leg up? Alice: Yes, please. Dan moved her leg up to the chair in front of her. Alice: Who cut off your crust? Dan: Me. Alice: Did your mother cut off your crust when you were a little boy? Dan: Yes. I believe she did. Alice: You should eat crust. Dan: You should stop smoking! Alice: How long was I halt? Dan: About ten seconds. Alice: That long? Dan: You came to, you focused on me, you said ‘hello stranger’. Alice: What a floozy! Dan: The cabby crossed himself and said ‘fuck! I thought I killed her!’ I said let’s get her to the hospital. He hesitated. I think he thought there might be paper work and would be responsible. So I said with slight sneer: ‘please just drop her in the hospital.’ Alice: Show me the sneer. Dan sneered to her immediately. Alice: Good bustard! They went out of the hospital, Alice get a cigarette from a man standing at the front gate and she passed the cigarette to Dan, try to lure him to have a smoke.
Dan: I told you I’ve given up.
Alice: Try harder. Dan: You live here? Alice: Just arrived, from New York. Dan: Taking a vacation? Alice: I’m on an expedition. Dan: Where is your baggage? Alice took another smoke without answering the question. Dan: Where are you staying? Alice: I’m a waif. They keep on going along a street when a bus was passing by.
Dan: A red bus. Two policemen come toward them as well. Dan: Policemen. Old Bobby? Observed the distinctive helmet? Alice giggled. Dan: See Paul Cathedral, the most famous dome? Alice: This is truly the magnificent tour. Dan: As long as you are in the tour you goanna get to see. Alice: What’s this?
Dan: No idea. Alice: There are people who die saving the lives of others. Dan: I’ve been here before. Twenty years ago we came here. My mother was dead my father and I went here the afternoon she died. She was a smoker. She died in the hospital actually. Alice: Is your father still alive? Dan: Hanging on. He is in a home. After they got on, sitting at the back of the bus and talked about Dan's work, everything seems to follow through naturally.When Dan looked at her attentively and said 'You are disarming', what was he thinking about her is simple and clear.
And so it is, love begins to make a man and a woman closer.
August 31 Water Alarm?Acomb House Flat 7, don't cry for us because we are leaving I just can't take too much tears on my carpet. Only when I got up this moring did I realised that there was something went wrong in my bedroom: there are two dark ares on the carpet. On the way of getting closer to have a clear look, I heard the funny noise when I stepped on the dark areas. The sound is really like walking on the slippery muddy pathway in the rain. Oh Dear! the carpet have been soaked for the whole night.By the light of nature, I escaped out of bedroom. The corridor have a even nicer look than the scenery in my bedroom. The ripples on the water is shining under the gloomy light, romantic enough to develop a love story if a beautiful girl is sitting beside me.
I don't like to jump to conclusions or make false assertions, but I would have to remind my perspective flate mates, always have raincoat and water proof shoes readily available, in case of 'water alarms'.
August 04 谈论Fanky Wisdom
引用 Fanky Wisdom June 30 The music for Zuozuo's love storyNothing else but somthing beyond words to depict my feeling about Zuozuo's love story... A music clip may be the best option in this situation.
June 27 The minutes after reading the ZuoZuo's love storyThe primary aim of this blog today is try to construct a series of research of ZuoZuo's personal space. As shown on the site statistics, the thread named 'The 7th Birthday of My Love, 5th Anniversary of the Marriage' and published on 23th of June have been visited 30 times. This is unblievable achievement for a topic related to love story and 'set up a paradigm approach'(adapted from the lines of a comment) for blog writers who persuiting high access rates. At the moment when I just finish reading the content of the piece of wrting, I suddenly realized that the only quotation used in this article is in the introduction part. The rest of the writing seems to be filled with vivid examples and small cases from her life in chronological order. Personally, I still strongly maintain the belief that only best memories can last forever. In the light of this, one who have read this article could conclude jealously that ZuoZuo have been surrounded by the happiness of her love during the past 7 years. As you might noticed, one of the most frequently used words in discribing what have been done by DouDou is 'every time' which means that there have been continuous effort from him to retain the romance of the love while injecting new meaning for it. Quite often we incline to accept the view that marriage is the demise of the romance because of the lack of changes. I would argue that the faith of continue doing a simple thing is highly edorable even for the couple who have spent their time for decades. Any artworks that suvive the change of time is appriciated by most of people because of its intrinsic beauty(for instance, the ceiling of Sistine Chapel, the Great Wall). So does the love. June 14 电子云的处女志~~~When I accidentally browsed my personal site today, I suddenly realized that something needed to be appended onto my blog for the sake of not leaving it emty as it was. Actually, I didn't have to much content to show off at this place so far. However, as time goes by, I could deliberately develop the habit of inscribe my own experiences and share them witn others who I may or may not know about. |
|
|