sql serverµÄËæ»úº¯ÊýnewID()ºÍRAND()¡¡¡¡
sql serverµÄËæ»úº¯ÊýnewID()ºÍRAND()¡¡¡¡
¡¡¡¡SELECT * from Northwind..Orders ORDER BY NEWID()
¡¡¡¡--Ëæ»úÅÅÐò
¡¡¡¡SELECT TOP 10 * from Northwind..Orders ORDER BY NEWID()
¡¡¡¡--´ÓOrders±íÖÐËæ»úÈ¡³ö10Ìõ¼Ç¼¡¡¡¡
¡¡¡¡Ê¾Àý¡¡¡¡
¡¡¡¡A.¶Ô±äÁ¿Ê¹Óà NEWID º¯Êý
¡¡¡¡ÒÔÏÂʾÀýʹÓà NEWID() ¶ÔÉùÃ÷Ϊ uniqueidentifier Êý¾ÝÀàÐ͵ıäÁ¿¸³Öµ¡£ÔÚ²âÊÔ uniqueidentifier Êý¾ÝÀàÐͱäÁ¿µÄֵ֮ǰ£¬ÏÈÊä³ö¸ÃÖµ¡£
¡¡¡¡-- Creating a local variable with DECLARESET syntax.
¡¡¡¡DECLARE @myid uniqueidentifier
¡¡¡¡SET @myid = NEWID()
¡¡¡¡PRINT 'Value of @myid is '+ CONVERT(varchar(255), @myid)
¡¡¡¡ÏÂÃæÊǽá¹û¼¯£º
¡¡¡¡Value of @myid is 6F9619FF-8B86-D011-B42D-00C04FC964FF
¡¡¡¡×¢Ò⣺
¡¡¡¡NEWID ¶Ôÿ̨¼ÆËã»ú·µ»ØµÄÖµ¸÷²»Ïàͬ¡£ËùÏÔʾµÄÊý×Ö½öÆð½âÊÍ˵Ã÷µÄ×÷Óᣡ¡¡¡
¡¡¡¡Ëæ»úº¯Êý£ºrand()
¡¡¡¡ÔÚ²éѯ·ÖÎöÆ÷ÖÐÖ´ÐУºselect rand()£¬¿ÉÒÔ¿´µ½½á¹û»áÊÇÀàËÆÓÚÕâÑùµÄËæ»úСÊý£º0.36361513486289558£¬ÏñÕâÑùµÄСÊýÔÚʵ¼ÊÓ¦ÓÃÖÐÓõò»¶à£¬Ò»°ãҪȡËæ»úÊý¶¼»áÈ¡Ëæ»úÕûÊý¡£ÄǾͿ´ÏÂÃæµÄÁ½ÖÖËæ»úÈ¡ÕûÊýµÄ·½·¨£º ¡¡¡¡
¡¡¡¡1¡¢
¡¡¡¡A£ºselect floor(rand()*N) ---Éú³ÉµÄÊýÊÇÕâÑùµÄ£º12.0
¡¡¡¡B£ºselect cast( floor(rand()*N) as int) ---Éú³ÉµÄÊýÊÇÕâÑùµÄ£º12 ¡¡¡¡
¡¡¡¡2¡¢
¡¡¡¡A£ºselect ceiling(rand() * N) ---Éú³ÉµÄÊýÊÇÕâÑùµÄ£º12.0
¡¡¡¡B£ºselect cast(ceiling(rand() * N) as int) ---Éú³ÉµÄÊýÊÇÕâÑùµÄ£º12 ¡¡¡¡
¡¡¡¡ÆäÖÐÀïÃæµÄNÊÇÒ»¸öÄãÖ¸¶¨µÄÕûÊý£¬Èç100£¬¿ÉÒÔ¿´³ö£¬Á½ÖÖ·½·¨µÄA·½·¨ÊÇ´øÓÐ.0Õâ¸öµÄСÊýµÄ£¬¶øB·½·¨¾ÍÊÇÕæÕýµÄÕûÊýÁË¡£
´óÖÂÒ»¿´£¬ÕâÁ½ÖÖ·½·¨Ã»Ê²Ã´Çø±ð£¬ÕæµÄûÇø±ð£¿ÆäʵÊÇÓÐÒ»µãµÄ£¬ÄǾÍÊÇËûÃǵÄÉú³ÉËæ»úÊýµÄ·¶Î§£º
¡¡¡¡·½·¨1µÄÊý×Ö·¶Î§£º0ÖÁN-1Ö®¼ä£¬Èçcast( floor(rand()*100) as int)¾Í»áÉú³É0ÖÁ99Ö®¼äÈÎÒ»ÕûÊý
¡¡¡¡·½·¨2µÄÊý×Ö·¶Î§£º1ÖÁNÖ®¼ä£¬Èçcast(ceiling(rand() * 100) as int)¾Í»áÉú³É1ÖÁ100Ö®¼äÈÎÒ»ÕûÊý
¡¡¡¡¶ÔÓÚÕâ¸öÇø±ð£¬¿´SQLµÄÁª»ú°ïÖú¾ÍÖªÁË£º¡¡¡¡
¡¡¡¡±È½Ï CEILING ºÍ FLOOR
¡¡¡¡CEILING º¯Êý·µ»Ø´óÓÚ»òµÈÓÚËù¸øÊý×Ö±í´ïʽµÄ×îСÕûÊý¡£FLOOR º¯Êý·µ»ØСÓÚ»òµÈÓÚËù¸øÊý×Ö±í´ïʽµÄ×î´óÕûÊý¡£ÀýÈ磬¶ÔÓÚÊý×Ö±í´ïʽ 12.9273£¬CEILING ½«·µ»Ø 13£¬FLOOR ½«·µ»Ø 12¡£FLOOR ºÍ CEILING ·µ»ØÖµµÄÊý¾ÝÀàÐͶ¼ÓëÊäÈëµÄÊý×Ö±í´ïʽµÄÊý¾ÝÀàÐÍÏàͬ¡£
¡¡¡¡ÏÖÔ
Ïà¹ØÎĵµ£º
1¡¢SQLÊÇÒ»ÖÖ˵Ã÷ÐÔÓïÑÔ£¬²»Êǹý³Ì»¯ÓïÑÔ¡£ÀàËÆ“¼ìË÷->¼ì²é->²åÈë->¸üДµÄ¹ý³Ì»¯²½ÖèµÄ˳ÐòÊÇûÓÐÒâÒåµÄ¡£Ó¦¸ÃÒÔÐм¯µÄ·½Ê½Ë¼¿¼£¬ÒÔÃèÊöÒ»¸öÂß¼µÄÓïÑÔ·½Ê½Ë¼¿¼¡£
2¡¢ÔÚÉè¼ÆÊý¾Ý¿â±í×Ö¶Îʱ£¬Ò»¸öÐÐÃèÊöÓ¦¸Ã°üº¬Ò»¸öÊÂʵ£¬¶øÇÒÊÇÈ«²¿ÊÂʵ¡£ÀýÈ翼ÇÚ¿¨µÄÉè¼Æ£¬
²»ÒªÉè¼ÆΪ£º[ userId, puchTime, even ......
create proc dbo.PROC_SQL_COMP @sql1 varchar(8000),@sql2 varchar(8000),@t int
as
/*
µ÷Óãºexec dbo.PROC_SQL_COMP @sql1='',@sql2='',@t=5 & ......
OracleÓкܶàÖµµÃѧϰµÄµØ·½£¬ÕâÀïÎÒÃÇÖ÷Òª½éÉÜOracle UNION ALL£¬°üÀ¨½éÉÜUNIONµÈ·½Ã档ͨ³£Çé¿öÏ£¬ÓÃUNIONÌæ»»WHERE×Ó¾äÖеÄOR½«»áÆ𵽽ϺõÄЧ¹û¡£¶ÔË÷ÒýÁÐʹÓÃOR½«Ôì³ÉÈ«±íɨÃè¡£×¢Ò⣬ÒÔÉϹæÔòÖ»Õë¶Ô¶à¸öË÷ÒýÁÐÓÐЧ¡£¼ÙÈçÓÐcolumnûÓб»Ë÷Òý£¬²éѯЧÂÊ¿ÉÄÜ»áÒòΪÄúûÓÐÑ¡ÔñOR¶ø½µµÍ¡£ÔÚÏÂÃæµÄÀý×ÓÖУ¬LOC_ID ºÍREGION ......
²éѯ¼°É¾³ýÖظ´¼Ç¼µÄSQLÓï¾ä
1¡¢²éÕÒ±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö×ֶΣ¨peopleId£©À´ÅжÏ
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2¡¢É¾³ý±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊÇ ......
1.±ÜÃâÔÚwhere×Ó¾äÖжÔ×Ö¶ÎÊ©¼Óº¯Êý£¬ÕâÑù½«µ¼ÖÂË÷ÒýʧЧ£¬±ÈÈ磺
select * from user where
to_char(create_time,'yyyymmdd')='20090101';
ÔÒò£ºÔÚ½¨Á¢indexµÄʱºòÊǸù¾Ý×Ö¶ÎÀ´½¨Á¢µÄ£¬Ò²¾ÍÊÇ˵oracleÔÚinidexµÄʱºòÊÇË÷ÒýµÄ×ֶεÄÖµ£¬Èç¹ûÌṩ¸øoracleµÄÊÇÒ»¸öÐèÒª¾¹ýº¯Êý´¦ÀíµÄ±È½Ï£¬oracle¾Íû°ì·¨Í¨¹ýË÷ÒýÖÐµÄ ......