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

SQL ºÏ²¢ÐУ¬·µ»Ø´ø·Ö¸ô×Ö·ûµÄ×Ö·û´®

 
--ºÏ²¢ÐУ¬²¢·µ»ØºÏ²¢µÄÖµ
Create proc [dbo].[proUniteRow]
@tab varchar(30),           --±íÃû
@col varchar(30),           --ºÏ²¢µÄÁÐÃû
@where varchar(2000),      --ºÏ²¢µÄÌõ¼þ
@firstSplit varchar(100), --Á¬½Ó×Ö·ûǰ׺
@lastSplit varchar(100),  --Á¬½Ó×Ö·ûºó׺
@order varchar(100),       --ÅÅÐò
@val nvarchar(2000)='' output –·µ»ØÖµ
as
begin
 declare @sql nvarchar(2000)
 set @sql='
 declare @retStr varchar(2000)
 set @retStr=''''
 declare cur cursor
 read_only
 for select
 '+@col+' from '+@tab
 if len(@where)>0
 set @sql=@sql+' where '+@where
 
 if len(@order)>0
 set @sql=@sql+' order by '+@order
 
 set @sql=@sql+'
 declare @str varchar(2000)
 open cur
 fetch next from cur into @str
 while (@@fetch_status =0)
 begin
 set @retStr=@retStr+'''+@firstSplit+'''+@str+'''+@lastSplit+'''
 fetch next from cur into @str
 end
 close cur
 deallocate cur
 if len(@retStr)>0
 set @strEnd=substring(@retStr,1,len(@retStr)-1)
  '
 exec sp_executesql @sql,N'@strEnd nvarchar(2000) output',@val output
end
 
Ö´ÐУº
±ípro_lbsxb:
ID  lbsx_value  lbsx_isList  lbsx_order
1   A           1            1
2   B           1            2
3   C           1            3
4   D        &


Ïà¹ØÎĵµ£º

SQLµÄ×óÁ¬½Ó£¨left join £©


SELECT g.rid, room_no, planed_count,cc from
    (SELECT rid,room_no, planed_count from YC_HOTEL_ROOM_T ) r
left join
    (SELECT rid,count(*) cc  from  YC_GUEST_INFO_T group by rid ) g
ON   r.rid = g.rid  WHERE planed_ ......

SQLÈ¡³öµÚ m Ìõµ½µÚ n Ìõ¼Ç¼µÄ·½·¨

´ÓTable ±íÖÐÈ¡³öµÚ m Ìõµ½µÚ n ÌõµÄ¼Ç¼£º(Not In °æ±¾)
SELECT TOP n-m+1 *
  from Table
  WHERE (id NOT IN (SELECT TOP m-1 id from Table ))
--´ÓTABLE±íÖÐÈ¡³öµÚmµ½nÌõ¼Ç¼ (Exists°æ±¾)
SELECT TOP n-m+1 * from TABLE AS a WHERE Not Exists
(Select * from (Select Top m-1 * from TABLE orde ......

oracle sql tuning

alert index mem_ct monitoring usage;
desc v$object_usage;
set linesize 190
select * from v$object_usage;
SQL>SET AUTOTRACE ON;
¡¡¡¡*autotrace¹¦ÄÜÖ»ÄÜÔÚSQL*PLUSÀïʹÓÃ
¡¡¡¡ÆäËûһЩʹÓ÷½·¨£º
¡¡¡¡2.2.1¡¢ÔÚSQLPLUSÖеõ½Óï¾ä×ܵÄÖ´ÐÐʱ¼ä
¡¡¡¡SQL> set timing on;
2.2.2¡¢Ö»ÏÔʾִÐмƻ®--(»áÍ¬Ê ......

SQL ServerÈÕÆÚ²Ù×÷

1¡¢²éѯÁ½¸öʱ¼äÖ®¼ä
select * from [tablename] where date between \'value1\' and \'value2\'
 
2¡¢ÏÔʾ×îºó»Ø¸´Ê±¼ä
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
 
3¡¢Èճ̰²ÅÅÌáǰ5·ÖÖÓÌáÐÑ
select * from Èճ̰²ÅÅ w ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ