SQLServer³£¼û²éѯÎÊÌâ
ÓÐЩ³£¼ûµÄÎÊÌâÔÚÂÛ̳Öв»¶Ï³öÏÖ£¬²»·ÁÕûÀíһϡ£
ÒÔÏÂÓï¾äÊÇÔÚSQLServer2005ÉÏʵÏֵģ¬Ò»Ð©Óï¾äÎÞ·¨ÔÚSS2000ÉÏÖ´ÐС£
ÓÐÓÃÖ¸ÊýÊÇÎÒ¸ù¾ÝÕâ¸öÎÊÌâµÄ³£¼û³Ì¶È´òµÄ·Ö£¬½ö¹©²Î¿¼¡£Êµ¼ÊÉÏ£¬µ±ÄãÓöµ½ÁËÕâ¸öÎÊÌ⣬Õâ¸öÎÊÌâÄÄÅÂÔÙÉÙ¼û£¬½â¾ö·½°¸Ò²ÊǷdz£ÓÐÓõġ£
1. Éú³ÉÈô¸ÉÐмǼ
ÓÐÓÃÖ¸Êý£º¡ï¡ï¡ï¡ï¡ï
³£¼ûµÄÎÊÌâÀàÐÍ£º¸ù¾ÝÆðÖ¹ÈÕÆÚÉú³ÉÈô¸É¸öÈÕÆÚ¡¢Éú³ÉÒ»ÌìÖеĸ÷¸öʱ¼ä¶Î
¡¶SQL Server 2005¼¼ÊõÄÚÄ»£ºT-SQL²éѯ¡·×÷Õß½¨ÒéÔÚÊý¾Ý¿âÖд´½¨Ò»¸öÊý¾Ý±í£º
SQL code
--×ÔÈ»Êý±í1-1M
CREATE TABLE Nums(n int NOT NULL PRIMARY KEY CLUSTERED)
--ÊéÉϽéÉÜÁ˺ܶàÖÖÌî³ä·½·¨£¬ÒÔÏÂÊÇ×î¸ßЧµÄÒ»ÖÖ£¬ÐèÒªSS2005µÄROW_NUMBER()º¯Êý¡£
WITH B1 AS(SELECT n=1 UNION ALL SELECT n=1), --2
B2 AS(SELECT n=1 from B1 a CROSS JOIN B1 b), --4
B3 AS(SELECT n=1 from B2 a CROSS JOIN B2 b), --16
B4 AS(SELECT n=1 from B3 a CROSS JOIN B3 b), --256
B5 AS(SELECT n=1 from B4 a CROSS JOIN B4 b), --65536
CTE AS(SELECT r=ROW_NUMBER() OVER(ORDER BY (SELECT 1)) from B5 a CROSS JOIN B3 b) --65536 * 16
INSERT INTO Nums(n)
SELECT TOP(1000000) r from CTE ORDER BY r
ÓÐÁËÕâ¸öÊý×Ö±í£¬¿ÉÒÔ×öºÜ¶àÊÂÇ飬³ýÉÏÃæÌáµ½µÄÁ½¸öÍ⣬»¹ÓУºÉú³ÉÒ»Åú²âÊÔÊý¾Ý¡¢Éú³ÉËùÓÐASCII×Ö·û»òUNICODEÖÐÎÄ×Ö·û¡¢µÈµÈ¡£
¾³£ÓиßÊÖʹÓÃSELECT number from master..spt_values WHERE type = 'P'£¬ÕâÊǺÜÃîµÄ·½·¨£»µ«ÕâÑùÖ»ÓÐ2048¸öÊý×Ö£¬¶øÇÒÓï¾äÌ«³¤£¬²»¹»·½±ã¡£
×ÜÖ®£¬Ò»¸öÊý×Ö¸¨Öú±í£¨10Íò»¹ÊÇ100Íò¸ù¾Ý¸öÈËÐèÒª¶ø¶¨£©£¬ÄãÖµµÃÓµÓС£
2. ÈÕÀú±í
ÓÐÓÃÖ¸Êý£º¡ï¡ï¡ï¡î¡î
¡¶SQL±à³Ì·ç¸ñ¡·Ò»Ê齨ÒéÒ»¸öÆóÒµµÄÊý¾Ý¿âÓ¦¸Ã´´½¨Ò»¸öÈÕÀú±í£º
SQL code
CREATE TABLE Calendar(
date datetime NOT NULL PRIMARY KEY CLUSTERED,
weeknum int NOT NULL,
weekday int NOT NULL,
weekday_desc nchar(3) NOT NULL,
is_workday bit NOT NULL,
is_weekend bit NOT NULL
)
GO
WITH CTE1 AS(
SELECT
date = DATEADD(day,n,'19991231')
from Nums
WHERE n <= DATEDIFF(day,'19991231','20201231')),
CTE2 AS(
SELECT
date,
weeknum = DATEPART(week,date),
weekday = (DATEPART(weekday,date) + @@DATEFIRST - 1) % 7,
weekday_desc = DATENAME(weekday,date)
f
Ïà¹ØÎĵµ£º
bcpÃüÁîÊÇSQL ServerÌṩµÄÒ»¸ö¿ì½ÝµÄÊý¾Ýµ¼Èëµ¼³ö¹¤¾ß¡£Ê¹ÓÃËü²»ÐèÒªÆô¶¯ÈκÎͼÐιÜÀí¹¤¾ß¾ÍÄÜÒÔ¸ßЧµÄ·½Ê½µ¼Èëµ¼³öÊý¾Ý¡£bcpÊÇSQL ServerÖиºÔðµ¼Èëµ¼³öÊý¾ÝµÄÒ»¸öÃüÁîÐй¤¾ß£¬ËüÊÇ»ùÓÚDB-LibraryµÄ£¬²¢ÇÒÄÜÒÔ²¢Ðеķ½Ê½¸ßЧµØµ¼Èëµ¼³ö´óÅúÁ¿µÄÊý¾Ý¡£bcp¿ÉÒÔ½«Êý¾Ý¿âµÄ±í»òÊÓͼֱ½Óµ¼³ö£¬Ò²ÄÜͨ¹ýSELECT fromÓï¾ä¶Ô±í»òÊÓ ......
Oracle´æ´¢¹ý³Ìת³ÉSqlserver´æ´¢¹ý³Ì
1.ÈÕÆÚת»»
Oracle£º TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD'))
SqlServer£ºCAST(CONVERT(CHAR(8),GETDATE(), 112) AS INT)
×¢£ºÆäÖБYYYYMMDD’¸ñʽ ¶ÔÓ¦112
2.ROWIDת»»
Oracle£º ROWID
SqlServer: PRIMARY KEY(±íÖ÷¼ü)
3.ROWNUM ......
Óû§È¨ÏÞ¹ÜÀí
Ò»¡¢·þÎñÆ÷µÇ¼ÕʺźÍÓû§ÕʺŹÜÀí
1.SQL Server·þÎñÆ÷µÇ¼¹ÜÀí
²»¹ÜʹÓÃÄÄÖÖÈÏ֤ģʽ£¬Óû§¶¼±ØÐëÏȾ߱¸ÓÐЧµÄÓû§µÇ¼Õʺš£SQL ServerÓÐÈý¸öĬÈϵÄÓû§µÇ¼Õʺţº¼´sa¡¢Builtin\administratorsºÍguest¡£saÊÇϵͳ¹ÜÀíÔ±(system administrator)µÄ¼ò³Æ£¬ÊÇÒ»¸öÌØÊâµÄÓû§£¬ÔÚSQL ServerϵͳºÍËùÓÐÊý¾Ý¿âÖÐÓ ......
sqlserverÊý¾Ý¿â£ºjavaÁ¬½Ósqlserver2005Êý¾Ý¿âÐĵÃÌå»á
Ê×ÏȵÃÏÂÔØÇý¶¯³ÌÐòµ½Î¢ÈíÍøÕ¾ÏÂÔØMicrosoft SQL Server 2005 JDBC Driver 1.2¡¡¡¡½âѹMicrosoft SQL Server 2005 jdbc driver1.2.exe
µÃµ½sqljdbc.jar£¬ÓõÃʱºòÖ±½Ó¼Óµ½classpathÖÐÈ¥.
¡¡ÉèÖÃSQL Server·þÎñÆ÷
·ÀÖ¹³öÏÖ
com.mi ......
SQLserverÖÐÓÃconvertº¯Êýת»»ÈÕÆÚ¸ñʽ
2008-01-23 15:47
SQLserverÖÐÓÃconvertº¯Êýת»»ÈÕÆÚ¸ñʽ2008-01-15 15:51SQLserverÖÐÓÃconvertº¯Êýת»»ÈÕÆÚ¸ñʽ
SQL ServerÖÐÎÄ°æµÄĬÈϵÄÈÕÆÚ×Ö¶Îdatetime¸ñʽÊÇyyyy-mm-dd Thh:mm:ss.mmm
ÀýÈç:
select getdate()
2004-09-12 11:06:08.177
ÕûÀíÁËÒ»ÏÂSQL ServerÀïÃæ¿ÉÄÜ ......