SQL²éÑ¯ÖØ¸´Êý¾ÝºÍÇå³ýÖØ¸´Êý¾Ý
ÓÐÀý±í£ºemp
emp_no name age
001 Tom 17
002 Sun 14
003 Tom 15
004 Tom 16
ÒªÇó£º
ÁгöËùÓÐÃû×ÖÖØ¸´µÄÈ˵ļǼ
(1)×îÖ±¹ÛµÄ˼·£ºÒªÖªµÀËùÓÐÃû×ÖÓÐÖØ¸´ÈË×ÊÁÏ£¬Ê×ÏȱØÐëÖªµÀÄĸöÃû×ÖÖØ¸´ÁË£º
select name from emp group by name having count(*)>1
ËùÓÐÃû×ÖÖØ¸´È˵ļǼÊÇ:
select * from emp
where name in (select name from emp group by name having count(*)>1)
(2)ÉÔ΢ÔÙ´ÏÃ÷Ò»µã£¬¾Í»áÏëµ½£¬Èç¹û¶Ôÿ¸öÃû×Ö¶¼ºÍÔ±í½øÐбȽϣ¬´óÓÚ2¸öÈËÃû×ÖÓëÕâÌõ¼Ç¼ÏàͬµÄ¾ÍÊǺϸñµÄ £¬¾ÍÓÐ
select * from emp where (select count(*) from emp e where e.name=emp.name) >1
--×¢ÒâÒ»ÏÂÕâ¸ö>1£¬ÏëÏÂÈç¹ûÊÇ =1£¬Èç¹ûÊÇ =2 Èç¹ûÊÇ>2 Èç¹û e ÊÇÁíÍâÒ»ÕÅ±í ¶øÇÒÊÇ=0Äǽá¹û ¾Í¸üºÃÍæÁË:)
Õâ¸ö¹ý³ÌÊÇ ÔÚÅжϹ¤ºÅΪ001µÄ ÈË µÄʱºòÏÈÈ¡µÃ 001µÄ Ãû×Ö£¨emp.name£© È»ºóºÍÔ±íµÄÃû×Ö½øÐÐ±È½Ï e.name
×¢ÒâeÊÇempµÄÒ»¸ö±ðÃû¡£
ÔÙÉÔ΢ÏëµÃ¶àÒ»µã£¬¾Í»áÏëµ½£¬Èç¹ûÓÐÁíÍâÒ»¸öÃû×ÖÏàͬµÄÈ˹¤ºÅ²»ÓëËýËûÏàͬÄÇôÕâÌõ¼Ç¼·ûºÏÒªÇó£º
select * from emp
where exists&
Ïà¹ØÎĵµ£º
µÚÒ»ÖÖ·½·¨: ʹÓÃoutput²ÎÊý
USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.usp_GetList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.usp_GetList;
GO
CREATE PROCEDURE Production.usp_GetList @product varchar(40)
, @maxprice money
, ......
ʹÓþۼ¯Ë÷ÒýÓÅ»¯SQL²éѯ
Ê×ÏÈÈÃÎÒÃÇ×öÒ»¸ö²âÊÔ£¬ÏÖ´´½¨Ò»¸ö±í Ïò±íÖвåÈë²»µÈÊý¾Ý
--DROP TABLE T_UserInfo--------------------------------------
CREATE
TABLE
T_UserInfo
(
Userid
varchar(20),
UserName varchar(20)
)
--
DECLARE
@I INT
DECLARE
@ENDID INT
SELECT
@I =
1
SELECT
@ ......
½ñÌìÔÚÂÛ̳ÄÚ¿´µ½Ò»Æª ÃæÊÔÌ⣬ÊǹØÓÚwhere ºÍon µÄÇø±ðµÄ£¬×Ô¼ºÒ²²»¶®£¬ÔÚÍøÉÏËѳöһƪÎÄÕ£¬¼ÓÉî×Ô¼ºµÄ¼ÇÒä
ת
Êý¾Ý¿âÔÚͨ¹ýÁ¬½ÓÁ½ÕÅ»ò¶àÕűíÀ´·µ»Ø¼Ç¼ʱ£¬¶¼»áÉú³ÉÒ»ÕÅÖмäµÄÁÙʱ±í£¬È»ºóÔÙ½«ÕâÕÅÁÙʱ±í·µ»Ø¸øÓû§¡£
ÔÚʹÓÃleft jionʱ£¬onºÍwhereÌõ¼þµÄÇø±ðÈçÏ£º
1¡¢ onÌõ¼þ ......
SQL SERVER 2008Öж¨Ê±±¸·ÝÊý¾Ý¿âÈÎÎñµÄ´´½¨Óëɾ³ý
[http://news.newhua.com/news1/program_database/2008/1215/0812151515485B22JHGBK9HJ87DK09D6958HB8K83EJ0053JKGC1A543.html]
ÓÉÓÚ֮ǰÊÇʹÓÃSQL SERVER 2000»òÕß2005£¬Õâ´Î¸ø¿Í»§°²×°Ê±Ê¹ÓÃ2008 SERVER£¬±¾ÒÔΪ´´½¨Êý¾Ý¿â¶¨Ê±±¸·ÝÈÎÎñ»áÓë2000/2005ÓкܴóÇø±ð£¬µ«¾ ......
Ìá¸ßSQLÖ´ÐÐЧÂʵļ¸µã½¨Òé:
¡¡¡¡¡ô¾¡Á¿²»ÒªÔÚwhereÖаüº¬×Ó²éѯ;
¡¡¡¡¹ØÓÚʱ¼äµÄ²éѯ£¬¾¡Á¿²»ÒªÐ´³É£ºwhere to_char(dif_date,'yyyy-mm-dd')=to_char('2007-07-01','yyyy-mm-dd');
¡¡¡¡¡ôÔÚ¹ýÂËÌõ¼þÖУ¬¿ÉÒÔ¹ýÂ˵ô×î´óÊýÁ¿¼Ç¼µÄÌõ¼þ±ØÐë·ÅÔÚwhere×Ó¾äµÄĩβ;
¡¡¡¡from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í£¬driving table)½«±»× ......