SQLiteÖв»Ö§³ÖµÄsqlÓï·¨
½ñÌìºÜ×ÔÈ»µÄÔÚдSqlÓï¾äµÄʱºòÓÃÁËTop£¬Ò»¿ªÊ¼Ã»·¢ÏÖÎÊÌ⣬ÒòΪÎÒ´ÓÊý¾Ý¿â¶Á³öµÄÖµÕýºÃÊÇ0£¬¶øÎÒÏ°¹ß±äÁ¿¶¨ÒåµÄʱºòÒ²¶¼¸³Öµ0£¬¿ÉÊǵ½ÎÒ²»Òª0µÄʱºòÎҾͷ¢ÏÖÎÊÌâÁË¡£ºóÀ´²ÅÖªµÀ£¬¿É°®µÄСsqlite¾¹È»Óв»Ö§³ÖµÄsqlÓï·¨¡£
¡¡¡¡¿´µ½Ä³¸öÂÛ̳ÓиöÐÂÊÖÒ²·¢ÏÖÁËÕâ¸öÎÊÌâ²¢·¢ÌûÁË£¬ÏÂÃæһţÈ˵Ļظ´ÊÇ“topÊÇÄļҵĹؼü´Ê?sqlite¼ÒûÓÐÕâ´ÊµÄ”£¬¹þ£¬ÔÀ´topÕâ´ÊÊÇmssqlÀï²ÅÓеÄ~
¡¡¡¡×îºó»¹Ïë˵£¬Êý¾Ý¿â¾ø¶ÔÊǺÜÖØÒªµÄÒ»¿é¡£ÎҵĻù´¡Óï·¨ºÜÀ¬»ø£¬¹ý¼¸ÌìÈÏÕæ¼Ç¼Ï¡£
¡¡¡¡Google֮ϵĽá¹û£º
1 TOP
¡¡¡¡ÕâÊÇÒ»¸ö´ó¼Ò¾³£Îʵ½µÄÎÊÌ⣬ÀýÈçÔÚSQLSERVERÖпÉÒÔʹÓÃÈçÏÂÓï¾äÀ´È¡µÃ¼Ç¼¼¯ÖеÄÇ°Ê®Ìõ¼Ç¼£º
¡¡¡¡SELECT TOP 10 * from [index] ORDER BY indexid DESC;
¡¡¡¡µ«ÊÇÕâÌõSQLÓï¾äÔÚSQLiteÖÐÊÇÎÞ·¨Ö´Ðеģ¬Ó¦¸Ã¸ÄΪ£º
¡¡¡¡SELECT * from [index] ORDER BY indexid DESC limit 0,10;
¡¡¡¡ÆäÖÐlimit 0,10±íʾ´ÓµÚ0Ìõ¼Ç¼¿ªÊ¼£¬ÍùºóÒ»¹²¶ÁÈ¡10Ìõ
2 ´´½¨ÊÓͼ£¨Create View£©
¡¡¡¡SQLiteÔÚ´´½¨¶à±íÊÓͼµÄʱºòÓÐÒ»¸öBUG£¬ÎÊÌâÈçÏ£º
¡¡¡¡CREATE VIEW watch_single AS SELECT DISTINCTwatch_item.[watchid],watch_item.[itemid] from watch_item;
¡¡¡¡ÉÏÃæÕâÌõSQLÓï¾äÖ´Ðкó»áÏÔʾ³É¹¦£¬µ«ÊÇʵ¼ÊÉϳýÁË
¡¡¡¡SELECT COUNT(*) from [watch_single ] WHERE watch_ single.watchid = 1;
¡¡¡¡ÄÜÖ´ÐÐÖ®ÍâÊÇÎÞ·¨Ö´ÐÐÆäËûÈκÎÓï¾äµÄ¡£ÆäÔÒòÔÚÓÚ½¨Á¢ÊÓͼµÄʱºòÖ¸¶¨ÁË×Ö¶ÎËùÔڵıíÃû£¬¶øSQLite²¢²»ÄÜÕýÈ·µØʶ±ðËü¡£ËùÒÔÉÏÃæµÄ´´½¨Óï¾äÒª¸ÄΪ£º
¡¡¡¡CREATE VIEW watch_single AS SELECT DISTINCT [watchid],[itemid] from watch_item;
¡¡¡¡µ«ÊÇËæÖ®¶øÀ´µÄÎÊÌâÊÇÈç¹ûÊǶà±íµÄÊÓͼ£¬ÇÒ±í¼äÓÐÖØÃû×ֶεÄʱºò¸ÃÔõô°ì£¿
3 COUNT£¨DISTINCT column£©
¡¡¡¡SQLiteÔÚÖ´ÐÐÈçÏÂÓï¾äµÄʱºò»á±¨´í£º
¡¡¡¡¡¡¡¡SELECT COUNT(DISTINCT watchid) from [watch_item] WHERE watch_item.watchid = 1;
¡¡¡¡¡¡¡¡ÆäÔÒòÊÇSQLiteµÄËùÓÐÄÚÖú¯Êý¶¼²»Ö§³ÖDISTINCTÏÞ¶¨£¬ËùÒÔÈç¹ûҪͳ¼Æ²»Öظ´µÄ¼Ç¼ÊýµÄʱºò»á³öÏÖһЩÂé·³¡£±È½Ï¿ÉÐеÄ×ö·¨ÊÇÏȽ¨Á¢Ò»¸ö²»Öظ´µÄ¼Ç¼±íµÄÊÓͼ£¬È»ºóÔÙ¶Ô¸ÃÊÓͼ½øÐмÆÊý¡£
4 ÍâÁ¬½Ó
¡¡¡¡ËäÈ»SQLite¹Ù·½ÒѾÉù³ÆLEFT OUTER JOIN ÒѾʵÏÖ£¬µ«»¹Ã»ÓÐ RIGHT OUTER JOIN ºÍ FULL OUTER JOIN¡£µ«ÊÇʵ¼Ê²âÊÔ±íÃ÷Ëƺõ²¢²»Äܹ»Õý³£µÄ¹¤×÷¡£ÒÔÏÂÈýÌõÓï¾äÔÚÖ´ÐеÄʱºò¾ù»á±¨´í£º
¡¡¡¡SELECT tags.[tagid] from [tags],[tag_rss] WHERE tags.[tagid]
Ïà¹ØÎĵµ£º
ÔÚSQL serverÖо³£»áת»»stringµ½datetimeÀàÐÍ£¬×î³£Óõĺ¯Êý¾ÍÊÇConvert¡£ÄÇô¶ÔConvertÕâ¸öº¯Êý£¬²»µÃ²»ÏêϸµÄÑо¿Ò»Ï¡£ConvertÕâ¸öº¯ÊýµÄ¹¦ÄܺÜÇ¿´ó£¬¸ñʽÓֺܼòµ¥CONVERT ( data_type [ ( length ) ] , expression [ , style ] )¡£µ¥¾Í½«stringµ½datetimeÀàÐ͵Äת»»¾ÍÓкܶàÑùʽ¡£Èç: Convert(datetime, expression), ......
ÊÓͼÊÇ¿ÉÊÓ»¯µÄ±í¡£
±¾Õ½²½âÈçºÎ´´½¨¡¢¸üкÍɾ³ýÊÓͼ¡£
SQL CREATE VIEW Óï¾ä
ʲôÊÇÊÓͼ£¿
ÔÚ SQL ÖУ¬ÊÓͼÊÇ»ùÓÚ SQL Óï¾äµÄ½á¹û¼¯µÄ¿ÉÊÓ»¯µÄ±í¡£
ÊÓͼ°üº¬ÐкÍÁУ¬¾ÍÏñÒ»¸öÕæʵµÄ±í¡£ÊÓͼÖеÄ×ֶξÍÊÇÀ´×ÔÒ»¸ö»ò¶à¸öÊý¾Ý¿âÖеÄÕæʵµÄ±íÖеÄ×ֶΡ£ÎÒÃÇ¿ÉÒÔÏòÊÓͼÌí¼Ó SQL º¯Êý¡¢WHERE ÒÔ¼° JOIN Óï¾ä£¬ÎÒÃÇÒ²¿ÉÒ ......
asp.net(C#)ʵÏÖSQL2000Êý¾Ý¿â±¸·ÝºÍ»¹Ô
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.Htm ......
¸Õ×°ÍêÊý¾Ý¿â¾Í±»ÈË·è¿ñɨÃèsa£¬ÎÞ±ÈÓôÃÆ¡£
¸ÄsaÃû³Æ£¬1433¶Ë¿ÚºÅ£¬ÒòΪ¿ª·¢ÐèÒª»¹±ØÐë´ò¿ªÔ¶³ÌÁ¬½Ó£¬½á¹ûÔÚ¿Í»§¶ËÎÞÂÛÈçºÎ¶¼²»ÄÜÁ¬½ÓÉÏ·þÎñÆ÷¡£
¿ñº¹Çë°Ù¶È´óÉñ³öÂí£¬°Ñ¸÷ÖÖÅäÖÃÁ·Á˸öÊ죬½á¹û»¹ÊÇû½â¾ö¡£
¸ãЦµÄÊÇ×îºóÎÞÒâÖÐÔÚMicrosoftÂÛ̳ÉÏ¿´µ½Õâôһ¾ä£¬ÐÞ¸ÄÍê¶Ë¿ÚºÅ£¬ÔÚ¿Í»§¶ËµÇ¼µÄʱºòÒªÔÚIPºóÃ棫¶ººÅ£«¶Ë¿Úº ......