SELECT c.Name AS PayMethodName, b.Name AS DepartmentName, 1 AS COUNT
FROM crm_Order a LEFT OUTER JOIN
crm_Group b ON b.ID = a.DepartmentID LEFT OUTER JOIN
crm_Enumerate c ON c.ID = a.PayMethod
WHERE (a.InsertDate BETWEEN '20070101' AND '20070102')
UNION ALL ------ 加个 all 试试SELECT '无' AS PayMethodName, crm_Group.Name AS DepartmentName,0 AS COUNT
FROM dbo.crm_Group
WHERE crm_Group.ParentID = 425
FROM crm_Order a LEFT OUTER JOIN
crm_Group b ON b.ID = a.DepartmentID LEFT OUTER JOIN
crm_Enumerate c ON c.ID = a.PayMethod
WHERE (a.InsertDate BETWEEN '20070101' AND '20070102')
UNION ALL ------ 加个 all 试试SELECT '无' AS PayMethodName, crm_Group.Name AS DepartmentName,0 AS COUNT
FROM dbo.crm_Group
WHERE crm_Group.ParentID = 425
FROM crm_Order a LEFT OUTER JOIN
crm_Group b ON b.ID = a.DepartmentID LEFT OUTER JOIN
crm_Enumerate c ON c.ID = a.PayMethod
WHERE (a.InsertDate BETWEEN '20070101' AND '20070102')
UNION ALLSELECT '无' AS PayMethodName, crm_Group.Name AS DepartmentName,0 AS COUNT
FROM dbo.crm_Group
WHERE crm_Group.ParentID = 425
SELECT distinct c.Name AS PayMethodName, b.Name AS DepartmentName, '1' AS [COUNT]
FROM crm_Order a LEFT OUTER JOIN
crm_Group b ON b.ID = a.DepartmentID LEFT OUTER JOIN
crm_Enumerate c ON c.ID = a.PayMethod
WHERE (a.InsertDate BETWEEN '20070101' AND '20070102')
UNION
SELECT distinct '无' AS PayMethodName, crm_Group.Name AS DepartmentName,'0' AS [COUNT]
FROM dbo.crm_Group
WHERE crm_Group.ParentID = 425