¡¡¡¡µÚÒ»²½£ºµ÷²éÁ˽âÎåÏîÉç»á±£ÏÕ»ù½ðÐÅÏ¢»¯¹ÜÀíÇé¿ö¡£Ò½ÁƱ£ÏÕ½ðºÍÑøÀϱ£ÏÕ½ð¹ÜÀí½Ï¹æ·¶£¬ÓÐͳһµÄÐÅÏ¢¹ÜÀíϵͳ£»¶øʧҵ±£ÏÕ½ðÒò°ì¹«Ìõ¼þÓÐÏÞ£¬²ÉÓÃÊÖ¹¤Í³¼ÆÒµÎñÊý¾Ý£¬Êý¾Ý¸ñʽΪEXCLE±í¸ñ¡£Óɴ˵¼ÖÂÊý¾Ý¿ÉÒÀÀµ³Ì¶È²»Í¬¡£
¡¡¡¡µÚ¶þ²½£ºÔÚµ÷²éÁ˽âµÄ»ù´¡ÉÏ£¬È·¶¨Éó¼Æ˼·£¬¸ù¾ÝÉó¼ÆÐèÒª£¬ÒªÇó±»Éó¼Æµ¥Î»ÌṩҵÎñÊý¾Ý£¬¾ßÌåÐèÒªÈçÏ£º
¡¡¡¡£¨Ò»£©Ê§Òµ½ð·¢·ÅÃ÷ϸ±í£¬°üÀ¨×Ö¶ÎÃû£º·¢·ÅÔ·ݡ¢ÐÕÃû¡¢ÐÔ±ð¡¢Éí·ÝÖ¤ºÅ¡¢·¢·Å½ð¶î ¡¢µ¥Î»¡¢·¢·Å¿ªÊ¼Ê±¼ä¡¢·¢·ÅÖÕֹʱ¼ä¡£
¡¡¡¡£¨¶þ£©Ò½±£½É·Ñ±í£¬°üÀ¨×Ö¶ÎÃû£º½É·ÑÔ·ݡ¢ÐÕÃû¡¢µ¥Î»¡¢Éí·ÝÖ¤ºÅ¡¢µ¥Î»½É·Ñ½ð¶î¡¢¸öÈ˽ɷѽð¶î¡£
¡¡¡¡µÚÈý²½£º½«ËùÐèÊý¾ÝÕûÀí¹æ·¶µ¼ÈëAO£¬ÐγÉÒµÎñÊý¾Ý£¬±àÖƲéѯÓï¾ä½øÐзÖÎö±È¶Ô¡£ÀûÓÃÉí·ÝÖ¤ºÅµÄΨһÐÔ£¬ÒÔÉí·ÝÖ¤ºÅ¡¢·¢·ÅºÍ½É·ÑÔ·ÝÏàͬΪÌõ¼þÁ¬½ÓÉÏÊöÁ½±í£¬²éѯÓÐÎÞ¼ÈÓнɷѵ¥Î»ÎªÆä½ÉÒ½±£·ÑͬʱÓÖÁìȡʧҵ½ðµÄÈËÔ±£¬ÆäÖÐÒ½±£½É·ÑÔ·ÝÐèÒªÓÃMIDº¯ÊýÈ¡³ö£¬²¢½øÐÐÊý¾ÝÀàÐÍת»»£¬²éѯ½á¹ûÏÔʾÆäÐÕÃû¡¢Éí·ÝÖ¤ºÅ¡¢½É·Ñµ¥Î»¼°Ê§Òµµ¥Î»£¬ÇÒ²»Öظ´£¬¹²²é³ö23ÈË£¬½«²éѯ½á¹ûµ¼³öÉú³É¼È½ÉÒ½±£ÓÖÁìʧҵ½ðÈËÔ±Ãûµ¥£¬½øÐзÖÎöÅжϡ£
¡¡¡¡²éѯÓï¾äÈçÏ£º
¡¡¡¡select distinct a.ÐÕÃû as ÁìʧҵÐÕÃû,b.ÐÕÃû as ½ÉÒ½±£ÐÕÃû,a.Éí·ÝÖ¤ºÅ,a.µ¥Î» as ʧҵµ¥Î»,b.µ¥Î» as ½É·Ñµ¥Î»
¡¡¡¡from [ÒµÎñ_ʧҵ½ð·¢·ÅÃ÷ϸ±í] a inner join [ÒµÎñ_Ò½±£½É·Ñ±í] b
¡¡¡¡on a.Éí·ÝÖ¤ºÅ=b.Éí·ÝÖ¤ºÅ and a.·¢·ÅÔ·Ý=val(mid(b.½É·ÑÔ·Ý,5,2))
¡¡¡¡order by a.ÐÕÃû
¡¡¡¡µÚËIJ½£º½øÒ»²½²éѯÏêϸÇé¿ö¡£¸ù¾ÝÉÏÒ»²½²éѯ½á¹û£¬ÅжÏÆä´æÔÚÎ¥¹æÁìȡʧҵ½ðÇé¿ö£¬½øÒ»²½²éѯÆäÏêϸÁìÈ¡Çé¿ö£¬ÔÚÉÏÊö²éѯµÄ»ù´¡ÉÏÌí¼ÓÏÔʾ×ֶη¢·ÅÔ·ݡ¢½É·ÑÔ·ݡ¢·¢·Å½ð¶î¡¢·¢·Å¿ªÊ¼Ê±¼ä¡¢·¢·ÅÖÕֹʱ¼ä£¬½«²éѯ½á¹ûµ¼³öµ½EXCLE£¬´ý·ÖÎö¡¢ºËʵ¡£
¡¡¡¡²éѯÓï¾äÈçÏ£º
¡¡¡¡select a.Éí·ÝÖ¤ºÅ,a.ÐÕÃû as ÁìʧҵÐÕÃû,b.ÐÕÃû as ½ÉÒ½±£ÐÕÃû,·¢·ÅÔ·Ý,mid(½É·ÑÔ·Ý,5,2) as ½É·ÑÔ·Ý,·¢·Å½ð¶î,·¢·Å¿ªÊ¼Ê±¼ä,·¢·ÅÖÕֹʱ¼ä,a.µ¥Î» as ʧҵµ¥Î»,b.µ¥Î» as ½É·Ñµ¥Î»
¡¡¡¡from [ÒµÎñ_ʧҵ½ð·¢·ÅÃ÷ϸ±í] a inner join [ÒµÎñ_Ò½±£½É·Ñ±í] b
¡¡¡¡on a.Éí·ÝÖ¤ºÅ=b.Éí·ÝÖ¤ºÅ and a.·¢·ÅÔ·Ý= val (mid(b.½É·ÑÔ·Ý,5,2))
¡¡¡¡order by a.Éí·ÝÖ¤ºÅ,·¢·ÅÔ·Ý, a.ÐÕÃû
¡¡¡¡µÚÎå²½£ºÑÓÉìÉó¼Æµ÷²é£¬ºËʵÏêϸÇé¿ö£¬µÃ³ö½áÂÛ¡£
¡¡¡¡Éó¼Æ²¿ÃŸù¾ÝÉó¼Æ½á¹û£¬×ö³öÏÞÆÚ×·»ØµÄ´¦Àí¾ö¶¨£¬Í¬Ê±½¨ÒéÀͶ¯²¿ÃżÓÇ¿ÄÚÍⲿÐÅÏ¢¹µÍ¨¹²Ïí¹¤×÷£¬ÀͶ¯²¿ÃŸ߶ÈÖØÊÓÕâÒ»Éó¼Æ½¨Ò飬Öƶ¨ºÍÍêÉÆÁËÏà¹Ø¸Ä½ø´ëÊ©£¬²¢½¨Á¢ÁËÄÚ²¿×ÊÔ´ÐÅÏ¢¹²ÏíµÈÖƶȣ¬Éó¼Æ³É¹ûÏÔÖø¡£
¡¡¡¡Éó¼ÆÆôʾ£ºAOÇ¿´óµÄÊý¾Ý·ÖÎö¡¢´¦Àí¹¦ÄÜ£¬Ê¹Ðí¶àÒÔÇ°Ïëµ½µ«×ö²»µ½µÄÉó¼ÆÒɵãµÃÒÔºËʵ¡¢²éÖ¤£¬ÒÔ¸üÉÙµÄÈËÁ¦ºÍÎïÁ¦£¬²é³ö¸ü¶à¡¢¸üÉî²ã´ÎµÄÎÊÌ⣬Éó¼ÆЧÂʺÍЧ¹û½ÏÒÔÇ°ÓÐÁËÖʺÍÁ¿µÄ·ÉÔ¾¡£