Biegral Blog

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 ) --7-2 select distinct od.productid,(select count(productid) from [order details] where productid =od.productid) from [order details] od order by productid --7-3 select ProductName,productid AS 产品名称, (select count(productid) FROM [Order Details] od WHERE od.ProductID = ProductID and Products.Productid = od.productid) as 订购次数 FROM products --7-4 SELECT ProductID,UnitPrice,(SELECT AVG(UnitPrice) FROM [Order Details] WHERE [Order Details].ProductID = Products.ProductID) AS AvgPrice, (SELECT MIN(UnitPrice) FROM [Order Details] WHERE [Order Details].ProductID =Products.ProductID) AS MaxPrice, ProductName FROM Products WHERE CategoryID = 1 --8 SELECT ProductName As 产品名称, ProductID As 产品号, UnitPrice As 产品单元价, (SELECT AVG(UnitPrice)FROM [Order Details] WHERE [Order Details].ProductID =Products.ProductID) AS 平均单价 FROM Products WHERE EXISTS ( SELECT * FROM [Order Details] WHERE [Order Details].ProductID = Products.productID ) AND CategoryID = 1 --11-1 select count(Productid) as 订购chai产品的订单数 from [order details] where productid =( select productid from products where productname ='chai') --12 Create table COLLECTTABLE ( itemName varchar(10), itemID int, itemtype char, itemQuantity int ) insert into collecttable values ('item001',1,1,100) insert into collecttable values ('item002',2,1,50) insert into collecttable values ('item003',3,2,30) insert into collecttable values ('item004',4,2,40) insert into collecttable values ('item001',5,1,100) insert into collecttable values ('item001',6,2,50) insert into collecttable values ('item002',7,1,100) insert into collecttable values ('item002',8,2,50) --13 select itemname,itemtype,sum(itemquantity) as 总数量, GROUPING(itemname) as itemname汇总记录数, GROUPING(itemtype) as itemtype汇总记录数 from collecttable group by itemname,itemtype with ROLLUP ORDER BY itemname DESC ----- sql帮助找到 将 GROUP BY 子句用于多个表 下面的示例检索与 EmployeeAddress 表联接的 Address 表中的各 City 的雇员数。 复制代码 USE AdventureWorks; GO SELECT a.City, COUNT(ea.AddressID) EmployeeCount FROM HumanResources.EmployeeAddress ea INNER JOIN Person.Address a ON ea.AddressID = a.AddressID GROUP BY a.City ORDER BY a.City; 以下示例使用 DATEPART 函数检索每年的销售总额。SELECT 列表和 GROUP BY 子句中必须有相同的表达式。 复制代码 USE AdventureWorks; GO SELECT DATEPART(yyyy,OrderDate) AS N'Year' ,SUM(TotalDue) AS N'Total Order Amount' FROM Sales.SalesOrderHeader GROUP BY DATEPART(yyyy,OrderDate) ORDER BY DATEPART(yyyy,OrderDate); 将 GROUP BY 子句与 HAVING 子句一起使用 下面的示例使用 HAVING 子句来指定应当将 GROUP BY 子句中生成的哪个组包括在结果集内。 复制代码 USE AdventureWorks; GO SELECT DATEPART(yyyy,OrderDate) AS N'Year' ,SUM(TotalDue) AS N'Total Order Amount' FROM Sales.SalesOrderHeader GROUP BY DATEPART(yyyy,OrderDate) HAVING DATEPART(yyyy,OrderDate) >= N'2003' ORDER BY DATEPART(yyyy,OrderDate);