--> 测试数据：[a_WeightTab]
IF OBJECT_ID('[a_WeightTab]') IS NOT NULL DROP TABLE [a_WeightTab]
GO
CREATE TABLE [a_WeightTab]([c_TypeNo] VARCHAR(2),[c_MachineId] VARCHAR(2),[c_Weight] NUMERIC(4,2),[c_date] DATETIME)
INSERT [a_WeightTab]
SELECT '01','02',25.31,'2012-07-13 17:39:27' UNION ALL
SELECT '01','02',24.98,'2012-07-13 17:38:27' UNION ALL
SELECT '02','07',27.09,'2012-07-13 17:31:27' UNION ALL
SELECT '02','07',27.98,'2012-07-13 17:31:27'
GO

--> 测试数据：[a_TyretypeTab]
IF OBJECT_ID('[a_TyretypeTab]') IS NOT NULL DROP TABLE [a_TyretypeTab]
GO
CREATE TABLE [a_TyretypeTab]([c_TypeNo] VARCHAR(2),[c_TypeName] VARCHAR(5),[c_Tyre] VARCHAR(4),[c_StandWeight] NUMERIC(4,2),[c_UpWeight] NUMERIC(4,2),[c_DoWeight] NUMERIC(4,2))
INSERT [a_TyretypeTab]
SELECT '01','aaaaa','nx01',25.00,25.50,24.50 UNION ALL
SELECT '02','bbbbb','nxo2',27.99,28.49,27.49 UNION ALL
SELECT '03','ccccc','nx03',20.50,21.00,20.00
GO

--> 测试语句：

SELECT  w.[c_TypeNo],w.[c_MachineId],t.[c_Tyre],t.[c_StandWeight],
总合计=count(*),
合格数=sum(case when w.[c_Weight]=t.[c_StandWeight] then 1 else 0 end),
超上限数=sum(case when w.[c_Weight]<t.[c_UpWeight] then 1 else 0 end),
低下限数=sum(case when w.[c_Weight]>t.[c_DoWeight] then 1 else 0 end)
FROM [a_WeightTab] w, [a_TyretypeTab] t 
where w.[c_TypeNo]=t.[c_TypeNo]
group by w.[c_TypeNo],w.[c_MachineId],t.[c_Tyre],t.[c_StandWeight]
/*
c_TypeNo c_MachineId c_Tyre c_StandWeight                           总合计         合格数         超上限数        低下限数
-------- ----------- ------ --------------------------------------- ----------- ----------- ----------- -----------
01       02          nx01   25.00                                   2           0           2           2
02       07          nxo2   27.99                                   2           0           2           1

(2 行受影响)

*/

------解决方案--------------------

SQL code

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'a_WeightTab')
BEGIN
    DROP TABLE a_WeightTab
END
GO
CREATE TABLE a_WeightTab
(
    c_TypeNo varchar(6),
    c_MachineId varchar(6),
    c_Weight float,
    c_date datetime
)
GO
INSERT INTO a_WeightTab
SELECT '01', '02', 25.31, '2012-07-13 17:39:27' UNION
SELECT '01', '02', 24.98, '2012-07-13 17:38:27' UNION
SELECT '02', '07', 27.09, '2012-07-13 17:31:27' UNION  
SELECT '02', '07', 27.98, '2012-07-13 17:31:27'
GO
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'a_TyretypeTab')
BEGIN
    DROP TABLE a_TyretypeTab
END
GO
CREATE TABLE a_TyretypeTab
(
    c_TypeNo varchar(6),
    c_TypeName VARCHAR(100),
    c_Tyre VARCHAR(100),
    c_StandWeight float,
    c_UpWeight float,
    c_DoWeight float
)
GO
INSERT INTO a_TyretypeTab
SELECT '01', 'aaaaa', 'nx01', 25.00, 25.50, 24.50 UNION
SELECT '02', 'bbbbb', 'nxo2', 27.99, 28.49, 27.49 UNION
SELECT '03', 'ccccc', 'nx03', 20.50 ,21.00, 20.00
GO

SELECT    c_MachineId, 
        A.c_TypeNo, 
        c_TypeName, 
        c_Tyre, 
        c_StandWeight, 
        COUNT(1) AS 总合计, 
        SUM(CASE WHEN c_Weight BETWEEN c_DoWeight AND c_UpWeight THEN 1
                 ELSE 0 END) AS 合格数,