'子查询后要加别名
SELECT packageID, unName, unMetal, COUNT(*) AS num, MIN(checkID)
+ '-' + MAX(checkID) AS CheckIDs, batchID
FROM (
SELECT dbo.tbDiamond_Rf_T.batchID, dbo.tbDiamond_Rf_T.checkID,
dbo.tbDiamond_Rf_T.packageID, dbo.tbDiamond_Rf_T.unName,
dbo.tbDiamond_Rf_T.unMetal
FROM dbo.tbDiamond_Rf_T
UNION
SELECT dbo.tbDiamond_Rf.batchID, dbo.tbDiamond_Rf.checkID,
dbo.tbDiamond_Rf.packageID, dbo.tbDiamond_Rf.unName,
dbo.tbDiamond_Rf.unMetal
FROM dbo.tbDiamond_Rf
) as temp
GROUP BY batchID, packageID, unName, unMetal
ORDER BY batchID, packageID
SELECT packageID, unName, unMetal, COUNT(*) AS num, MIN(checkID)
+ '-' + MAX(checkID) AS CheckIDs, batchID
FROM (
SELECT dbo.tbDiamond_Rf_T.batchID, dbo.tbDiamond_Rf_T.checkID,
dbo.tbDiamond_Rf_T.packageID, dbo.tbDiamond_Rf_T.unName,
dbo.tbDiamond_Rf_T.unMetal
FROM dbo.tbDiamond_Rf_T
UNION
SELECT dbo.tbDiamond_Rf.batchID, dbo.tbDiamond_Rf.checkID,
dbo.tbDiamond_Rf.packageID, dbo.tbDiamond_Rf.unName,
dbo.tbDiamond_Rf.unMetal
FROM dbo.tbDiamond_Rf
) as temp
GROUP BY batchID, packageID, unName, unMetal
ORDER BY batchID, packageID
+ '-' + ltrim(str(MAX(checkID))) AS CheckIDs, batchID
FROM (
SELECT dbo.tbDiamond_Rf_T.batchID, dbo.tbDiamond_Rf_T.checkID,
dbo.tbDiamond_Rf_T.packageID, dbo.tbDiamond_Rf_T.unName,
dbo.tbDiamond_Rf_T.unMetal
FROM dbo.tbDiamond_Rf_T
UNION
SELECT dbo.tbDiamond_Rf.batchID, dbo.tbDiamond_Rf.checkID,
dbo.tbDiamond_Rf.packageID, dbo.tbDiamond_Rf.unName,
dbo.tbDiamond_Rf.unMetal
FROM dbo.tbDiamond_Rf
)
GROUP BY batchID, packageID, unName, unMetal
ORDER BY batchID, packageID