求SQL！！！
A表 B表user name auser btime etime1 a 1 2012-05-30 18:09:13.000 2012-05-30 18:09:15.0002 b 2 2012-05-30 18:01:13.000 2012-05-30 18:02:15.0003 c 1 2012-05-30 18:03:13.000 2012-05-30 18:04:15.0004 d 35 e 3user关联auser btime-etime 10分钟内为合格得到s（a存在B表记录多少），q（合格存在的几），d（不合格的几）效果图如：name s q da 3 2 1 b 5 3 2c 2 1 1后续操作 取得s，q，d 对应B表数据如：a 2012-05-30 18:09:13.000 2012-05-30 18:09:15.000a 2012-05-30 18:09:13.000 2012-05-30 18:09:15.000a 2012-05-30 18:09:13.000 2012-05-30 18:09:15.000
SELECT t1.[name], Count(*) AS s, (SELECT Count(*) FROM b t3 WHERE t3.auser = Max(t1.[user]) AND Dateadd(day, 10, btime) >= etime) AS q, (SELECT Count(*) FROM b t3 WHERE t3.auser = Max(t1.[user]) AND Dateadd(day, 10, btime) < etime) AS d FROM a t1, b t2 WHERE t1.[user] = t2.auser GROUP BY t1.[name]
[解决办法]
后续操作没看明白，以下是非后续操作，能执行，优化的话楼主自己搞搞吧
--A表建表语句CREATE TABLE [dbo].[A]( [user] [int] NULL, [name] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL) ON [PRIMARY]--B表建表语句CREATE TABLE [dbo].[B]( [auser] [int] NULL, [btime] [datetime] NULL, [etime] [datetime] NULL) ON [PRIMARY]--A表插入数据INSERT INTO A ([user],[name]) VALUES (1,'a')INSERT INTO A ([user],[name]) VALUES (2,'b')INSERT INTO A ([user],[name]) VALUES (3,'c')INSERT INTO A ([user],[name]) VALUES (4,'d')INSERT INTO A ([user],[name]) VALUES (5,'e')--B表插入数据INSERT INTO B ([auser],[btime],[etime]) VALUES (1,'2012-05-30 18:09:13.000','2012-05-30 18:09:15.000')INSERT INTO B ([auser],[btime],[etime]) VALUES (1,'2012-05-30 18:01:13.000','2012-05-30 18:02:15.000')INSERT INTO B ([auser],[btime],[etime]) VALUES (1,'2012-05-30 18:03:13.000','2012-05-30 18:14:15.000')INSERT INTO B ([auser],[btime],[etime]) VALUES (2,'2012-05-30 18:09:13.000','2012-05-30 18:12:13.000')INSERT INTO B ([auser],[btime],[etime]) VALUES (2,'2012-05-30 18:09:13.000','2012-05-30 18:09:15.000')INSERT INTO B ([auser],[btime],[etime]) VALUES (2,'2012-05-30 18:01:13.000','2012-05-30 18:02:15.000')INSERT INTO B ([auser],[btime],[etime]) VALUES (2,'2012-05-30 18:03:13.000','2012-05-30 18:14:15.000')INSERT INTO B ([auser],[btime],[etime]) VALUES (2,'2012-05-30 18:09:13.000','2012-05-30 18:22:13.000')INSERT INTO B ([auser],[btime],[etime]) VALUES (3,'2012-05-30 18:03:13.000','2012-05-30 18:04:15.000')INSERT INTO B ([auser],[btime],[etime]) VALUES (3,'2012-05-30 18:09:13.000','2012-05-30 18:22:13.000')--查询数据select * from A select * from B--查询结果select name,q+d as s,q,d from (select name,q=isnull(sum(case zt when '合格' then num end),0),d=isnull(sum(case zt when '不合格' then num end),0) from (select A.name,D.zt,D.num from A left join (select auser,zt,count(*) num from (select auser,case when datediff(minute,btime,etime)>10 then '不合格' else '合格' end as zt from B) C group by auser,zt) D on A.[user]=D.auser where zt is not null) E group by name) F
