基表和数据表数据统计[问题点数:100分]数据表（a_WeightTab）c_TypeNo(varchar(6)) c_MachineId(varchar(6))
基表和数据表数据统计[问题点数:100分]
数据表（a_WeightTab）
c_TypeNo(varchar(6)) c_MachineId(varchar(6)) c_Weight(float) c_date(datetime)
01 02 25.31 2012-07-13 17:39:27
01 02 24.98 2012-07-13 17:38:27
02 07 27.09 2012-07-13 17:31:27
02 07 27.98 2012-07-13 17:31:27
... ... .... ......
基表（a_TyretypeTab）
c_TypeNo(varchar(6)) c_TypeName c_Tyre c_StandWeight c_UpWeight c_DoWeight
01 aaaaa nx01 25.00 25.50 24.50
02 bbbbb nxo2 27.99 28.49 27.49
03 ccccc nx03 20.50 21.00 20.00
用c_TypeNo关联查询，得到
c_MachineId c_TypeNo c_TypeName c_Tyre c_StandWeight 总合计 合格数 超上限数 低下限数
请各位给个主意
QQ：41278515
[解决办法]
是下面这个样子吗？不是应该也差不多，你自己改下吧
- SQL code
--> 测试数据：[a_WeightTab]IF OBJECT_ID('[a_WeightTab]') IS NOT NULL DROP TABLE [a_WeightTab]GOCREATE 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 ALLSELECT '01','02',24.98,'2012-07-13 17:38:27' UNION ALLSELECT '02','07',27.09,'2012-07-13 17:31:27' UNION ALLSELECT '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]GOCREATE 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 ALLSELECT '02','bbbbb','nxo2',27.99,28.49,27.49 UNION ALLSELECT '03','ccccc','nx03',20.50,21.00,20.00GO--> 测试语句：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 202 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_WeightTabENDGOCREATE TABLE a_WeightTab( c_TypeNo varchar(6), c_MachineId varchar(6), c_Weight float, c_date datetime)GOINSERT INTO a_WeightTabSELECT '01', '02', 25.31, '2012-07-13 17:39:27' UNIONSELECT '01', '02', 24.98, '2012-07-13 17:38:27' UNIONSELECT '02', '07', 27.09, '2012-07-13 17:31:27' UNION SELECT '02', '07', 27.98, '2012-07-13 17:31:27'GOIF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'a_TyretypeTab')BEGIN DROP TABLE a_TyretypeTabENDGOCREATE TABLE a_TyretypeTab( c_TypeNo varchar(6), c_TypeName VARCHAR(100), c_Tyre VARCHAR(100), c_StandWeight float, c_UpWeight float, c_DoWeight float)GOINSERT INTO a_TyretypeTabSELECT '01', 'aaaaa', 'nx01', 25.00, 25.50, 24.50 UNIONSELECT '02', 'bbbbb', 'nxo2', 27.99, 28.49, 27.49 UNIONSELECT '03', 'ccccc', 'nx03', 20.50 ,21.00, 20.00GOSELECT 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 合格数, SUM(CASE WHEN c_Weight > c_UpWeight THEN 1 ELSE 0 END) AS 超上限数, SUM(CASE WHEN c_Weight < c_DoWeight THEN 1 ELSE 0 END) AS 低下限数FROM a_WeightTab AS A INNER JOIN a_TyretypeTab AS B ON A.c_TypeNo = B.c_TypeNoGROUP BY c_MachineId, A.c_TypeNo, c_TypeName, c_Tyre, c_StandWeight/*c_MachineId c_TypeNo c_TypeName c_Tyre c_StandWeight 总合计 合格数 超上限数 低下限数02 01 aaaaa nx01 25 2 2 0 007 02 bbbbb nxo2 27.99 2 1 0 1*/