求SQL！！！

[_萧萧]

A表       B表
user name auser btime                   etime
1     a     1   2012-05-30 18:09:13.000 2012-05-30 18:09:15.000
2     b     2   2012-05-30 18:01:13.000 2012-05-30 18:02:15.000
3     c     1   2012-05-30 18:03:13.000 2012-05-30 18:04:15.000
4     d     3
5     e     3

user关联auser   btime-etime 10分钟内为合格
得到s（a存在B表记录多少），q（合格存在的几），d（不合格的几）
效果图如：
name s q d
a    3 2 1  
b    5 3 2
c    2 1 1

后续操作  
取得s，q，d 对应B表数据
如：
a 2012-05-30 18:09:13.000 2012-05-30 18:09:15.000
a 2012-05-30 18:09:13.000 2012-05-30 18:09:15.000
a 2012-05-30 18:09:13.000 2012-05-30 18:09:15.000

答案合理满意直接给分

[licai1210]

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]

[F15Eagle]

后续操作没看明白，以下是非后续操作，能执行，优化的话楼主自己搞搞吧

--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

[F15Eagle]

[Quote=引用 1 楼 的回复:]

SQL code

SELECT t1.[name],
Count(*) AS s,
(SELECT Count(*)
FROM b t3
WHERE t3.auser = Max(t1.[user])
AND Da……
[/Quote]

SELECT t1.[name],
Count(*) AS s,
(SELECT Count(*)
FROM b t3
WHERE t3.auser = Max(t1.[user])
AND Dateadd(minute, 10, btime) >= etime) AS q,
(SELECT Count(*)
FROM b t3
WHERE t3.auser = Max(t1.[user])
AND Dateadd(minute, 10, btime) < etime) AS d
FROM a t1,
b t2
WHERE t1.[user] = t2.auser
GROUP BY t1.[name]

把day改成minute，楼主要的是分钟，这个大哥速度好快噻

[licai1210]

SELECT t1.[name], 
       Count(*) AS s, 
       Sum(CASE 
             WHEN Datediff(minute, t2.btime, etime) <=10 THEN 1 
             ELSE 0 
           END) AS q, 
       Sum(CASE 
             WHEN Datediff(minute, t2.btime, etime) > 10 THEN 1 
             ELSE 0 
           END) AS d 
FROM   a t1, 
       b t2 
WHERE  t1.[user] = t2.auser 
GROUP  BY t1.[name] 

屎上最快的

[_萧萧]

楼上的两位 谢谢了 刚找到答案了 但还是感谢啊 试了下 发现都还可以

[指间的风]

sql区 找 叶子