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 SERVERµÄ·ÖÀà»ã×Ü
SQL SERVERÖÐʹÓÃGROUP BY¶ÔÊý¾Ý½øÐзÖÀà»ã×Ü£¬ÎÒÃÇÒ²¿ÉÒÔʹÓÃWITH ROLLUPºÍWITH CUBEÅäºÏGROUP BY½øÐГÔöÇ¿”Á˵ķÖÀà»ã×Ü£¬ÄÇôËûÃÇÁ½¸öÊÇÈçºÎÔöÇ¿GROUP BYµÄ»ã×ÜÄÜÁ¦µÄÄØ£¿
Ò».¹¦ÄÜÔöÇ¿
1.ʹÓÃWITH ROLLUP
ÓÃÏÂÃæµÄÀý×Ó˵Ã÷,GROUPʹÓÃÁË3¸ö·Ö×é×ֶΣºGROUP BY A, B, C WITH ROLLUP ......
±¾ÎĽéÉÜÁËSQL Server 2008ÀïһЩеÄÌØµãÒÔ¼°·¢ÏÖµÄһЩ¾«ÃîÖ®´¦……
¡¡¡¡°²×°
¡¡¡¡SQL
Server
2008µÄÉèÖúͰ²×°Ò²ÓÐËù¸Ä½ø¡£ÅäÖÃÊý¾ÝºÍÒýÇæÎ»ÒѾ·Ö¿ªÁË£¬ËùÒÔËüʹ´´½¨»ù±¾µÄδÅäÖÃϵͳµÄ´ÅÅÌͼÏñ±äµÃ¿ÉÄÜÁË£¬Ëüʹ·Ö²¼µ½¶à¸ö·þÎñÆ÷±äµÃ¸üÈÝÒ×ÁË¡£
´Ó΢ÈíµÄÕ¾µãÒ²¿ÉÒÔÕÒµ½°²×°¿ÉÓõÄ×îиüС£ÁíÒ»¸öÌØµ ......
±¾ÎĽÚÑ¡×ÔMSDNµÄÎÄÕ¡¶ÎåÖÖÌá¸ß SQL ÐÔÄܵķ½·¨¡·£¬Ìá³öÈçºÎÌá¸ß»ùÓÚSQL ServerÓ¦ÓóÌÐòµÄÔËÐÐЧÂÊ£¬·Ç³£ÖµµÃÍÆ¼ö¡£¶ÔһЩTrafficºÜ¸ßµÄÓ¦ÓÃϵͳ¶øÑÔ£¬ÈçºÎÌá¸ßºÍ¸Ä½øSQLÖ¸ÁÊǷdz£ÖØÒªµÄ£¬Ò²ÊÇÒ»¸öºÜºÃµÄÍ»ÆÆµã¡£
*ÎÄÕÂÖ÷Òª°üÀ¨ÈçÏÂһЩÄÚÈÝ£¨Èç¸ÐÐËȤ£¬ÇëÖ±½Ó·ÃÎÊÏÂÃæµÄURLÔĶÁÍêÕûµÄÖÐÓ¢ÎÄÎĵµ£©£º
1, ´Ó INSE ......
ʹÓÃSQL ServerÉí·ÝÑéÖ¤µÇ¼ʱ±¨´í£º
ÎÞ·¨Á¬½Óµ½.\SQLEXPRESS.
Additional information:
Óû§'sa'µÇ¼ʧ°Ü¡£¸ÃÓû§Óë¿ÉÐÅSQL Server Á¬½ÓÎÞ¹ØÁª¡£(microsoft SQL Server,Error:18452£©)
------------------
ÏÂÃæÊÇÓ³Éäʱ±¨µÄ´í£º
´´½¨¶ÔÓÚÓû§“sa”ʧ°Ü¡££¨microsoft.sqlServer.express.smo£©
Additional ......
Ò»¡¢Êʺ϶ÁÕß¶ÔÏó£ºÊý¾Ý¿â¿ª·¢³ÌÐòÔ±£¬Êý¾Ý¿âµÄÊý¾ÝÁ¿ºÜ¶à£¬Éæ¼°µ½¶ÔSP£¨´æ´¢¹ý³Ì£©µÄÓÅ»¯µÄÏîÄ¿¿ª·¢ÈËÔ±£¬¶ÔÊý¾Ý¿âÓÐŨºñÐËȤµÄÈË¡£ ¡¡
¶þ¡¢½éÉÜ£ºÔÚÊý¾Ý¿âµÄ¿ª·¢¹ý³ÌÖУ¬¾³£»áÓöµ½¸´ÔÓµÄÒµÎñÂß¼ºÍ¶ÔÊý¾Ý¿âµÄ²Ù×÷£¬Õâ¸öʱºò¾Í»áÓÃSPÀ´·â×°Êý¾Ý¿â²Ù×÷¡£Èç¹ûÏîÄ¿µÄSP½Ï¶à£¬ÊéдÓÖûÓÐÒ»¶¨µÄ¹æ·¶£¬½«»áÓ°ÏìÒÔºóµÄϵͳά»¤ ......