(1)
alter table adsva add flag bit
go
update adsva set flag=case when tram>=0 then 1 else 0 end
select acno from adsva group by acno having count(*)>3 and acno in(
select acno from adsva
where trdt>='20051022'and trdt<='20051115' and
flag=0
group by acno,flag
having count(*)=1
)
alter table adsva drop column flag
alter table adsva add flag bit
go
update adsva set flag=case when tram>=0 then 1 else 0 end
select acno from adsva group by acno having count(*)>3 and acno in(
select acno from adsva
where trdt>='20051022'and trdt<='20051115' and
flag=0
group by acno,flag
having count(*)=1
)
alter table adsva drop column flag
解决方案 »
- SQL里如何把日期时间类型的数值,只取时间部分,请问怎么实现?在线等待
- 大侠救命:不同数据库的不同表结构如何导数据???
- 存储过程问题
- 表的列是繁体中文的,如何按UNICODE排序
- 疑难求解:如何向带INT自增主键及外键的数据表中插入记录
- 关于SQL 2000 数据库连接超时的问题
- 数据库记录删除求助
- 找不到适合的查询方法,请赐教。
- nononono(null,null),dudo(dudo).............你们在哪里?你快来呀。。。。。。。。
- DB2优化,说话的都给分!!!!!
- 如何创建一个简单的时间触发器??
- 急!为什么我的SQLSERVER2000版本在Windows XP下面不能安装?...
create table adsva
(trdt varchar(8),acno varchar(20),tram int)
insert adsva
select '20051021','1102',200 union all
select '20051022','1102',300 union all
select '20051023','1102',100 union all
select '20051024','1102',200 union all
select '20051025','1102',300 union all
select '20051026','1106',-100 union all
select '20051026','1106',200 union all
select '20051026','1106',500 union all
select '20051028','1102',200 union all
select '20051029','1102',300 union all
select '20051030','1102',100 union all
select '20051031','1102',200 union all
select '20051101','1102',-100 union all
select '20051102','1102',200 union all
select '20051103','1103',500 union all
select '20051104','1104',100 union all
select '20051105','1102',100 union all
select '20051106','1102',100 union all
select '20051107','1102',-200 union all
select '20051107','1102',100 union all
select '20051108','1102',100 union all
select '20051109','1102',100 union all
select '20051110','1102',100 union all
select '20051110','1104',500 union all
select '20051112','1102',200 union all
select '20051113','1102',300 union all
select '20051114','1102',400 union all
select '20051115','1102',100 union all
select '20051116','1102',200 union all
select '20051117','1102',100 union all
select '20051118','1102',100 union all
select '20051120','1104',500 union all
select '20051026','1106',900--执行语句:
alter table adsva add flag bit
go
update adsva set flag=case when tram>=0 then 1 else 0 end
select t.trdt,t.acno,u.tram from (
select trdt,acno from adsva
group by trdt,acno having count(*)>3 and acno in(
select acno from adsva
where trdt>='20051022'and trdt<='20051115' and
flag=0
group by trdt,acno,flag
having count(*)=1
)
)t left join adsva u on t.trdt=u.trdt and t.acno=u.acno
union all
select trdt,acno,tram from adsva where acno in(
select acno from adsva
where trdt>='20051022'and trdt<='20051115' and
flag=0
group by acno,flag
having count(*)>1 and count(*)<4
)and acno in(
select acno from adsva
where trdt>='20051022'and trdt<='20051115' and
flag=1
group by acno,flag
having count(*)>10
)
alter table adsva drop column flag
--结果:
/*
trdt acno tram
-------- -------------------- -----------
20051026 1106 -100
20051026 1106 200
20051026 1106 500
20051026 1106 900
20051021 1102 200
20051022 1102 300
20051023 1102 100
20051024 1102 200
20051025 1102 300
20051028 1102 200
20051029 1102 300
20051030 1102 100
20051031 1102 200
20051101 1102 -100
20051102 1102 200
20051105 1102 100
20051106 1102 100
20051107 1102 -200
20051107 1102 100
20051108 1102 100
20051109 1102 100
20051110 1102 100
20051112 1102 200
20051113 1102 300
20051114 1102 400
20051115 1102 100
20051116 1102 200
20051117 1102 100
20051118 1102 100(所影响的行数为 29 行)
*/
20051117 1102 100
20051118 1102 100
这些数据呢?