时间戳

首页 > 解决方案 > 无法组合 GROUP BY、ORDER BY 和 UNION

sql - 无法组合 GROUP BY、ORDER BY 和 UNION

问题描述

SELECT TOP 1 o.OrderID,e.FirstName+''+e.Lastname,COUNT(DISTINCT p.ProductID) AS [NUMBER OF PRODUCTS], DATEDIFF(DAY,o.OrderDate,o.RequiredDate) AS [DAYS TAKEN FOR DELIVERY], s.CompanyName 
  FROM Orders o, Shippers s, Products p, Employee e, OrderDetails oe 
 WHERE o.ShipperID=s.ShipperID AND e.EmployeeID=o.EmployeeID 
   AND o.OrderID=oe.OrderID AND oe.ProductID=p.ProductID
 GROUP BY o.OrderID,s.CompanyName,o.OrderDate,o.RequiredDate,e.FirstName,e.LastName
 ORDER BY ABS(DATEDIFF(DAY,o.OrderDate,o.RequiredDate)) ASC
UNION
SELECT TOP 1 o.OrderID,e.FirstName+''+e.Lastname,COUNT(DISTINCT p.ProductID) AS [NUMBER OF PRODUCTS], DATEDIFF(DAY,o.OrderDate,o.RequiredDate) AS [DAYS TAKEN FOR DELIVERY], s.CompanyName 
  FROM Orders o, Shippers s, Products p, Employee e, OrderDetails oe 
 WHERE o.ShipperID=s.ShipperID AND e.EmployeeID=o.EmployeeID 
   AND o.OrderID=oe.OrderID AND oe.ProductID=p.ProductID
GROUP BY o.OrderID,s.CompanyName,o.OrderDate,o.RequiredDate,e.FirstName,e.LastName
ORDER BY ABS(DATEDIFF(DAY,o.OrderDate,o.RequiredDate)) DESC;

标签： sqlsql-servergroup-bysql-order-byunion

解决方案

使用括号如下：

SELECT * FROM
(SELECT TOP 1 o.OrderID,e.FirstName+''+e.Lastname,COUNT(DISTINCT p.ProductID) AS [NUMBER OF PRODUCTS], DATEDIFF(DAY,o.OrderDate,o.RequiredDate) AS [DAYS TAKEN FOR DELIVERY], s.CompanyName 
  FROM Orders o, Shippers s, Products p, Employee e, OrderDetails oe 
 WHERE o.ShipperID=s.ShipperID AND e.EmployeeID=o.EmployeeID 
   AND o.OrderID=oe.OrderID AND oe.ProductID=p.ProductID
 GROUP BY o.OrderID,s.CompanyName,o.OrderDate,o.RequiredDate,e.FirstName,e.LastName
 ORDER BY ABS(DATEDIFF(DAY,o.OrderDate,o.RequiredDate)) ASC)
UNION
(SELECT TOP 1 o.OrderID,e.FirstName+''+e.Lastname,COUNT(DISTINCT p.ProductID) AS [NUMBER OF PRODUCTS], DATEDIFF(DAY,o.OrderDate,o.RequiredDate) AS [DAYS TAKEN FOR DELIVERY], s.CompanyName 
  FROM Orders o, Shippers s, Products p, Employee e, OrderDetails oe 
 WHERE o.ShipperID=s.ShipperID AND e.EmployeeID=o.EmployeeID 
   AND o.OrderID=oe.OrderID AND oe.ProductID=p.ProductID
GROUP BY o.OrderID,s.CompanyName,o.OrderDate,o.RequiredDate,e.FirstName,e.LastName
ORDER BY ABS(DATEDIFF(DAY,o.OrderDate,o.RequiredDate)) DESC);

推荐阅读