有一张表,表结构Type Code Date Flag
---------------------
A AA 2012-1-1 N
A AA 2012-1-2 N
A AA 2012-1-3 Y
A AA 2012-1-4 N
B BB 2012-1-1 N
B BB 2012-1-2 N
B BB 2012-1-3 N我需要达到的效果是同一个分组中,当Flag是Y时,后面日期大于此日期的纪录全部不显示。
如果此分组下都是N,就全部显示。效果图如下:Type Code Date Flag
---------------------
A AA 2012-1-1 N
A AA 2012-1-2 N
A AA 2012-1-3 Y
B BB 2012-1-1 N
B BB 2012-1-2 N
B BB 2012-1-3 N请问这个SQL如何写呢?不能用临时表,也不能用游标之类的。
---------------------
A AA 2012-1-1 N
A AA 2012-1-2 N
A AA 2012-1-3 Y
A AA 2012-1-4 N
B BB 2012-1-1 N
B BB 2012-1-2 N
B BB 2012-1-3 N我需要达到的效果是同一个分组中,当Flag是Y时,后面日期大于此日期的纪录全部不显示。
如果此分组下都是N,就全部显示。效果图如下:Type Code Date Flag
---------------------
A AA 2012-1-1 N
A AA 2012-1-2 N
A AA 2012-1-3 Y
B BB 2012-1-1 N
B BB 2012-1-2 N
B BB 2012-1-3 N请问这个SQL如何写呢?不能用临时表,也不能用游标之类的。
(select 1 from tb where t.type=type and t.date<date and flag='Y')
select * from tb as a
where Date<=(select min(Date) from tb as b where Flag='Y' and a.Type=b.Type and a.Code=b.Code)
select *
from tb t
where date <= isnull((select top 1 date from tb
where type = t.type and code = t.code and t.flag = 'Y'
order by date),date)
GO
-->生成表tbif object_id('tb') is not null
drop table tb
Go
Create table tb([Type] nvarchar(1),[Code] nvarchar(2),[Date] datetime,[Flag] nvarchar(1))
Insert into tb
Select N'A',N'AA','2012-1-1',N'N'
Union all Select N'A',N'AA','2012-1-2',N'N'
Union all Select N'A',N'AA','2012-1-3',N'Y'
Union all Select N'A',N'AA','2012-1-4',N'N'
Union all Select N'A',N'AA','2012-1-5',N'N'
Union all Select N'B',N'BB','2012-1-1',N'N'
Union all Select N'B',N'BB','2012-1-2',N'N'
Union all Select N'B',N'BB','2012-1-3',N'N'SELECT * FROM tb AS a
WHERE NOT EXISTS(SELECT 1 FROM tb AS x
WHERE x.Type=a.Type
AND x.Flag='Y'
AND x.Date<a.Date
)
/*
Type Code Date Flag
---- ---- ----------------------- ----
A AA 2012-01-01 00:00:00.000 N
A AA 2012-01-02 00:00:00.000 N
A AA 2012-01-03 00:00:00.000 Y
B BB 2012-01-01 00:00:00.000 N
B BB 2012-01-02 00:00:00.000 N
B BB 2012-01-03 00:00:00.000 N*/
select
*
from tb as a
left join (select type,min(Date) as Date from tb where Flag='Y'group by type)as b on a.type=b.type
where a.Date<=b.Date and b.Date is null
同一个Type会有多个Y存在的。但是碰到第一个Y后。后面的记录就都不需要了。谢谢