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

SQL ÿ¸ö·ÖÀà¸÷È¡2ÌõÊý¾Ý

create table tb (ptoid int,proclassid int,proname varchar(10))
insert tb
select 1,1,'Ò·þ1'
union all
select 2,2,'Ò·þ2'
union all
select 3,3,'Ò·þ3'
union all
select 4,3,'Ò·þ4'
union all
select 5,2,'Ò·þ5'
union all
select 6,2,'Ò·þ6'
union all
select 7,2,'Ò·þ7'
union all
select 8,1,'Ò·þ8'
select * from tb
ptoid       proclassid  proname   
----------- ----------- ----------
1           1           Ò·þ1
2           2           Ò·þ2
3           3           Ò·þ3
4           3           Ò·þ4
5           2           Ò·þ5
6           2           Ò·þ6
7           2           Ò·þ7
8           1           Ò·þ8
£¨ËùÓ°ÏìµÄÐÐÊýΪ 8 ÐУ©
select * from tb a where
(select count(*) from tb b where b.proclassid=a.proclassid and b.ptoid>a.ptoid) <2
order by proclassid
ptoid       proclassid  proname   
----------- ----------- ----------
1           1           Ò·þ1
8           1   


Ïà¹ØÎĵµ£º

º½¿Õ¹«Ë¾¹ÜÀíϵͳ(VC++ ÓëSQL 2005)

ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
      ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃæ£¬ºóÃæ ......

SQLÖÐcase whenµÄÁ½ÖÖʹÓ÷½·¨Ê¾Àý

Case¾ßÓÐÁ½ÖÖ¸ñʽ¡£¼òµ¥Caseº¯ÊýºÍCaseËÑË÷º¯Êý¡£
--¼òµ¥Caseº¯Êý
CASE sex
         WHEN '1' THEN 'ÄÐ'
         WHEN '2' THEN 'Å®'
ELSE 'ÆäËû' END
--CaseËÑË÷º¯Êý
CASE WHEN sex = '1' THEN 'ÄÐ'
  &nbs ......

SQL SERVER¿ç·þÎñÆ÷²éѯ(SQL SERVER DBLINK)

--¿ç·þÎñÆ÷²éѯÈçÏ£º
SELECT a.*,b.stor_Name
from OPENROWSET('MSDASQL',
   'DRIVER={SQL Server};SERVER=tom;UID=sa;PWD=123',
   pubs.dbo.authors) AS a,stores b
ORDER BY a.au_lname, a.au_fname
--ÆäÖУ¬tomΪԶ³Ì·þÎñÆ÷Ãû£¬stores ÊDZ¾»úÊý¾Ý¿âpubsÖеıí
--ÐèҪעÒâµÄÊÇÈô¶þ¸ö±íÖÐµÄ ......

sql µ¼³ö£¬½â¾ösheet±í´óСÏÞÖÆ

CREATE proc sqlToMultiExcelFile
@sqlstr nvarchar(4000), --²éѯÓï¾ä,Èç¹û²éѯÓï¾äÖÐʹÓÃÁËorder by ,Çë¼ÓÉÏtop 100 percent
@primaryKey varchar(100),--·ÖÒ³Ö÷¼ü×Ö¶Î
@path nvarchar(1000), --Îļþ´æ·ÅĿ¼
@fname nvarchar(250), --ÎļþÃû
@sheetname varchar(250)='sheet1' --Òª´´½¨µÄ¹¤×÷±íÃû,Ä ......

sql serverÈÕÆÚʱ¼äº¯Êý

Sql ServerÖеÄÈÕÆÚÓëʱ¼äº¯Êý
1. µ±Ç°ÏµÍ³ÈÕÆÚ¡¢Ê±¼ä
    select getdate()
2. dateadd ÔÚÏòÖ¸¶¨ÈÕÆÚ¼ÓÉÏÒ»¶Îʱ¼äµÄ»ù´¡ÉÏ£¬·µ»ØÐ嵀 datetime Öµ
   ÀýÈ磺ÏòÈÕÆÚ¼ÓÉÏ2Ìì
   select dateadd(day,2,'2004-10-15') --·µ»Ø£º2004-10-17 00:00:00.000
3. datediff ·µ»Ø¿çÁ½¸öÖ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ