Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQLÓï¾äPART2

Subquery: (single-row subqueries and multi-rows subqueries).
select select_list
from table
where expr operator (select select_list from table);
single-row subqueries operator: =, >, >=, <, <=, <>
e.g.:
1. select department_id, min(salary) from employees group by department_id having min(salary)>(select mn(salary) from employees where department_id = 50)
* min salary in department_id=50
* list department_id, min(salary) group by department_id and min(salary)> (min salary in departmentid=50).
2. select department_id, min(salary) from employees group by department_id having min(salary)>(select min(salary) from employees group by department_id);
* this sentense is wrong. has the subquery operates multi results.
Multi-row subqueires: any, all, in
IN:     equals to any member in the list
any:   1) must be preceded by =, !=, >, <, <=, >=. 2) compares a value to each value in a list or returned by a query. 3) evaluates to FALSE if the query returns no rows.
all:    1) must be preceded by =, !=, >, <, <=, >=. 2) compares a value to every value in a list or returned by a query. 3) evaluates to TRUE if the query returns no rows.
e.g.:
ndocsortid: 1...9
select ndocsortid from wf_docsort where nmoduleinfoid=2: results: 4,5,9
select ndocsortid from wf_docsort where nmoduleinfoid=9: results: null;
1. select  distinct ndocsortid
from  wf_procname
where ndocsortid < all (select ndocsortid from wf_docsort where nmoduleinfoid=2)
order by ndocsortid
results: 1,2,3
2. select nmoduleinfoid, ndocsortid, nprocid
from  wf_procname
where ndocsortid < any (select ndocsortid from wf_docsort where nmoduleinfoid=2)
order by ndocsortid 
results: 1,2,3,4,5,6,7,8
3. select  distinct ndocsortid
from  wf_procname
where ndocsortid < all (select ndocsortid from wf_docsort where nmoduleinfoid=9)
order by ndocsortid
results: 1,2,3,4,5,6,7,8,9
4. select&


Ïà¹ØÎĵµ£º

sql Ö´ÐÐ˳Ðò

SQL SelectÓï¾äÍêÕûµÄÖ´ÐÐ˳Ðò
2009-06-02 15:57
SQL SelectÓï¾äÍêÕûµÄÖ´ÐÐ˳Ðò£º1¡¢from×Ó¾ä×é×°À´×Ô²»Í¬Êý¾ÝÔ´µÄÊý¾Ý£»2¡¢where×Ó¾ä»ùÓÚÖ¸¶¨µÄÌõ¼þ¶Ô¼Ç¼ÐнøÐÐɸѡ£»3¡¢group by×Ӿ佫Êý¾Ý»®·ÖΪ¶à¸ö·Ö×飻4¡¢Ê¹Óþۼ¯º¯Êý½øÐмÆË㣻5¡¢Ê¹ÓÃhaving×Ó¾äɸѡ·Ö×飻
ÒÔϽáÂÛÊÇͨ¹ýÔÚSql Server2000ÉϵÄʵÑéµÃ³öµÄ½áÂÛ£¬ ......

PL/SQL³ÌÐòÉè¼Æ£¨ÓαêµÄʹÓã©


 ÎªÁË´¦Àí SQL Óï¾ä£¬ORACLE ±ØÐë·ÖÅäһƬ½ÐÉÏÏÂÎÄ( context area )µÄÇøÓòÀ´´¦ÀíËù±ØÐèµÄÐÅÏ¢£¬ÆäÖаüÀ¨Òª´¦ÀíµÄÐеÄÊýÄ¿£¬Ò»¸öÖ¸ÏòÓï¾ä±»·ÖÎöÒÔºóµÄ±íʾÐÎʽµÄÖ¸ÕëÒÔ¼°²éѯµÄ»î¶¯¼¯(active set)¡£
 ÓαêÊÇÒ»¸öÖ¸ÏòÉÏÏÂÎĵľä±ú( handle)»òÖ¸Õ롣ͨ¹ýÓα꣬PL/SQL¿ÉÒÔ¿ØÖÆÉÏÏÂÎÄÇøºÍ´¦ÀíÓï¾äʱÉÏÏÂÎÄÇø»á·¢ÉúÐ ......

sqlÓαê

ÒòΪҪ¸ù¾ÝºÜ¸´ÔӵĹæÔò´¦ÀíÓû§Êý¾Ý£¬ËùÒÔÕâÀïÓõ½Êý¾Ý¿âµÄÓαꡣƽʱ²»ÔõôÓÃÕâ¸ö£¬Ð´ÔÚÕâÀï´¿´âΪ×Ô¼º±¸¸öÍü¡£
--½«Ñ§¼®ºÅÖظ´µÄ·ÅÈëÁÙʱ±í tmp_zdsoft_unitive_code(³ý¸ßÖÐѧ¶ÎÍâ)
drop table tmp_zdsoft_unitive_code;
select s.id ,sch.school_code,sch.school_name,s.student_name,s.unitive_code,s.identity_car ......

¹ØÓÚSQLSERVERÖиüÐÂTextÀàÐÍSQLÓï¾ä½âÎö

 »ù±¾·½·¨:
                   
UPDATETEXT { table_name.dest_column_name dest_text_ptr }//{ Òª¸üеıíÒÔ¼° text¡¢ntext »ò image ÁеÄÃû³Æ,Ö¸ÏòÒª¸üÐ嵀 text¡¢ntext »ò image Êý¾ÝµÄÎı¾Ö¸ÕëµÄÖµ£¨ÓÉ TEXT ......

£¨×ª£©SQL¾­µäÃæÊÔÌ⼯£¨¶þ£©

µÚʮһÌ⣺
Óбístudents(name,class,grade),ÇëÓñê×¼sqlÓï¾äÍê³É
name class grade
ÕÅÈý Êýѧ 81
ÀîËÄ ÓïÎÄ 70
ÍõÎå Êýѧ 90
ÕÅÈý ÓïÎÄ 60
ÀîËÄ Êýѧ 100
ÍõÎå ÓïÎÄ 90
ÍõÎå Ó¢Óï 81
ÒªÇó: ÓÃsqlÓï¾äÊä³ö¸÷ÃŹ¦¿Î¶¼´óÓÚ80·ÖµÄͬѧÐÕÃû?
create table students (
name varchar(25),
class varchar(25),
grad ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ