求助,高手帮帮忙。

hww2004 2008-02-26 06:04:08
有如下表:A,
CUS_NO PRD_NO S_DD UP
====== ======= ========== =====
A BAD001 2007-12-01 2.0
A BAD001 2008-01-21 2.2
A BAD001 2008-02-15 2.1
B BAD001 2008-01-11 2.2
B BAD001 2008-02-22 2.3
C BAD001 2008-02-21 2.2
C BAD001 2008-02-23 2.1
A1 BAD002 2008-01-21 2.2
A2 BAD002 2008-01-21 2.3
A2 BAD002 2008-01-26 2.2
... ... ... ...


希望查询的结果如下:
A BAD001 2007-12-01 2.0
A BAD001 2008-01-21 2.2
B BAD001 2008-01-11 2.2
C BAD001 2008-02-21 2.2
A2 BAD002 2008-01-21 2.3
...全文
87 5 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
hww2004 2008-02-27
  • 打赏
  • 举报
回复
happyflagstone 兄:
我用你的语句确实有问题。
-狙击手- 2008-02-26
  • 打赏
  • 举报
回复
create table A(CUS_NO varchar(8),PRD_NO varchar(10),S_DD datetime,UP decimal(9,2))
go
insert into A
select 'A','BAD001','2007-12-01',2.0
union select 'A','BAD001','2008-01-21',2.2
union select 'A','BAD001','2008-02-15',2.1
union select 'B','BAD001','2008-01-11',2.2
union select 'B','BAD001','2008-02-22',2.3
union select 'C','BAD001','2008-02-21',2.2
union select 'C','BAD001','2008-02-23',2.1
union select 'A1','BAD002','2008-01-21',2.2
union select 'A2','BAD002','2008-01-21',2.3
union select 'A2','BAD002','2008-01-26',2.3
union select 'A2','BAD002','2008-01-26',2.2
go
select * from A
select cus_no,prd_no,s_dd,up
from A
where not exists(select *
from A b
where datepart(yy,a.S_DD)= datepart(yy,b.S_DD) and a.cus_no=b.cus_no and a.prd_no=b.prd_no and a.s_dd > b.s_dd )
and cus_no in (select cus_no from A group by cus_no having count(1) > 1)
order by len(cus_no),cus_no



go
drop table A;
go
/*
cus_no prd_no s_dd up
-------- ---------- ------------------------------------------------------ -----------
A BAD001 2007-12-01 00:00:00.000 2.00
A BAD001 2008-01-21 00:00:00.000 2.20
B BAD001 2008-01-11 00:00:00.000 2.20
C BAD001 2008-02-21 00:00:00.000 2.20
A2 BAD002 2008-01-21 00:00:00.000 2.30

(所影响的行数为 5 行)
*/
fcuandy 2008-02-26
  • 打赏
  • 举报
回复
不知道楼主什么想法
pt1314917 2008-02-26
  • 打赏
  • 举报
回复

create table tA(CUS_NO varchar(8),PRD_NO varchar(10),S_DD datetime,UP decimal(9,2))
go
insert into tA
select 'A','BAD001','2007-12-01',2.0
union select 'A','BAD001','2008-01-21',2.2
union select 'A','BAD001','2008-02-15',2.1
union select 'B','BAD001','2008-01-11',2.2
union select 'B','BAD001','2008-02-22',2.3
union select 'C','BAD001','2008-02-21',2.2
union select 'C','BAD001','2008-02-23',2.1
union select 'A1','BAD002','2008-01-21',2.2
union select 'A2','BAD002','2008-01-21',2.3
union select 'A2','BAD002','2008-01-26',2.3
union select 'A2','BAD002','2008-01-26',2.2
go

select * from ta a where exists
(select 1 from ta where CUS_NO=a.CUS_NO and PRD_NO=a.PRD_NO and S_DD>a.S_DD)
pt1314917 2008-02-26
  • 打赏
  • 举报
回复

---这个可以了啊。。

select * from ta a where exists
(select 1 from ta where CUS_NO=a.CUS_NO and PRD_NO=a.PRD_NO and S_DD>a.S_DD)


34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧