SQL分类汇总（高分求解决）

Question

本帖最后由 yuanwei90315 于 2011-12-08 16:24:11 编辑

Answer 1

加一个部门ID,然后以ID排序,总计字段用一个最大的ID:
select * from(
SELECT    Department.DepartmentID,
Department.DepartmentName as 接单部门,
           Employee.EmployeeName as 接单人员,
           COUNT(Orders.OrderNO) as 订单量, 
           (Customers.CustomerName1 + '  ' + Customers.CustomerName2) AS 客户姓名,
           sum(Orders.OrderSuitePrice) 套系金额,
           sum(OrdersPayState.ActualSuite) 已付金额, 
           sum(OrdersPayState.PayableSuite) 未付金额,
           Orders.OrderDate 订单日期,
           Orders.OrderDepartmentNO
FROM       Customers LEFT OUTER JOIN
           Department INNER JOIN
           Employee INNER JOIN
           Orders ON Employee.EmployeeNO = Orders.OrderEmployeeNO ON Department.DepartmentNO = Orders.OrderDepartmentNO ON 
           Customers.CustomerNO = Orders.CustomerNO FULL OUTER JOIN
           OrdersPayState ON Orders.OrderNO = OrdersPayState.OrderNO
WHERE     (Orders.IsDelete = 0) AND (Orders.OrderState = 1)
GROUP BY  Department.DepartmentName, Employee.EmployeeName,Customers.CustomerName1,Customers.CustomerName2,Orders.OrderDate,Orders.OrderDepartmentNO
union all 
SELECT    Department.DepartmentID,
Department.DepartmentName 接单部门,
          '小计' , 
          count(Orders.OrderNO) as 订单数量 ,
          (Customers.CustomerName1 + '  ' + Customers.CustomerName2) AS 客户姓名, 
          sum(Orders.OrderSuitePrice) 套系金额,
          sum(OrdersPayState.ActualSuite) 已付金额, 
          sum(OrdersPayState.PayableSuite) 未付金额,
          Orders.OrderDate 订单日期, 
          Orders.OrderDepartmentNO
FROM      Customers LEFT OUTER JOIN
          Department INNER JOIN
          Employee INNER JOIN
          Orders ON Employee.EmployeeNO = Orders.OrderEmployeeNO ON Department.DepartmentNO = Orders.OrderDepartmentNO ON 
          Customers.CustomerNO = Orders.CustomerNO FULL OUTER JOIN
          OrdersPayState ON Orders.OrderNO = OrdersPayState.OrderNO
WHERE     (Orders.IsDelete = 0) AND (Orders.OrderState = 1)
GROUP BY  Department.DepartmentName,Customers.CustomerName1,Customers.CustomerName2,Orders.OrderDate,Orders.OrderDepartmentNO
union all
SELECT    10000000,
'合计' 接单部门,           
          '合计' , 
          count(Orders.OrderNO) as 订单数量 ,
          ' ' ,
          sum(Orders.OrderSuitePrice) 套系金额, 
          sum(OrdersPayState.ActualSuite) 已付金额, 
          sum(OrdersPayState.PayableSuite) 未付金额,
          '' , 
          '' 
FROM      Customers LEFT OUTER JOIN
          Department INNER JOIN
          Employee INNER JOIN
          Orders ON Employee.EmployeeNO = Orders.OrderEmployeeNO ON Department.DepartmentNO = Orders.OrderDepartmentNO ON 
          Customers.CustomerNO = Orders.CustomerNO FULL OUTER JOIN
          OrdersPayState ON Orders.OrderNO = OrdersPayState.OrderNO
WHERE     Orders.IsDelete = 0 AND Orders.OrderState = 1
) A
ORDER BY 1,A.接单部门,A.接单人员

Answer 2

又提示这个东东了 在将 varchar 值 'Administrators' 转换成数据类型 int 时失败。