SQL VIEW£¨ÊÓͼ£©
ÊÓͼÊÇ¿ÉÊÓ»¯µÄ±í¡£
±¾Õ½²½âÈçºÎ´´½¨¡¢¸üкÍɾ³ýÊÓͼ¡£
SQL CREATE VIEW Óï¾ä
ʲôÊÇÊÓͼ£¿
ÔÚ SQL ÖУ¬ÊÓͼÊÇ»ùÓÚ SQL Óï¾äµÄ½á¹û¼¯µÄ¿ÉÊÓ»¯µÄ±í¡£
ÊÓͼ°üº¬ÐкÍÁУ¬¾ÍÏñÒ»¸öÕæʵµÄ±í¡£ÊÓͼÖеÄ×ֶξÍÊÇÀ´×ÔÒ»¸ö»ò¶à¸öÊý¾Ý¿âÖеÄÕæʵµÄ±íÖеÄ×ֶΡ£ÎÒÃÇ¿ÉÒÔÏòÊÓͼÌí¼Ó SQL º¯Êý¡¢WHERE ÒÔ¼° JOIN Óï¾ä£¬ÎÒÃÇÒ²¿ÉÒÔÌá½»Êý¾Ý£¬¾ÍÏñÕâЩÀ´×ÔÓÚij¸öµ¥Ò»µÄ±í¡£
×¢ÊÍ£ºÊý¾Ý¿âµÄÉè¼ÆºÍ½á¹¹²»»áÊܵ½ÊÓͼÖеĺ¯Êý¡¢where »ò join Óï¾äµÄÓ°Ïì¡£
SQL CREATE VIEW Óï·¨
CREATE VIEW view_name AS
SELECT column_name(s)
from table_name
WHERE condition
×¢ÊÍ£ºÊÓͼ×ÜÊÇÏÔʾ×î½üµÄÊý¾Ý¡£Ã¿µ±Óû§²éѯÊÓͼʱ£¬Êý¾Ý¿âÒýÇæͨ¹ýʹÓà SQL Óï¾äÀ´Öؽ¨Êý¾Ý¡£
SQL CREATE VIEW ʵÀý
¿ÉÒÔ´Óij¸ö²éѯÄÚ²¿¡¢Ä³¸ö´æ´¢¹ý³ÌÄÚ²¿£¬»òÕß´ÓÁíÒ»¸öÊÓͼÄÚ²¿À´Ê¹ÓÃÊÓͼ¡£Í¨¹ýÏòÊÓͼÌí¼Óº¯Êý¡¢join µÈµÈ£¬ÎÒÃÇ¿ÉÒÔÏòÓû§¾«È·µØÌá½»ÎÒÃÇÏ£ÍûÌá½»µÄÊý¾Ý¡£
Ñù±¾Êý¾Ý¿â Northwind ÓµÓÐһЩ±»Ä¬ÈÏ°²×°µÄÊÓͼ¡£ÊÓͼ "Current Product List" »á´Ó Products ±íÁгöËùÓÐÕýÔÚʹÓõIJúÆ·¡£Õâ¸öÊÓͼʹÓÃÏÂÁÐ SQL ´´½¨£º
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
from Products
WHERE Discontinued=No
ÎÒÃÇ¿ÉÒÔ²éѯÉÏÃæÕâ¸öÊÓͼ£º
SELECT * from [Current Product List]
Northwind Ñù±¾Êý¾Ý¿âµÄÁíÒ»¸öÊÓͼ»áÑ¡È¡ Products ±íÖÐËùÓе¥Î»¼Û¸ñ¸ßÓÚƽ¾ùµ¥Î»¼Û¸ñµÄ²úÆ·£º
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
from Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) from Products)
ÎÒÃÇ¿ÉÒÔÏñÕâÑù²éѯÉÏÃæÕâ¸öÊÓͼ£º
SELECT * from [Products Above Average Price]
ÁíÒ»¸öÀ´×Ô Northwind Êý¾Ý¿âµÄÊÓͼʵÀý»á¼ÆËãÔÚ 1997 Äêÿ¸öÖÖÀàµÄÏúÊÛ×ÜÊý¡£Çë×¢Ò⣬Õâ¸öÊÓͼ»á´ÓÁíÒ»¸öÃûΪ "Product Sales for 1997" µÄÊÓͼÄÇÀïÑ¡È¡Êý¾Ý£º
CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
from [Product Sales for 1997]
GROUP BY CategoryName
ÎÒÃÇ¿ÉÒÔÏñÕâÑù²éѯÉÏÃæÕâ¸öÊÓͼ£º
SELECT * from [Category Sales For 1997]
ÎÒÃÇÒ²¿ÉÒÔÏò²éѯÌí¼ÓÌõ¼þ¡£ÏÖÔÚ£¬ÎÒÃǽö½öÐèÒª²é¿´ "Beverages" ÀàµÄÈ«²¿ÏúÁ¿£º
SELECT * from [Category Sales For 1997]
WHERE CategoryName='Beverages'
SQL ¸üÐÂÊÓͼ
Äú¿ÉÒÔʹÓÃÏÂÃæµÄÓï·¨À´¸üÐÂÊÓÍ
Ïà¹ØÎĵµ£º
MagentoÊÇÔÚZend FrameworkµÄ»ù´¡Éϴ¶øÀ´£¬ÓÐÁ½ÖÖ·½Ê½¿ÉÒÔ´ÓMagentoµÄcollection»ñµÃÕæÕýµÄSQLÓï¾ä¡£
ÒÔ Mage::getResourceModel('reports/product_collection') ΪÀý£º
1
$collection=Mage::getResourceModel('reports/product_collection');
......
-- ======================================================
--ÁгöSQL SERVER ËùÓÐ±í£¬×Ö¶ÎÃû£¬Ö÷¼ü£¬ÀàÐÍ£¬³¤¶È£¬Ð¡ÊýλÊýµÈÐÅÏ¢
--ÔÚ²éѯ·ÖÎöÆ÷ÀïÔËÐм´¿É,¿ÉÒÔÉú³ÉÒ»¸ö±í£¬µ¼³öµ½EXCELÖÐ
-- ======================================================
SELECT
(case when a. ......
ÓÃT-SQL×öÊý¾Ý¿â·ÖÒ³²éѯҲÓкü¸ÄêÁË£¬µ«¶ÔÓÚ¸÷ÖÖ²éѯ·½·¨µÄд·¨Ò»ÖÁ¶¼Ã»ÔõôȥÀí»á£¬×î½ü²ÎÓëÁ˼¸¸öÏîÄ¿µÄ¿ª·¢£¬¼¸¸öÏîÄ¿ÖзÖÒ³²éѯµÄд·¨Ò²²»Ïàͬ£¬ÕâÒ²ÈÃÎÒ²úÉúÁËÈ·Èϼ¸ÖÖд·¨Ð§ÂÊÎÊÌâµÄÐËȤ¡££¨×¢£ºÎÒËù˵µÄ¼¸ÖÖд·¨¶¼ÊÇÐèÒª·µ»Ø×ܼǼÊýµÄ·ÖÒ³£©
1¡¢Éú³É²âÊÔÊý¾Ý
select a.* into test from sys.columns a,sys.colu ......
½ñÌìÔÚÓÃUNION½øÐн«Á½¸ö²éѯ½áºÏʱ£¬·¢ÏÖ¼ÓÁËtextÀàÐ͵Ä×ֶνøÈ¥¾Í±¨´íÁË£¬´íÎóÐÅÏ¢ÈçÏ£º
²»ÄÜÒÔ DISTINCT ·½Ê½Ñ¡Ôñ text¡¢ntext »ò image Êý¾ÝÀàÐÍ¡£
¾¹ý·ÔÄ×ÊÁϺó²ÅÖªµÀ£¬ÔÒòÔÚÓÚÈç´Ë²Ù×÷ ÎÞ·¨¶ÔtextÀàÐÍÅжÏÊÇ·ñÖظ´£¬ËùÒÔÒªÔÚUNIONºó¼Ó¸öALL¹Ø¼ü×Ö£¬ÈçÏ£º
select top ......