sql choch02
ch02
--3-4
select * from orders
where 'México D.F.' in
(select City
from Customers
where Orders.CustomerID = Customers.CustomerID )
--3-5
select * from orders
where 'usa' =
(select Country
from Customers
where Orders.CustomerID = Customers.CustomerID )
--4-1
select * from orders
where Exists
(select *
from Customers
where Orders.CustomerID = Customers.CustomerID
and City='México D.F.')
--4-2
select Customers.companyname, Suppliers.companyname,Customers.City from customers,suppliers
where customers.city = suppliers.city
order by city asc
select CompanyName, city from Customers
where City = any
(Select City from Suppliers
where Customers.city = Suppliers.city)
order by city asc
select CompanyName, city from Customers
where exists
(Select * from Suppliers
where Customers.city = Suppliers.city)
order by city asc
--5-1
select CompanyName from Customers
where CustomerID in
(
select CustomerID from Orders
where OrderID in
(
select OrderID from [Order Details]
where quantity >all(
select quantity from Customers c,Orders O,[order details] od
where c.CustomerID = O.CustomerID and O.OrderID = od.OrderID and
CompanyName ='Around the Horn')
)
)
--5-3
select CompanyName from Customers
where CustomerID in
(
select CustomerID from Orders
where OrderID in
(
select OrderID from [order details]
where quantity >any(
select quantity from Customers c,Orders O,[order details] od
where c.CustomerID = O.CustomerID and O.OrderID = od.OrderID and
CompanyName ='Around the Horn')
)
)
--5-6
select CompanyName from Customers
where CustomerID in
(
select CustomerID from Orders
where OrderID in
(
select OrderID from [order details]
where quantity >'3'
)
)
--5-7
select ProductName,UnitPrice from Products
where Exists
(
select * from Suppliers
where city='london' and suppliers.SupplierID = Products.SupplierID )
Ïà¹ØÎĵµ£º
ǰÁ½ÌìÒ»¸öѧ½ãÎÊÎÒ¸öSQLÓï¾äµÄÎÊÌ⣬ÏÖÔڰѽâ¾ö·½°¸Ìù³öÀ´£¬Ò²Ëã×ܽáһϰɡ£
ËýµÄÎÊÌâÊÇ£º“Ò»¸ö±íÖÐÓÐ15¸ö×ֶΣ¬ÓÃSQLÓï¾äÅÐ¶ÏÆäÖÐ5Ïî»òÒÔÉϲ»Îª¿Õ£¬ÔõôÅжϣ¬ºÜ¼±……”¡£
µ±Ê±Î񼆾£¬Ã»¶à¿´£¬ºÇºÇ£¬²»¹»Òâ˼À²¡£µ«ÎÒÇëͬʰïËýдÁ˸ö£¬ÒÔ½âȼü֮¼±£¬ÔÚ´ËҲллСÃס£
½ñÌìÔÙ°ÑQQÁÄÌì¼Ç¼· ......
Microsoft SQL Server
2008ºÍMicrosoftÊý¾Ýƽ̨Ϊ¿ª·¢ÈËÔ±ÌṩÁËÒ»¸ö×ۺϵıà³Ì¼Ü¹¹£¬ÓÃÓÚ´´½¨Õë¶ÔÒÆ¶¯É豸¡¢×ÀÃæµçÄÔ¡¢Web·þÎñÆ÷£¬ºÍÆóÒµ·þÎñÆ÷µÄ»ùÓÚÊý¾ÝµÄ½â
¾ö·½°¸¡£Õâ¸öÊý¾Ý±à³Ì»·¾³½«Êý¾Ý·ÃÎʺ͹ÜÀí¼¯³Éµ½Ò»¸öÓ¦ÓóÌÐòµÄÒµÎñÂß¼ÖУ¬´Ó¶øÌá¸ßÁË¿ª·¢ÈËÔ±µÄÉú²úÁ¦¡£ËüÌṩÁËÒ»¸öÓÃÓÚ¶ÔÒì¹¹Êý¾ÝÔ´µÄÊý¾Ý·ÃÎʺ͹ÜÀí
µÄ ......
SQL ServerÀïµÄ ISNULL Óë Oracle ÖÐµÄ NULLIF²»Í¬:
SQL Server ÖÐÓÐÁ½¸ö²ÎÊý£¬Óï·¨£º
ISNULL(check_expression, replacement_value)
check_expression Óë replacement_value Êý¾ÝÀàÐͱØÐëÒ»ÖÂ
Èç¹û check_expression Ϊ NULL£¬Ôò·µ»Ø replacement_v ......
×ªÔØ×Ô£ºhttp://www.cnblogs.com/leonbao/archive/2008/03/07/1094821.html
¹ØÓÚSQL ServerÊý¾Ý¿âÉè¼ÆµÄ¸ÐÎò£¬ÇëÖ¸½Ì
ÓÐÎÊÌâµÄʱºò£¬ÎÒ¾³£»ØÀ´²©¿Í԰ѰÕҴ𰸣¬¾Ã¶ø¾ÃÖ®£¬×ܽáÁËһЩ¶«Î÷¡£
Íý×ԷƱ¡£¬Çë´ó¼Ò¶àÖ¸³ö´íÎ󣬲¢¸ø³öÒâ¼û
Êý¾Ý¿âÉè¼ÆÈý·¶Ê½»ù±¾ÔÔò
µÚÒ»·¶Ê½£ºÊý¾Ý¿â±íÖеÄ×ֶζ¼Êǵ¥Ò»ÊôÐԵ쬲»¿ÉÔÙ·Ö¡£Õâ ......
ǰÑÔ
±¾ÎĵµÖ÷Òª½éÉÜÓëSQLµ÷ÕûÓйصÄÄÚÈÝ£¬ÄÚÈÝÉæ¼°¶à¸ö·½Ã棺SQLÓï¾äÖ´ÐеĹý³Ì¡¢ORACLEÓÅ»¯Æ÷£¬±íÖ®¼äµÄ¹ØÁª£¬ÈçºÎµÃµ½SQLÖ´Ðмƻ®£¬ÈçºÎ·Ö ÎöÖ´Ðмƻ®µÈÄÚÈÝ£¬´Ó¶øÓÉdzµ½ÉîµÄ·½Ê½Á˽âSQLÓÅ»¯µÄ¹ý³Ì£¬Ê¹´ó¼ÒÖð²½²½ÈëSQLµ÷ÕûÖ®ÃÅ£¬È»ºóÄ㽫·¢ÏÖ……¡£
&nb ......