·¢²¼Ê±¼ä : ÐÇÆÚ¶þ ÎÄÕÂSqlServer ³£ÓÃÃüÁî˵Ã÷¸üÐÂÍê±Ï¿ªÊ¼ÔĶÁ
ÓѺãͨÓÐÏÞ¹«Ë¾
from ZY_BRJSK
where jsrq between '20050101' and '2005011024' and ybjszt=2 and jlzt in (0,1,2) group by hzxm
having sum(zje)>10000
²»´ø¾ÛºÏº¯ÊýµÄ HAVING ×Ó¾ä SELECT hzxm FROM ZY_BRJSK
where jsrq between '20050101' and '2005011024' and ybjszt=2 and jlzt in (0,1,2) GROUP BY hzxm
HAVING hzxm LIKE 'ÕÅ%'
11¡¢order by ?. [ ASC | DESC ]
¶Ô½á¹û¼¯ÅÅÐò¡£ASC ºÍ DESC ¹Ø¼ü×ÖÓÃÓÚÖ¸¶¨ÐÐÊÇ°´ÉýÐò»¹ÊÇ°´½µÐòÅÅÐò¡£Ê¹ÓÃunionʱ£¬Ö»ÄܶÔ×îºó½á¹ûÅÅÐò¡£
12¡¢union
UNION ÔËËã·ûʹÄúµÃÒÔ½«Á½¸ö»ò¶à¸ö SELECT Óï¾äµÄ½á¹û×éºÏ³ÉÒ»¸ö½á¹û¼¯¡£Ê¹Óà UNION ×éºÏµÄ½á¹û¼¯¶¼±ØÐë¾ßÓÐÏàͬµÄ½á¹¹¡£¶øÇÒËüÃǵÄÁÐÊý±ØÐëÏàͬ£¬²¢ÇÒÏàÓ¦µÄ½á¹û¼¯ÁеÄÊý¾ÝÀàÐͱØÐë¼æÈÝ¡£
UNION ÔËËã·û´Ó½á¹û¼¯ÖÐɾ³ýÖظ´µÄÐС£Èç¹ûʹÓà ALL ¹Ø¼ü×Ö£¬ÄÇô½á¹ûÖн«°üº¬ËùÓÐÐв¢ÇÒ½«²»É¾³ýÖظ´µÄÐС£
Select hzxm,sum(zje) zje from ZY_BRJSK
where jsrq between '200312101' and '2003121024' and ybjszt=2 and jlzt in (0,1,2) group by hzxm union
Select 'ºÏ¼Æ',sum(zje) from ZY_BRJSK
where jsrq between '200312101' and '2003121024' and ybjszt=2 and jlzt in (0,1,2) order by sum(zje)
¶þ¡¢delete ÃüÁî
DELETE Óï·¨µÄ¼ò»¯ÐÎʽΪ£º DELETE table_or_view [FROM table_sources ] [WHERE search_condition ]
1¡¢É¾³ýÈ«²¿ÐÐ
Delete #temp = TRUNCATE TABLE #temp
Èç¹ûҪɾ³ýÔÚ±íÖеÄËùÓÐÐУ¬Ôò TRUNCATE TABLE ±È DELETE ¿ì¡£DELETE ÒÔÎïÀí
µÚ 5 Ò³ ¹² 53 Ò³
ÓѺãͨÓÐÏÞ¹«Ë¾
·½Ê½Ò»´Îɾ³ýÒ»ÐУ¬²¢ÔÚÊÂÎñÈÕÖ¾ÖмǼÿ¸öɾ³ýµÄÐС£TRUNCATE TABLE ÔòÊÍ·ÅËùÓÐÓë±í¹ØÁªµÄÒ³¡£Òò´Ë£¬TRUNCATE TABLE ±È DELETE ¿ìÇÒÐèÒªµÄÊÂÎñÈÕÖ¾¿Õ¼ä¸üÉÙ¡£
×¢Òâdrop table Óëdelete µÄÇø±ð
2¡¢ ÔÚÐм¯ÉÏʹÓÃdelete delete from #temp where ¡
3¡¢ÔÚÓαêµÄµ±Ç°ÐÐÉÏʹÓà DELETE
ÏÂÀýÏÔʾÔÚÃûΪ cs_dxmdm µÄÓαêÉÏËù×öµÄɾ³ý¡£ËüÖ»Ó°Ï쵱ǰ´ÓÓαêÌáÈ¡µÄµ¥ÐС£
DELETE FROM #dxmdm
WHERE CURRENT OF cs_dxmdm
4¡¢¾ÝÓë¹ØÁª±í¡¢×Ó²éѯÉÏʹÓÃdelete delete ZY_BRSYK
from ZY_BRSYK a,ZYB_BRYJK b where a.syxh=b.syxh and a.brzt=9
Èý¡¢update ÃüÁî
¼òµ¥¸ñʽ£º
update table_name set ÁÐ=±í´ïʽ [FROM table_sources] [WHERE search_condition]
1¡¢ ʹÓüòµ¥µÄupdate
UPDATE YF_YFZKC SET djsl = 0
2¡¢ °Ñ WHERE ×Ó¾äºÍ UPDATE Óï¾äÒ»ÆðʹÓÃ
UPDATE YF_YFZKC SET jxje=0
Where abs(jxje)>1000000
3¡¢Í¨¹ý UPDATE Óï¾äʹÓÃÀ´×ÔÁíÒ»¸ö±íµÄÐÅÏ¢ update #temp
set ksmc =b.name
from #temp a,YY_KSBMK b where a.ksdm=b.id
µÚ 6 Ò³ ¹² 53 Ò³
ÓѺãͨÓÐÏÞ¹«Ë¾
4¡¢ ÔÚÓαêµÄµ±Ç°ÐÐÉÏʹÓÃupdate
update #dxmdm set zje=@zje
WHERE CURRENT OF cs_dxmdm
ËÄ¡¢insertÃüÁî(±í±ØÐë´æÔÚ)
ËùÌṩµÄÊý¾ÝÖµ±ØÐëÓëÁеÄÁбíÆ¥Åä¡£Êý¾ÝÖµµÄÊýÄ¿±ØÐëÓëÁÐÊýÏàͬ£¬Ã¿¸öÊý¾ÝÖµµÄÊý¾ÝÀàÐÍ¡¢¾«¶ÈºÍСÊýλÊýÒ²±ØÐëÓëÏàÓ¦µÄÁÐÆ¥Åä¡£ 1¡¢Ê¹Óà INSERT...SELECT ²åÈë¶àÐÐ
insert #mzybtemp (hzxm,pzh,jzks,jzrq,jzcs,zje,ybdm,sjh,zzbz,sfzh,zddm,bjqk) select hzxm,convert(varchar(17),substring(cardno,1,10)),ksdm,
substring(sfrq,1,8),1,zje-zfyje-yhje,ybdm,sjh,substring(zhbz,1,1), substring(sfzh,1,18),zddm,substring(zhbz,2,1) from VW_MZBRJSK a (nolock)
where sfrq between @ksrq and @jssj and ybjszt=2 and ghsfbz in (0,1)
and substring(zhbz,4,1)='0' and substring(zhbz,12,1)='0' and exists(select 1 from YY_YBFLK b (nolock)
where b.ybdm=a.ybdm and b.pzlx=10)
2¡¢ ʹÓà INSERT...Values ²åÈëÒ»ÐС£
Èç¹ûûÓÐÖ¸¶¨ÁеÄÁÐ±í£¬Ö¸¶¨ÖµµÄ˳Ðò±ØÐëÓë±í»òÊÓͼÖеÄÁÐ˳ÐòÒ»Ö¡£ insert into #temp(syxh,jsxh) values (@syxh,@jsxh)
3¡¢SET IDENTITY_INSERT ±í ON|OFF ÔÊÐí½«ÏÔʽֵ²åÈë±íµÄ±êʶÁÐÖÐ
Èç¹û²åÈëÖµ´óÓÚ±íµÄµ±Ç°±êʶֵ£¬Ôò SQL Server ×Ô¶¯½«Ð²åÈëÖµ×÷Ϊµ±Ç°±êʶֵʹÓá£
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40)) GO
-- Inserting values into products table.
INSERT INTO products (product) VALUES ('screwdriver') INSERT INTO products (product) VALUES ('hammer') INSERT INTO products (product) VALUES ('saw') INSERT INTO products (product) VALUES ('shovel') GO
--ɾ³ýµÚÈýÐÐ DELETE products
µÚ 7 Ò³ ¹² 53 Ò³
ÓѺãͨÓÐÏÞ¹«Ë¾
WHERE product = 'saw' GO
-- ÊÔͼ²åÈëid=3µÄ¼Ç¼,½«±¨´í
INSERT INTO products (id, product) VALUES(3, 'garden shovel') GO
-- SET IDENTITY_INSERT to ONʱ,ÄܲåÈëid=3µÄ¼Ç¼. SET IDENTITY_INSERT products ON GO
INSERT INTO products (id, product) VALUES(3, 'garden shovel'). GO
SET IDENTITY_INSERT products OFF GO
µÚ¶þ½Ú º¯Êý Ò»¡¢¾ÛºÏº¯Êý
SUM¡¢AVG¡¢COUNT¡¢MAX ºÍ MIN ºöÂÔ¿ÕÖµ£¬¶ø COUNT(*) ²»ºöÂÔ¡£
1¡¢ count()
COUNT(*) ·µ»Ø×éÖÐÏîÄ¿µÄÊýÁ¿¡£Ëü¶ÔÿÐзֱð½øÐмÆÊý£¬°üÀ¨º¬ÓпÕÖµnullµÄÐС£
COUNT(ALL expression)=count(expression) ¶Ô×éÖеÄÿһÐж¼¼ÆËã expression ²¢·µ»Ø·Ç¿ÕÖµµÄÊýÁ¿¡£
COUNT(DISTINCT expression) ¶Ô×éÖеÄÿһÐж¼¼ÆËã expression ²¢·µ»ØΨһ·Ç¿ÕÖµµÄÊýÁ¿¡£
select count(*) ×ÜÐÐÊý,
count(zlf_pt) ÓÐÖµµÄÐÐÊý, count(all zlf_pt) ÓÐÖµµÄÐÐÊý,
count(distinct zlf_pt) ²»Öظ´µÄÐÐÊý from YY_KSBMK
×ÜÐÐÊý ÓÐÖµµÄÐÐÊý ÓÐÖµµÄÐÐÊý ²»Öظ´µÄÐÐÊý ----------- ----------- ----------- ----------- 205 61 61 3
2¡¢ sum()
sum(ALL expression)=sum(expression) ¶ÔËùÓеķǿյÄÖµÇóºÍ sum(DISTINCT expression) ·µ»ØΨһ·Ç¿ÕÖµµÄºÍ Àý:
create table #temp (aa int,bb money) insert #temp values(1,null)
µÚ 8 Ò³ ¹² 53 Ò³