SQLÊý¾Ý¿â±í»µÁ˵ÄÊÖ¶¯»Ö¸´·½·¨
½ñÌì½Óµ½¿Í»§µç»°£¬Ëµ²Ù×÷Êý¾ÝÎÞ·¨±£´æ¡£¾¹ý·ÖÎö£¬·¢ÏÖËûµÄÊý¾Ý¿âÒѾÓÐ5G¶àµÄ´óС£¬¶ø×îÖÕ·¢ÏÖÓÐÕűíµÄË÷Òý³ö´íÁË£¬ÓÃDBCC CHECKÒ²ÎÞÁ¦»ØÌì¡£
ÿ´ÎÓÃselect * from ln003082 Óï¾ä²éѯ£¬¶¼±¨ÈçÏ´íÎó£º
·þÎñÆ÷: ÏûÏ¢ 605£¬¼¶±ð 21£¬×´Ì¬ 1£¬ÐÐ 1
ÊÔͼ´ÓÊý¾Ý¿â 'ln_fl0125' ÖÐÌáÈ¡µÄÂß¼Ò³ (1:629904) ÊôÓÚ¶ÔÏó '869578136'£¬¶ø·Ç¶ÔÏó 'LN003082'¡£
Á¬½ÓÖжÏ
û°ì·¨£¬Ö»ÄÜÏë°ì·¨»Ö¸´ÁË¡£»¹ºÃÓм¸Î»ÀÏÊÖÔÚÉí±ß¡£ÓÚÊÇͨÈçÒÔϲ½ÖèÃãÇ¿»Ö¸´ÓÐЧÊý¾Ý£¬µ±È»£¬Óв¿·Ýµ¥×ÓÊý¾ÝÊǶªÊ§ÁË¡£Ö»Äܽпͻ§ÔÙÈ¥²¹ÁË¡£
Ê×ÏÈҪȷ¶¨ÄÄЩÊý¾Ý»Ø²»À´ÁË£¬Í¨¹ýÓαêÀ´Ò»Ò»È·¶¨ÄÄЩÊý¾ÝÎÞЧÁË¡£
declare @bill_no varchar(20)
declare @shop_no varchar(20)
DECLARE @QTY INT
DECLARE terminal_changecursor CURSOR FOR
select bill_no,shop_no from terminal_change
group by shop_no,bill_no
ORDER by shop_no,bill_no
OPEN terminal_changecursor
FETCH NEXT from terminal_changecursor into @bill_no ,@shop_no
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT from terminal_changecursor into @bill_no ,@shop_no
PRINT @bill_no +'-'+@shop_no
IF NOT (
(@bill_no='MB10010033' AND @shop_no = 'P9ZT') OR
(@bill_no='MT90910001' AND @shop_no = 'P9ZT') OR
(@bill_no='MT00110002' AND @shop_no = 'P10D') OR
(@bill_no='MB10010034' AND @shop_no = 'P9ZT') OR
(@bill_no='MB10010035' AND @shop_no = 'P9ZT')
)
select @QTY=sum(bill_qty) from LN003082 WHERE bill_no=@bill_no AND shop_no=@shop_no
ENDEND
DEALLOCATE terminal_changecursor
GO
ͨ¹ýÉÏÃæµÄÓï¾ä£¬¿ÉÒԲ鵽ÄÄЩµ¥ºÅµÄÊý¾Ý³öÁËÎÊÌâ¡£ÓÃIFÌø¹ýÓÐÎÊÌâµÄÊý¾Ý£¬µ±select sumͨ¹ýÓαê²éѯʱ£¬Èç¹ûÓÐÎÊÌâµÄÊý¾Ý£¬¾Í»áÍ£ÏÂÀ´Öжϣ¬¸ù¾Ýprint³öÀ´µÄµ¥ºÅ£¬½«Æä¼ÓÈëÏ´ÎIFÖУ¬Õâ¸ö²½ÖèºÜ¿ÝÔï¡£²»¹ýÒ»°ã²»»á³öÏÖ¹ý¶à¶ªÊ§µÄÊý¾Ý£¬Ò²Ö»ÄÜÕâÑùһһȷ¶¨ÁË¡£
½ÓÏÂÀ´¾ÍÊǸù¾Ý²éѯ³öÀ´ÓÐÎÊÌâµÄÊý¾Ý£¬½øÐйýÂ˻ָ´ÁË¡£
»Ö¸´·½·¨½Å±¾ÈçÏ£º
Ö÷ÒªÓÐ3²½¡£
--˵Ã÷£ºÖն˱䶯Êý¾Ý±í»µÁË£¬¶ªÊ§ÎåÕÅÊý¾Ý£¬Çë֪ͨÖØ´«
--1¡£Öؽ¨±íLN003082ΪLN003082_new,ÓÃÓÚתÒÆÓÐЧÊý¾Ý
select * into LN003082_ne
Ïà¹ØÎĵµ£º
--¾ÛºÏº¯Êý
use pubs
go
select avg(distinct price) --Ëãƽ¾ùÊý
from titles
where type='business'
go
use pubs
go
select max(ytd_sales) --×î´óÊý
from titles
go
use pubs
go
select min(ytd_sales) --×îСÊý
from titles
go
use pubs
go
select type,sum(price),sum(advance)  ......
Äã´´½¨µÄÿһ¸ö±¸·Ý¶¼ÊÇÒ»¸ö±¸·ÝÉ豸£¬¹ØÓÚËüµÄϸ½ÚÐÅÏ¢¶¼´æ´¢ÔÚmsdb..backupset±íÖС£Ò»¸ö±¸·ÝÉ豸¿ÉÒÔ±»´æ´¢ÔÚµ¥Ò»Îļþ£¬»òÊǶà¸öÎļþÖС£Í¬Ñù£¬Ò»¸öÎļþÒ²¿ÉÒÔ´æ´¢¶à¸ö±¸·ÝÉ豸¡£
ËùÒÔ£¬¼ÙÈçÄãÿ´Î±¸·Ý¶¼Ê¹ÓÃÏàͬµÄÎļþÃû£¬Îļþ¾Í»áÒ»Ö±Ôö³¤¡£Ò»¸öÆÕ±éµÄÎó½âÊÇ£ºÈç¹ûÄãÿ´ÎʹÓÃÏàͬµÄÎļþÃû£¬ÄǾɵı¸·ÝÉ豸¾Í»á±»¸²¸Ç¡ ......
ÔÚʹÓÃSQL ServerÊý¾Ý¿âʱ, Èç¹ûÐèÒª·ÃÎÊÔ¶³ÌµÄÁíÍâÒ»¸öÊý¾Ý¿â, ÄÇô¿ÉÒÔʹÓÃLinked ServerÀ´ÊµÏÖÕâ¸ö¹¦ÄÜ, ºÜ·½±ã. ±¾ÎÄ×ܽáÁË×Ô¼ºÔÚʹÓÃLinked Server¹¦ÄÜʱÓöµ½µÄ¼¸¸öÎÊÌâ, Èç¹ûÉÔ²»×¢Òâ,ºÜ¿ÉÄܳöÏÖ¸÷ÖÖ´íÎó.
1)´´½¨ÁËÒ»¸öеÄLinked Serverºó, ÐèÒªÊ×ÏÈÉèÖÃlogin ÕË»§, ÓÒ¼ü->ÊôÐÔ->Security, Èçͼ
......
1.Union
UNION Ö¸ÁîµÄÄ¿µÄÊǽ«Á½¸ö SQL Óï¾äµÄ½á¹ûºÏ²¢ÆðÀ´¡£´ÓÕâ¸ö½Ç¶ÈÀ´¿´£¬ UNION ¸ú JOIN ÓÐЩÐíÀàËÆ£¬ÒòΪÕâÁ½¸öÖ¸Á¿ÉÒÔÓɶà¸ö±í¸ñÖÐߢȡ×ÊÁÏ¡£ UNION µÄÒ»¸öÏÞÖÆÊÇÁ½¸ö SQL Óï¾äËù²úÉúµÄÀ¸Î»ÐèÒªÊÇͬÑùµÄ×ÊÁÏÖÖÀà¡£ÁíÍ⣬µ±ÎÒÃÇÓà UNIONÕâ¸öÖ¸Áîʱ£¬ÎÒÃÇÖ»»á¿´µ½²»Í¬µÄ×ÊÁÏÖµ (ÀàËÆ SELECT DISTINCT)¡£
UNION µ ......
inner join(µÈÖµÁ¬½Ó) Ö»·µ»ØÁ½¸ö±íÖÐÁª½á×Ö¶ÎÏàµÈµÄÐÐ
left join(×óÁª½Ó) ·µ»Ø°üÀ¨×ó±íÖеÄËùÓмǼºÍÓÒ±íÖÐÁª½á×Ö¶ÎÏàµÈµÄ¼Ç¼
right join(ÓÒÁª½Ó) ·µ»Ø°üÀ¨ÓÒ±íÖеÄËùÓмǼºÍ×ó±íÖÐÁª½á×Ö¶ÎÏàµÈµÄ¼Ç¼
INNER JOIN Óï·¨£º
INNER JOIN Á¬½ÓÁ½¸öÊý¾Ý±íµÄÓ÷¨£º
SELECT * from ±í1 INNER JOIN ±í2 ON ±í1.×ֶκÅ=±í2 ......