SQL复杂条件语句，求大神相助
附件已上传，我只想提出数据库里的部分内容，但苦于水平有限，求各位大神指点。
条件是这样：对同一个pid的情况下，如果所有的条目searchkey都为空，则取time最早的一条；
如果所有条目中的searchkey不全为空，则取出所有searchkey不为空的条目。
比如pid为1的几条记录里，就提取第一条。pid为2的几条记录里，就提取第1，2，5条。
万分感谢！
sql 数据库 查询
[解决办法]
USE test
GO
-->生成表tb
if object_id(N'tb') is not null
drop table [tb]
Go
Create table [tb]([pid] smallint,[url] nvarchar(4),[searchkey] nvarchar(50),[time] datetime)
Insert into [tb]
Select 1,N'url1','friendlink','2012-12-1'
Union all Select 1,N'url2','','2012-12-2'
Union all Select 1,N'url3','','2012-12-3'
Union all Select 1,N'url1','','2012-12-4'
Union all Select 2,N'url3',N'djue','2012-12-5'
Union all Select 2,N'url4',N'dww','2012-12-6'
Union all Select 2,N'url2','','2012-12-7'
Union all Select 2,N'url3','','2012-12-8'
Union all Select 2,N'url1',N'ddj','2012-12-9'
Union all Select 2,N'url3','','2012-12-10'
------------------ date range:
DECLARE @startDate DATETIME,@endDate DATETIME
SELECT @startDate='2012-12-1'
,@endDate='2012-12-8'
------------------ statement 1:
SELECT
pid,url,searchkey,time
FROM tb AS a
WHERE ((ISNULL(searchkey,'') <> ''
AND searchkey <> 'friendlink'
)--- searchkey不全为空或friendlink
OR(NOT EXISTS(SELECT 1 FROM tb AS x
WHERE x.pid=a.pid
AND ISNULL(x.searchkey,'') <> ''
AND x.searchkey <> 'friendlink'
)
AND NOT EXISTS(SELECT 1 FROM tb AS x
WHERE x.pid=a.pid AND x.time < a.time
AND x.time >= @startDate
AND x.time <= @endDate
)
)
)--- searchkey都为空或friendlink
AND time >= @startDate
AND time <= @endDate
ORDER BY pid,time
------------------ statement 2:
IF OBJECT_ID('tempdb..#Result')IS NOT NULL
DROP TABLE #Result
SELECT
pid
,url
,searchkey
,CASE
WHEN searchkey='friendlink'
THEN NULL
ELSE searchkey
END AS searchkey2--- friendlink 視為 NULL
,time
INTO #Result
FROM tb
WHERE time >= @startDate
AND time <= @endDate
SELECT
pid,url,searchkey,time
FROM #Result AS a
WHERE ISNULL(searchkey2,'') <> '' --- searchkey不全为空
OR(NOT EXISTS(SELECT 1 FROM #Result AS x
WHERE x.pid=a.pid
AND ISNULL(x.searchkey2,'') <> ''
)
AND NOT EXISTS(SELECT 1 FROM #Result AS x
WHERE x.pid=a.pid AND x.time < a.time
)
)--- searchkey都为空
ORDER BY pid,time
/*
pid url searchkey time
------ ---- ------------- -----------------------
1 url1 friendlink 2012-12-01 00:00:00.000
2 url3 djue 2012-12-05 00:00:00.000
2 url4 dww 2012-12-06 00:00:00.000
*/
