sql serverÊÓͼµÄ×÷ÓÃ
ÊÓͼ¿ÉÒÔ±»¿´³ÉÊÇÐéÄâ±í»ò´æ´¢²éѯ¡£¿Éͨ¹ýÊÓͼ·ÃÎʵÄÊý¾Ý²»×÷Ϊ¶ÀÌصĶÔÏó´æ´¢ÔÚÊý¾Ý¿âÄÚ¡£Êý¾Ý¿âÄÚ´æ´¢µÄÊÇ SELECT Óï¾ä¡£SELECT Óï¾äµÄ½á¹û¼¯¹¹³ÉÊÓͼËù·µ»ØµÄÐéÄâ±í¡£Óû§¿ÉÒÔÓÃÒýÓñíʱËùʹÓõķ½·¨£¬ÔÚ Transact-SQL Óï¾äÖÐͨ¹ýÒýÓÃÊÓͼÃû³ÆÀ´Ê¹ÓÃÐéÄâ±í¡£Ê¹ÓÃÊÓͼ¿ÉÒÔʵÏÖÏÂÁÐÈÎÒ»»òËùÓй¦ÄÜ£º
½«Óû§ÏÞ¶¨ÔÚ±íÖеÄÌض¨ÐÐÉÏ¡£
ÀýÈ磬ֻÔÊÐí¹ÍÔ±¿´¼û¹¤×÷¸ú×Ù±íÄڼǼÆ乤×÷µÄÐС£
½«Óû§ÏÞ¶¨ÔÚÌض¨ÁÐÉÏ¡£
ÀýÈ磬¶ÔÓÚÄÇЩ²»¸ºÔð´¦Àí¹¤×ʵ¥µÄ¹ÍÔ±£¬Ö»ÔÊÐíËûÃÇ¿´¼û¹ÍÔ±±íÖеÄÐÕÃûÁС¢°ì¹«ÊÒÁС¢¹¤×÷µç»°ÁкͲ¿ÃÅÁУ¬¶ø²»ÄÜ¿´¼ûÈκΰüº¬¹¤×ÊÐÅÏ¢»ò¸öÈËÐÅÏ¢µÄÁС£
½«¶à¸ö±íÖеÄÁÐÁª½ÓÆðÀ´£¬Ê¹ËüÃÇ¿´ÆðÀ´ÏóÒ»¸ö±í¡£
¾ÛºÏÐÅÏ¢¶ø·ÇÌṩÏêϸÐÅÏ¢¡£
ÀýÈ磬ÏÔʾһ¸öÁеĺͣ¬»òÁеÄ×î´óÖµºÍ×îСֵ¡£
ͨ¹ý¶¨Òå SELECT Óï¾äÒÔ¼ìË÷½«ÔÚÊÓͼÖÐÏÔʾµÄÊý¾ÝÀ´´´½¨ÊÓͼ¡£SELECT Óï¾äÒýÓõÄÊý¾Ý±í³ÆΪÊÓͼµÄ»ù±í¡£ÔÚÏÂÀýÖУ¬pubs Êý¾Ý¿âÖÐµÄ titleview ÊÇÒ»¸öÊÓͼ£¬¸ÃÊÓͼѡÔñÈý¸ö»ù±íÖеÄÊý¾ÝÀ´ÏÔʾ°üº¬³£ÓÃÊý¾ÝµÄÐéÄâ±í£º
CREATE VIEW titleview
AS
SELECT title, au_ord, au_lname, price, ytd_sales, pub_id
from authors AS a
JOIN titleauthor AS ta ON (a.au_id = ta.au_id)
JOIN titles AS t ON (t.title_id = ta.title_id)
Ö®ºó£¬¿ÉÒÔÓÃÒýÓñíʱËùʹÓõķ½·¨ÔÚÓï¾äÖÐÒýÓà titleview¡£
SELECT *
from titleview
Ò»¸öÊÓͼ¿ÉÒÔÒýÓÃÁíÒ»¸öÊÓͼ¡£ÀýÈ磬titleview ÏÔʾµÄÐÅÏ¢¶Ô¹ÜÀíÈËÔ±ºÜÓÐÓ㬵«¹«Ë¾Í¨³£Ö»ÔÚ¼¾¶È»òÄê¶È²ÆÎñ±¨±íÖвŹ«²¼±¾Äê¶È½ØÖ¹µ½ÏÖÔڵIJÆÕþÊý×Ö¡£¿ÉÒÔ½¨Á¢Ò»¸öÊÓͼ£¬ÔÚÆäÖаüº¬³ý au_ord ºÍ ytd_sales ÍâµÄËùÓÐ titleview ÁС£Ê¹ÓÃÕâ¸öÐÂÊÓͼ£¬¿Í»§¿ÉÒÔ»ñµÃÒÑÉÏÊеÄÊé¼®Áбí¶ø²»»á¿´µ½²ÆÎñÐÅÏ¢£º
CREATE VIEW Cust_titleview
AS
SELECT title, au_lname, price, pub_id
from titleview
ÊÓͼ¿ÉÓÃÓÚÔÚ¶à¸öÊý¾Ý¿â»ò Microsoft® SQL Server™ 2000 ʵÀý¼ä¶ÔÊý¾Ý½øÐзÖÇø¡£·ÖÇøÊÓͼ¿ÉÓÃÓÚÔÚÕû¸ö·þÎñÆ÷×éÄÚ·Ö²¼Êý¾Ý¿â´¦Àí¡£·þÎñÆ÷×é¾ßÓÐÓë·þÎñÆ÷¾Û¼¯ÏàͬµÄÐÔÄÜÓŵ㣬²¢¿ÉÓÃÓÚÖ§³Ö×î´óµÄ Web Õ¾µã»ò¹«Ë¾Êý¾ÝÖÐÐĵĴ¦ÀíÐèÇó¡£Ôʼ±í±»Ï¸·ÖΪ¶à¸ö³ÉÔ±±í£¬Ã¿¸ö³ÉÔ±±í°üº¬Ôʼ±íµÄÐÐ×Ó¼¯¡£Ã¿¸ö³ÉÔ±±í¿É·ÅÖÃÔÚ²»Í¬·þÎñÆ÷µÄÊý¾Ý¿âÖС£Ã¿¸ö·þÎñÆ÷Ò²¿ÉµÃµ½·ÖÇøÊÓͼ¡£·ÖÇøÊÓͼʹÓà Transact-SQL UNION ÔËËã·û£¬½«ÔÚËùÓгÉÔ±±íÉÏÑ¡ÔñµÄ½á¹ûºÏ²¢Îªµ¥¸ö½á¹û¼¯£¬¸Ã½á¹û¼¯µÄÐÐΪÓëÕû¸öÔʼ±íµÄ¸´±¾ÍêÈ«Ò»Ñù¡£ÀýÈçÔÚÈý¸
Ïà¹ØÎĵµ£º
ÈóǬ±¨±í¿ÉÒÔͨ¹ýSQL¼ìË÷ºÍ¸´ÔÓSQLÉú³ÉÊý¾Ý¼¯¡£µ±SQLÖÐÐèÒª´«Èë¶à¸ö²ÎÊýʱ£¬ÒªÔÚÉè¼ÆÆ÷ÖÐͨ¹ý ÅäÖÃ-²ÎÊý ¶¨ÒåÏàÓ¦µÄ²ÎÊý£¬È»ºóÔÙ°ÑSQLÖÐÐèÒª²ÎÊýµÄµØ·½Ìæ»»³É?£¬×îºó»¹ÒªÔÚSQL±à¼Æ÷ÖÐÌí¼Ó¶ÔÓ¦?µÄ²ÎÊý¡£ÕâÑùµ±SQLÖÐÓжàÉÙ¸öÎʺţ¬ÎÒÃǾÍÐèÒªÌí¼Ó¶àÉÙ¸ö²ÎÊý¡£µ±SQLÖÐÓõ½µÄ²ÎÊý±È½ÏÉÙʱ£¬²Ù×÷ÆðÀ´»¹±È½Ï·½±ã¡£µ«µ±ÒµÎñ±È½Ï¸´ ......
/*--text×ֶεÄÌæ»»´¦Àí
--*/
--´´½¨Êý¾Ý²âÊÔ»·¾³
--create table #tb(aa text)
declare @s_str varchar(8000),@d_str varchar(8000), --¶¨ÒåÌæ»»µÄ×Ö·û´®
......
£¨1£© Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
ORACLEµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏÂ,Äã±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í¡£Èç¹ûÓÐ3¸öÒÔÉϵıíÁ¬½Ó²éѯ, ÄÇ ......
Ò»¸ösqlÓï¾ä£ºÒ»¸ö±ítestÓÐËĸö×Ö¶Îid,a,b,c,Èç¹û±íÖеļǼÓÐÈý¸ö×Ö¶Îa,b,c¶¼ÏàµÈ£¬Ôò˵Ã÷ÕâÌõ¼Ç¼ÊÇÏàͬµÄ£¬ÇóÏàͬµÄ¼Ç¼µÄ¸öÊý ¡£
select a,b,c,count(*) from (select c.a,c.b,c.c from test c) having count(*) >= 2 group by a,b,c
»òÕß
select zdbh,tdzl,zdmj,count(*) from ecaadmin.zdsx group by zdbh ......