大家好!
下午我发的那个问题少了条件,现在重新发一个,请大家帮忙解决。表A:F1 F2 F3 F4 F5
AAA CarA 2010-3-1 2010-3-31 2.2
AAA CarA 2010-4-1 2010-4-30 2.0
BBB BikeB 2009-3-1 2010-2-28 1.0
BBB MMB 2010-3-1 2010-3-31 1.0
BBB MMB 2010-4-1 2010-8-31 1.0条件一:
以F1,F2这两列作为分组条件
条件二:
判断时间,看2009-12-31是否在F3、F4这两个日期之间,如果在的话,取出来;
如果对同一个F1、F2,2009-12-31没有在F3、F4之间,就再看F3的时间,如果F3>2009-12-31,就把这条数据取出来,而且只取F3时间最早的那条数据。例如上面的结果应该为:
F1 F2 F3 F4 F5
AAA CarA 2010-3-1 2010-3-31 2.2
BBB BikeB 2009-3-1 2010-2-28 1.0
BBB MMB 2010-3-1 2010-3-31 1.0请教大家如何完成?
下午我发的那个问题少了条件,现在重新发一个,请大家帮忙解决。表A:F1 F2 F3 F4 F5
AAA CarA 2010-3-1 2010-3-31 2.2
AAA CarA 2010-4-1 2010-4-30 2.0
BBB BikeB 2009-3-1 2010-2-28 1.0
BBB MMB 2010-3-1 2010-3-31 1.0
BBB MMB 2010-4-1 2010-8-31 1.0条件一:
以F1,F2这两列作为分组条件
条件二:
判断时间,看2009-12-31是否在F3、F4这两个日期之间,如果在的话,取出来;
如果对同一个F1、F2,2009-12-31没有在F3、F4之间,就再看F3的时间,如果F3>2009-12-31,就把这条数据取出来,而且只取F3时间最早的那条数据。例如上面的结果应该为:
F1 F2 F3 F4 F5
AAA CarA 2010-3-1 2010-3-31 2.2
BBB BikeB 2009-3-1 2010-2-28 1.0
BBB MMB 2010-3-1 2010-3-31 1.0请教大家如何完成?
select t.* from a t where '2009-12-31' not between F3 and F4 and f3 > '2009-12-31' and f3 = (select min(f3) from a where f1 = t.f1 and f2 = t.f2 and '2009-12-31' not between F3 and F4 and f3 > '2009-12-31')select a.* from a where '2009-12-31' between F3 and F4
select t.* from a t where '2009-12-31' not between F3 and F4 and f3 > '2009-12-31' and not exists (select 1 from a where f1 = t.f1 and f2 = t.f2 and f3 < t.f3 and '2009-12-31' not between F3 and F4 and f3 > '2009-12-31')
insert into a values('AAA', 'CarA' , '2010-3-1', '2010-3-31', 2.2)
insert into a values('AAA', 'CarA' , '2010-4-1', '2010-4-30', 2.0)
insert into a values('BBB', 'BikeB', '2009-3-1', '2010-2-28', 1.0)
insert into a values('BBB', 'MMB' , '2010-3-1', '2010-3-31', 1.0)
insert into a values('BBB', 'MMB' , '2010-4-1', '2010-8-31', 1.0)
goselect a.* from a where '2009-12-31' between F3 and F4
union all
select t.* from a t where '2009-12-31' not between F3 and F4 and f3 > '2009-12-31' and f3 = (select min(f3) from a where f1 = t.f1 and f2 = t.f2 and '2009-12-31' not between F3 and F4 and f3 > '2009-12-31')
order by f1,f2
/*
F1 F2 F3 F4 F5
---------- ---------- ------------------------------------------------------ ------------------------------------------------------ --------------------
AAA CarA 2010-03-01 00:00:00.000 2010-03-31 00:00:00.000 2.20
BBB BikeB 2009-03-01 00:00:00.000 2010-02-28 00:00:00.000 1.00
BBB MMB 2010-03-01 00:00:00.000 2010-03-31 00:00:00.000 1.00(所影响的行数为 3 行)
*/select a.* from a where '2009-12-31' between F3 and F4
union all
select t.* from a t where '2009-12-31' not between F3 and F4 and f3 > '2009-12-31' and not exists (select 1 from a where f1 = t.f1 and f2 = t.f2 and f3 < t.f3 and '2009-12-31' not between F3 and F4 and f3 > '2009-12-31')
order by f1,f2
/*
F1 F2 F3 F4 F5
---------- ---------- ------------------------------------------------------ ------------------------------------------------------ --------------------
AAA CarA 2010-03-01 00:00:00.000 2010-03-31 00:00:00.000 2.20
BBB BikeB 2009-03-01 00:00:00.000 2010-02-28 00:00:00.000 1.00
BBB MMB 2010-03-01 00:00:00.000 2010-03-31 00:00:00.000 1.00(所影响的行数为 3 行)
*/drop table a
你写的这个好像还是有点问题,
比如这样的数据
F1 F2 F3 F4 F5
CCC CarA 2009-3-1 2010-1-31 2.2
CCC CarA 2010-2-1 2010-4-30 2.0结果应该只拿出第一条,但是按照你的运行出来的就是两条都有了。
就是说,如果同一个F1、F2,2009-12-31在它的F3、F4之间的话,那就拿出这条,后面的就不要了。
use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
F1 char(4),
F2 varchar(5),
F3 datetime,
F4 datetime,
F5 decimal(10,1)
)
go
--插入测试数据
insert into tb select 'AAA','CarA','2010-3-1','2010-3-31',2.2
union all select 'AAA','CarA','2010-4-1','2010-4-30',2.0
union all select 'BBB','BikeB','2009-3-1','2010-2-28',1.0
union all select 'BBB','MMB','2010-3-1','2010-3-31',1.0
union all select 'BBB','MMB','2010-4-1','2010-8-31',1.0
union all select 'CCC','CarA','2009-3-1','2010-1-31',2.2
union all select 'CCC','CarA','2010-2-1','2010-4-30',2.0
go
--代码实现select F1,F2,F3=convert(varchar(10),F3,120),F4=convert(varchar(10),F4,120),F5
from tb t
where not exists(select 1
from tb
where F1=t.F1 and F2=t.F2 and F3<t.F3)
and not exists(select 1
from (select *
from tb
where '2009-12-31' between F3 and F4)tt
where tt.F1=t.F1 and tt.F2=t.F2)
union all
select F1,F2,F3=convert(varchar(10),F3,120),F4=convert(varchar(10),F4,120),F5
from tb
where '2009-12-31' between F3 and F4/*测试结果F1 F2 F3 F4 F5
-----------------------------------------------------
AAA CarA 2010-03-01 2010-03-31 2.2
BBB MMB 2010-03-01 2010-03-31 1.0
BBB BikeB 2009-03-01 2010-02-28 1.0
CCC CarA 2009-03-01 2010-01-31 2.2(4 行受影响)
*/
查询到的结果居然还有这样的情况:F1 F2 F3 F4 F5
DDD DriverD 2009-3-1 2010-7-31 2.2
DDD DriverD 2009-8-1 2010-4-30 2.0
同一个F1、F2,2009-12-31在它们对应的F3、F4之间的数据超过两条的话,就会把这些数据也一起拿出来,
但是我实际希望的结果是只拿一条,应该是拿F3最早的那条。
如上面这两行数据,应该只拿第一条出来。
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
F1 char(4),
F2 varchar(10),
F3 datetime,
F4 datetime,
F5 decimal(10,1)
)
go
--插入测试数据
insert into tb select 'AAA','CarA','2010-3-1','2010-3-31',2.2
union all select 'AAA','CarA','2010-4-1','2010-4-30',2.0
union all select 'BBB','BikeB','2009-3-1','2010-2-28',1.0
union all select 'BBB','MMB','2010-3-1','2010-3-31',1.0
union all select 'BBB','MMB','2010-4-1','2010-8-31',1.0
union all select 'CCC','CarA','2009-3-1','2009-7-31',2.2
union all select 'CCC','CarA','2009-11-1','2010-1-31',2.2
union all select 'CCC','CarA','2010-3-1','2010-4-30',2.0
union all select 'DDD','DriverD','2009-3-1','2009-11-30',2.3
union all select 'DDD','DriverD','2009-3-1','2010-7-31',2.2
union all select 'DDD','DriverD','2009-8-1','2010-4-30',2.0
go
--代码实现;with t as (select * from tb where F3>'2009-12-31' or '2009-12-31' between F3 and F4)
select F1,F2,F3=convert(varchar(10),F3,120),F4=convert(varchar(10),F4,120),F5
from t t1
where not exists (select 1 from t where F1=t1.F1 and F2=t1.F2 and F3<t1.F3)/*测试结果F1 F2 F3 F4 F5
-----------------------------------------------------
AAA CarA 2010-03-01 2010-03-31 2.2
BBB BikeB 2009-03-01 2010-02-28 1.0
BBB MMB 2010-03-01 2010-03-31 1.0
CCC CarA 2009-11-01 2010-01-31 2.2
DDD DriverD 2009-03-01 2010-07-31 2.2(4 行受影响)
*/
from tb t
where not exists(select 1
from tb
where F1=t.F1 and F2=t.F2 and F3<t.F3)
and not exists(select 1
from (select *
from tb
where '2009-12-31' between F3 and F4)tt
where tt.F1=t.F1 and tt.F2=t.F2)
union all
select F1,F2,F3=convert(varchar(10),F3,120),F4=convert(varchar(10),F4,120),F5
from tb
where '2009-12-31' between F3 and F4
我想问下,如果在这段代码里,再加一个状态的排除条件,怎么改?
条件如下:
F1 F2 F3 F4 F5 F6
WWW HorH 2009-3-1 2010-3-31 2.2 -1
WWW HorH 2010-4-1 2010-5-28 1.0 -1
WWW HorH 2010-5-29 2010-8-31 1.0 0
AAA CarA 2010-3-1 2010-3-31 2.2 0
AAA CarA 2010-4-1 2010-4-30 2.0 0
BBB BikeB 2009-3-1 2010-2-28 1.0 0
BBB MMB 2010-3-1 2010-3-31 1.0 0
BBB MMB 2010-4-1 2010-8-31 1.0 0
CCC CarA 2009-3-1 2010-1-31 2.2 0
CCC CarA 2010-2-1 2010-4-30 2.0 0
DDD DriverD 2009-3-1 2010-7-31 2.2 0
DDD DriverD 2009-8-1 2010-4-30 2.0 01. 将所有F6为-1的数据全部排除掉;(这个是新增加的条件,下面的条件都和之前问题中的一样)
2. 在余下的数据里将F1、F2作为分组条件;
3.判断时间,看2009-12-31是否在F3、F4这两个日期之间,如果在的话,取出来;
如果对同一个F1、F2,2009-12-31没有在F3、F4之间,就再看F3的时间,如果F3>2009-12-31,就把这条数据取出来,而且只取F3时间最早的那条数据。
4.如果对同一个F1、F2,既有2009-12-31在它的F3、F4之间的数据,又有F3>2009-12-31的数据,仅拿出2009-12-31在它之间的那条数据,F3>2009-12-31的数据全部舍弃;
5.同一个F1、F2,2009-12-31在它们对应的F3、F4之间的数据超过两条的话,全部拿出(本来是要拿F3最早的那条,现在不用了,先全部拿出吧)如上面数据的结果应该为:F1 F2 F3 F4 F5 F6
-----------------------------------------------------
WWW HorH 2010-5-29 2010-8-31 1.0 0
AAA CarA 2010-03-01 2010-03-31 2.2 0
BBB BikeB 2009-03-01 2010-02-28 1.0 0
BBB MMB 2010-03-01 2010-03-31 1.0 0
CCC CarA 2009-11-01 2010-01-31 2.2 0
DDD DriverD 2009-03-01 2010-07-31 2.2 0
谁还有其它方法呢?
不用放到另外一张表里,这样怎么样select * from (select * from tb where F6<>'-1')t(select * from tb where F6<>'-1')t --这个是查询结果集,就算是你说的那个新建立的“表”
不客气!只是我这两天很少登陆 CSDN...呵呵...