求个sql 语句

[taohua300]

数据格式如下，sql 2000

FilePath	Key1
A01-51656\51656\1961\09  	2
A01-51656\51656\1961\08  	2
A01-51656\51656\1961\07  	2
A01-51656\51656\1961\06  	2
A01-51656\51656\1961\03  	2
A01-51656\51656\1961\02  	2
A01-51656\51656\1961\01  	2
A01-51656\51656\1960\12  	2
A01-51656\51656\1960\11  	2
A01-51656\51656\1960\10  	2
A01-51656\51656\1960\09  	2
A01-51656\51656\1960\08  	2
A01-51656\51656\1960\07  	2
A01-51656\51656\1960\06  	2
A01-51656\51656\1960\05  	2
A01-51656\51656\1960\04  	2
A01-51656\51656\1962\06  	2
A01-51656\51656\1962\05  	2
A01-51656\51656\1962\04  	2
A01-51656\51656\1962\03  	2
A01-51656\51656\1962\02  	1
A01-51656\51656\1962\01  	1
A01-51656\51656\1961\12  	1
A01-51656\51656\1961\11  	1
A01-51656\51656\1961\10  	2
	

想实现查找当前数据库表年份所有月份的 Key1 值全都为 2

因为 1961 和 1962 有的月份对应的Key1不为 2
所以上述事例的查询结果应该为

A01-51656\51656\1960



求sql 语句

[NBDBA]

数据重贴下，看不清

[taohua300]

改了

回复内容太短了！

[chuanzhang5687]

select left(FilePath,14)+ min(left(right(FilePath,7),4)) from tb where key1 = 2 
group by left(FilePath,14),(left(right(FilePath,7),4))

[闹铃]

思路：

select LEFT(FilePath,20) 
from dbo.Test
where Key1=2
group by LEFT(FilePath,20)
having COUNT(1)=12

[gw6328]

-- 如果保证12月都有
select LEFT(filepath,LEN(filepath)-3) as v from tb where key1=2
group by LEFT(filepath,LEN(filepath)-3),key1 having count(1)=12

-- 
select LEFT(x.filepath,LEN(x.filepath)-3) as v from tb x
where not exists(select 1 from tb where key1=1 and LEFT(x.filepath,LEN(x.filepath)-3)=LEFT(filepath,LEN(filepath)-3))
group by LEFT(x.filepath,LEN(x.filepath)-3),x.key1

[taohua300]

谢谢大家

这样可以

select left(FilePath,20) as tt 
from tWork 
where left(FilePath,20) not in (select left(FilePath,20) from tWork where Key1 = 0 or Key1 = 1) 
group by left(FilePath,20)