Mark's profileElectron CloudPhotosBlogLists Tools Help

Blog


    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.原料:

    • 胡萝卜 100克(小一些的胡萝卜可能需要4根),蘑菇 50克(如果是tesco的62p的蘑菇约摸半合),西兰花 50克(大约一小棵的样子),黄豆 30克
    • 色拉油 5克 5克 味精 2克,白糖2克
    • 葱姜末适量

    2.做法:

    •  胡萝卜去皮切成小块,蘑菇切片(不要太薄,半厘米厚比较好),黄豆泡透后煮熟,西兰花用手摘成小棵备用
    • 烧锅热后放油,假如蘑菇片和胡萝卜翻炒5分钟,加入开水煮汤,注意不要用太大的火(满城同学除了St Gabreil Hall的以外,估计都可以放到最大使劲煮,英国的电灶镇的不敢恭维……)
    • 等胡萝卜快煮烂的时候,放入西兰花,黄豆,盐 ,味精,白糖和葱姜末梢煮片刻即可。

     

     
     
    February 23

    和Helen同学的页面主题音乐

    马克在忙着做菜的时候也经常被别人说层次不够,心里不服气,于是在深更半夜偷偷到海伦同学的BLOG上面看了看,果然,人家的MTV都作的不同凡响……马克也决定在自己的主页上搞个MTV,叫做“和海伦同学”
     
     
    February 15

    Mark's Recipe 4: Stir-fried Green Beans with Pork Mince (干煸豆角)

     
     
    新年过后估计是因为从老家带回来的东西很多,就总是拿一些熟食加上米饭瞎凑合,没有时间好好自己做饭,继而导致马克做菜连载节目在春节后断档,热心观众怨声载道……今天看到“美丽酵母菌”和“没有雨的一天”对我的不懈支持,对自己的懒惰深深感到愧疚。到了写一些东西回馈热心读者的时候了,不然我电子云的blog估计真的要关张了……另外,此连载节目也是受人重托,不能不写,呵呵。做什么菜呢?马克看了看冰箱里的东东,半斤猪肉,10来个鸡蛋,四季豆还有不少,西红柿一个,土豆若干。土豆炖肉?鸡蛋西红柿?虽然说黄蓉当年也只是做了一道白菜就让老前辈吃得服服帖帖,我估计还没说出这种菜谱来就被大家一脚踹出门了……还是敬业一点吧,这么多东西排列组合也不少……有了—猪肉+四季豆=干煸豆角,哈哈,天无绝人之路阿(各位川菜前辈就免收我版权费吧,我今天也就只能到这个程度了)
     

    1。原料

    • 四季豆 500g 曼城同学可以直接去Tesco带2镑买2盒Green Beans ASDA可能会便宜些,不过个人认为没有Tesco的质量好
    • 猪肉末 150g Tesco 或者 ASDA的 Pork Mince 四分之一盒就差不多,不过去年不知道怎么的,我在的时候猪肉总是很少,不过好在这个菜用Beef Mince代替也还可以
    • 榨菜,海米(10个) 少许
    • 葱姜末(一根小葱,4薄片姜切成末)
    • 黄酒一汤匙,盐和糖各一茶匙,芝麻油半茶匙

    2。做法

    • 榨菜和海米切成米粒状
    • 四季豆去头尾,用手折成1寸左右的小段,洗干净后晾干
    • 热锅后多放些油,加入四季豆不断翻炒至四季豆水分逐渐散失。此时注意火千万不能太大,而且不能停止翻炒,不然就只有吃Black Bean了:)
    • 把四季豆捞出备用,另备热锅和油加入葱姜末爆锅,放入肉末,海米和榨菜,倒黄酒加盐糖和味精,翻炒片刻加入四季豆即可出锅。

    菜虽然用料简单,确比较好吃,曼城曾经多次给俺们的同学做。后来试着把鱼香调料加进来,便成鱼香干煸豆角,发现也很不错,有兴趣的同学可以参考马克做菜第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.原料
     
    • 五花肉 150克(如果实在懒得自己剁馅,就到超市买点肥瘦相间的肉馅吧,曼城同学可以到tesco买89p一盒的Pork Mince)
    • 马蹄 10克(差不多两到三个,新鲜的当然最好。曼城同学可以考虑到永兴隆那里看看,或者Portland Street 的 China Town)
    • 冬菇 10克(也可以用福建香菇干代替,用开水泡好,取4到5个就不错。很多曼城同学压箱底的干货估计都有这个吧,所以一时没有不要紧,可以去邻近宿舍去抢……推荐去处:Whitworth Park ^_^)
    • 青菜 5棵 (关键就是为了美观,肉食动物可以考虑省掉这个东西,我反正自己吃的时候不放青菜~~)
    • 生姜和葱 少许
    • 花生油 100克
    • 10克 白糖 5克 鸡精 5克 芝麻油 少许 淀粉 30 克 老抽 10克,料酒 5克
    • 面包屑 20克(可以加入到狮子头内,使之松软。喜欢高弹性的丸子的话,就省了这个吧)
    • 鸡蛋 1个

    2.做法

    • 五花肉剁成肉泥,马蹄和冬菇切成稍稍粗一点的米
    • 掺入面包屑,鸡蛋,盐,鸡精 ,淀粉,葱姜末料酒后搅拌成胶状,并作成4个大丸子(四喜丸子,差不多够两人吃的)
    • 青菜用开水炒一下捞出备用
    • 锅烧热后加入花生油,用一点点葱末测试油温,以嗞滋作响而不炸糊为准。放入四个丸子油炸至金黄色后捞出
    • 锅内留少量油,放入开水加入鸡精,放入盐,白糖,老抽,当然要加入丸子,用小火慢慢炖大约20分钟至汁浓后,稍稍加入一点水淀粉勾芡出锅
    • 把5棵青菜在盘边码放一下,如果有艺术细胞的还可以考虑一下整体造型,不过造型时间不宜超过5分钟,凉了就不好吃了

     

    January 23

    Mark's Recipe 2: Fried Aubergine with Fish Flavour Sauce (鱼香茄子)

    1.原料:
    • 茄子 250克(差不多够个情侣餐的分量,两个爷们显然不够)
    • 郫县豆瓣酱 50克(四川同学大都知道郫县豆瓣酱在川菜中的地位,什么回锅肉啦,什么鱼香肉丝拉,虽然仍然在英国奋斗的同学们买起来或多或少有些困难,不过我还是不推荐用替代品)
    • 葱姜末若干(按照个人喜好了),
    • 蒜末多多(差不多用4到5瓣蒜的量)
    • 白糖若干(差不多2汤勺的量)
    • 生抽一点点(差不多1汤勺的量)

    2.做法:

    •  将茄子洗净后切成滚刀块(块不要太大,切口是斜面,这样容易入味);
    • 鉴于ASDA和World Wide的茄子普遍比较涩(不知道现在有无改进,反正以前吃过亏,好好的鱼香茄子变成了怪味茄子,5555~~~~),建议大家用热盐水把茄子泡15分钟后捞出,将水沥干;
    • 用多多的油将茄子炸成金黄色(这一步油可千万省不得,不然最后的结果就和熬茄子没什么两样了)
    • 另加入油炒豆瓣酱,加白糖和生抽一起翻炒,加入炸好的茄子和一点点排骨汤(热水也可以凑合,不过起锅的时候别忘了味精),收汁。
    • 起锅之前加入蒜末翻一下,闻到香味就可以了

    综合了我多次实践经验,应该还是可以在非四川同学面前露一手了,呵呵

    在英国另一发现就是,其实把干煸豆角做成鱼香味道的似乎也不错~~~~

    不能再写了,不然就有要回去吃零食了。

     
     
    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分钟后起锅。
     
    经多次试验,效果还算不错了……呵呵
     
    October 13

    偶的作业

    风生,雨生,学生,生生不息。
    云袭,露袭,文袭,袭袭秋意。
    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
    The Mayonnaise Jar And Coffee

    When things in your life seem almost too much to handle, when 24 hours a day are not enough, remember the mayonnaise jar....and the coffee.

     

    A professor stood before his philosophy class and had some items in front of him. When the class began, wordlessly, he picked up a very large and empty mayonnaise jar and proceeded to fill it with golf balls. He then asked the students if the jar was full. They agreed that it was.

    The professor then picked up a box of pebbles and poured them into the jar. He shook the jar lightly. The pebbles rolled into the open areas between the golf balls. He then asked the students again if the jar was full. They agreed it was. 

    The professor next picked up a box of sand and poured it into the jar. Of course, the sand filled up everything else. He asked once more if the jar was full. The students responded with a unanimous “yes”.

    The professor then produced two cups of coffee from under the table and poured the entire contents into the jar, effectively filling the empty space between the sand. The students laughed.

    “Now,” said the professor, as the laughter subsided, “I want you to recognize that this jar represents your life. The golf balls are the important things in life-God, family, your children, your health, your friends, and your favorite passions-things that if everything else was lost and only they remained, your life would still be full. The pebbles are the other things that matter like your job, your house, and your car. The sand is everything else-the small stuff.”

    “If you put the sand into the jar first,” he continued, “there is no room for the pebbles or the golf balls. The same goes for life. If you spend all your time and energy on the small stuff, you will never have room for the things that are important to you.

    Pay attention to the things that are critical to your happiness. Play with your children. Take time to get a medical check ups. Take your partner out to dinner. Play another 18.There will always be time to clean the house and fix the disposal.” Take care of the golf balls first, the things that really matter. Set your priorities. The rest is just sand.” 

    One of the students raised her hand and inquired what the coffee represented. The professor smiled, "I am glad you asked. It just goes to show you that no matter how full your life may seem, there is always room for a couple of cups of coffee with a friend."

     
     
    June 30

    The music for Zuozuo's love story

    Nothing 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 story

    The 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.