求教一条SQL语句

显示全部楼层 · 2007-10-20 08:38:44

表A有三列： ID (Identity,不重复)EmpID (工号)Days (天数)
表中的记录如下：
IDEmpID Days
1000110
2000110
3000215
4000215
5000215
6000215
7000215
8000215
9000215
10000310
11000311
12000312
13000312
14000312
15000312
16000312
17000312
18000312
求一条SQL语句，找出EmpID和Days记录相同，且相同记录数大于等于7的记录
即结果应该为0002从（3-9）的七条和 0003 从（12-18）的七条
谢谢~

千问 · 2007-10-20 08:38:44

declare @ttable(IDint,EmpID nvarchar(4),[Days] int)
insert into @t
select 1,'0001',10 UNION ALL
select 2,'0001',10 UNION ALL
select 3, '0002', 15 UNION ALL
select 4, '0002', 15UNION ALL
select 5,'0002', 15 UNION ALL
select 6, '0002', 15 UNION ALL
select 7,'0002', 15 UNION ALL
select 8, '0002', 15 UNION ALL
select 9,'0002', 15
UNION ALL
select 10, '0003', 10UNION ALL
select 11,'0003', 11
UNION ALL
select 12, '0003', 12
UNION ALL
select 13,'0003', 12 UNION ALL
select 14, '0003', 12UNION ALL
select 15,'0003', 12 UNION ALL
select 16, '0003', 12
UNION ALL
select 17,'0003 ',12 UNION ALL
select 18,'0003', 12
SELECT T.ID, T.EmpID, S.[Days] FROM
@t AS T
JOIN
(

SELECT EmpID, [Days] FROM

@t

GROUP BY EmpID, [Days]

HAVING COUNT(*) >= 7
) AS S
ON
T.EmpID = S.EmpID

AND T.[Days] = S.[Days]

/*

(18 row(s) affected)
ID
EmpID Days
----------- ----- -----------
3 000215
4 000215
5 000215
6 000215
7 000215
8 000215
9 000215
12
000312
13
000312
14
000312
15
000312
16
000312
17
000312
18
000312
(14 row(s) affected)

*/

千问 · 2007-10-20 08:38:44

因为有 ID 稍微优化一下
SELECT T.ID, T.EmpID, T.[Days] FROM
@t AS T
JOIN
(

SELECT MIN(ID) AS StartId,MAX(ID) AS EndId FROM

@t

GROUP BY EmpID, [Days]

HAVING COUNT(*) >= 7
) AS S
ON
T.ID >= S.StartId
AND
T.ID =7
or
select * from A as t1 where exists(select 1 from A where EmpID=t1.EmpID andDays=t1. Days having count(*)>=7)

千问 · 2007-10-20 08:38:44

select count(id),empid,days from test group by empid,days having count(id) >=7
[ 本帖最后由 smthgdin 于 2009-8-12 18:09 编辑 ]

千问 · 2007-10-20 08:38:44

Roy_88 的 2 条语句都很高效，推荐采纳！！

千问 · 2007-10-20 08:38:44

谢谢netdna和Roy_88，你们的方法我都测了，都是可行的，非常感谢。
同样也谢谢 smthgdin。

千问 · 2007-10-20 08:38:44

牛人啊

千问 · 2007-10-20 08:38:44

这里有两种思路：
1.表A，对每一条数据都判断 empid,days相同的个数是否>=7，是则取出，否则舍去；
实现： Roy_88的第二个语句
select * from A as t1 where exists(select 1 from A where EmpID=t1.EmpID andDays=t1. Days having count(*)>=7)
说明：比第一个语句效率要高，因为是用exists作true/false的判断，而不是两张表的join
2.表A，首先查出empid,days相同，个数>=7的EmpID、Days，然后再做exists判断；
把实现步骤分解：
-- 查出 EmpID、Days
select EmpID, Days from A group by EmpID, Days having count(id)>=7;
-- 做exists判断
select * from A as t1
where exists
(
select 1 from (select EmpID, Days from A group by EmpID, Days having count(id)>=7) as t2
where t1.EmpID=t2.EmpID and t1.Days=t2.Days
)
这个语句和下面这个效果是相同的
select * from A as t1
inner join (select EmpID, Days from A group by EmpID, Days having count(id)>=7) as t2
on t1.EmpID=t2.EmpID and t1.Days=t2.Days
3. 效率最高的写法
对于SqlServer来说，没有字符串聚集函数，在MySQL中非常好用的就是 group_concat

select * from A
where id in
(
select group_concat(id) as ids from
(select group_concat(id) as id from A group by EmpID, Days having count(id)>=7)
)
虽然语句难看一些，但个人认为这个效率最高，不过遗憾的是在SqlServer中不知道单语句如何实现