查询唯一多列语句

[tqw708]

数据源:
ID ClientCode Newcode Name NewlyQuote QuoteDate
1 S000011 Z0000005 1015#18/105度AS銅芯線 白色 0.72 2007-9-1
2 S000011 Z0000005 1015#18/105度AS銅芯線 白色 0.73 2006-12-12
4 S000011 Z0000005 1015#18/105度AS銅芯線 白色 0.65 2007-3-1
7 S000011 Z0000086 青銅螺母 1.7 2007-8-30
8 S000074 Z0000086 青銅螺母 1.5 2007-5-20
5 S000088 Z0000005 1015#18/105度AS銅芯線 白色 0.89 2007-6-13
3 S000088 Z0000005 1015#18/105度AS銅芯線 白色 0.9 2006-10-25
6 S000099 Z0000005 1015#18/105度AS銅芯線 白色 0.94 2007-8-21

###################################

问题一:
查询结果:(是要每个ClientCode的Newcode以QuoteDate最大值只取一笔)

ID ClientCode Newcode Name NewlyQuote QuoteDate
1 S000011 Z0000005 1015#18/105度AS銅芯線 白色 0.72 2007-9-1
7 S000011 Z0000086 青銅螺母 1.7 2007-8-30
8 S000074 Z0000086 青銅螺母 1.5 2007-5-20
5 S000088 Z0000005 1015#18/105度AS銅芯線 白色 0.89 2007-6-13
6 S000099 Z0000005 1015#18/105度AS銅芯線 白色 0.94 2007-8-21

问题二:
查询结果:(是要每个ClientCode的Newcode='Z0000005'以QuoteDate最大值只取一笔)

ID ClientCode Newcode Name NewlyQuote QuoteDate
1 S000011 Z0000005 1015#18/105度AS銅芯線 白色 0.72 2007-9-1
5 S000088 Z0000005 1015#18/105度AS銅芯線 白色 0.89 2007-6-13
6 S000099 Z0000005 1015#18/105度AS銅芯線 白色 0.94 2007-8-21

求SQL语句?谢

[dawugui]

问题一:
查询结果:(是要每个ClientCode的Newcode以QuoteDate最大值只取一笔)

select a.* from tb a,
(select ClientCode,Newcode,max(QuoteDate) QuoteDate from tb group by ClientCode,Newcode) b
where a.ClientCode = b.ClientCode and a.Newcode = b.Newcode and a.QuoteDate = b.QuoteDate

[dawugui]

问题二:
查询结果:(是要每个ClientCode的Newcode='Z0000005'以QuoteDate最大值只取一笔)
select a.* from tb a,
(select ClientCode,max(QuoteDate) QuoteDate from tb where Newcode='Z0000005' group by ClientCode) b
where a.ClientCode = b.ClientCode and a.Newcode='Z0000005' and a.QuoteDate = b.QuoteDate

[yrwx001]

问题一:
select * from tb1 A where A.ID in (select top 1 B.ID from tb1 B where B.ClientCode = A.ClientCode and B.Newcode = A.Newcode order by B.QuoteDate desc )
问题二:
select * from tb1 A where A.ID in (select top 1 B.ID from tb1 B where B.ClientCode = A.ClientCode and B.Newcode = 'Z0000005' order by B.QuoteDate desc )

[dawugui]

create table tb(ID int,ClientCode varchar(10),Newcode varchar(10),Name varchar(30),NewlyQuote decimal(18,2),QuoteDate datetime)
insert into tb values(1,'S000011','Z0000005','1015#18/105度AS銅芯線 白色',0.72,'2007-9-1')
insert into tb values(2,'S000011','Z0000005','1015#18/105度AS銅芯線 白色',0.73,'2006-12-12')
insert into tb values(4,'S000011','Z0000005','1015#18/105度AS銅芯線 白色',0.65,'2007-3-1')
insert into tb values(7,'S000011','Z0000086','青銅螺母',1.7,'2007-8-30')
insert into tb values(8,'S000074','Z0000086','青銅螺母',1.5,'2007-5-20')
insert into tb values(5,'S000088','Z0000005','1015#18/105度AS銅芯線 白色',0.89,'2007-6-13')
insert into tb values(3,'S000088','Z0000005','1015#18/105度AS銅芯線 白色',0.9,'2006-10-25')
insert into tb values(6,'S000099','Z0000005','1015#18/105度AS銅芯線 白色',0.94,'2007-8-21')
go
select a.* from tb a,
(select ClientCode,Newcode,max(QuoteDate) QuoteDate from tb group by ClientCode,Newcode) b
where a.ClientCode = b.ClientCode and a.Newcode = b.Newcode and a.QuoteDate = b.QuoteDate
order by a.ClientCode,a.Newcode

select a.* from tb a,
(select ClientCode,max(QuoteDate) QuoteDate from tb where Newcode='Z0000005' group by ClientCode) b
where a.ClientCode = b.ClientCode and a.Newcode='Z0000005' and a.QuoteDate = b.QuoteDate
order by a.ClientCode,a.Newcode

drop table tb
/*
ID ClientCode Newcode Name NewlyQuote QuoteDate
-- ---------- ---------- ------------------------------ ---------- -----------------------
1 S000011 Z0000005 1015#18/105度AS銅芯線 白色 .72 2007-09-01 00:00:00.000
7 S000011 Z0000086 青銅螺母 1.70 2007-08-30 00:00:00.000
8 S000074 Z0000086 青銅螺母 1.50 2007-05-20 00:00:00.000
5 S000088 Z0000005 1015#18/105度AS銅芯線 白色 .89 2007-06-13 00:00:00.000
6 S000099 Z0000005 1015#18/105度AS銅芯線 白色 .94 2007-08-21 00:00:00.000

（所影响的行数为 5 行）

ID ClientCode Newcode Name NewlyQuote QuoteDate
-- ---------- ---------- ------------------------------ ------------ -----------------------
1 S000011 Z0000005 1015#18/105度AS銅芯線 白色 .72 2007-09-01 00:00:00.000
5 S000088 Z0000005 1015#18/105度AS銅芯線 白色 .89 2007-06-13 00:00:00.000
6 S000099 Z0000005 1015#18/105度AS銅芯線 白色 .94 2007-08-21 00:00:00.000

（所影响的行数为 3 行）

*/

[dawugui]

create table tb(ID int,ClientCode varchar(10),Newcode varchar(10),Name varchar(30),NewlyQuote decimal(18,2),QuoteDate datetime)
insert into tb values(1,'S000011','Z0000005','1015#18/105度AS銅芯線 白色',0.72,'2007-9-1')
insert into tb values(2,'S000011','Z0000005','1015#18/105度AS銅芯線 白色',0.73,'2006-12-12')
insert into tb values(4,'S000011','Z0000005','1015#18/105度AS銅芯線 白色',0.65,'2007-3-1')
insert into tb values(7,'S000011','Z0000086','青銅螺母',1.7,'2007-8-30')
insert into tb values(8,'S000074','Z0000086','青銅螺母',1.5,'2007-5-20')
insert into tb values(5,'S000088','Z0000005','1015#18/105度AS銅芯線 白色',0.89,'2007-6-13')
insert into tb values(3,'S000088','Z0000005','1015#18/105度AS銅芯線 白色',0.9,'2006-10-25')
insert into tb values(6,'S000099','Z0000005','1015#18/105度AS銅芯線 白色',0.94,'2007-8-21')
go
select a.* from tb a,
(select ClientCode,Newcode,max(QuoteDate) QuoteDate from tb group by ClientCode,Newcode) b
where a.ClientCode = b.ClientCode and a.Newcode = b.Newcode and a.QuoteDate = b.QuoteDate
order by a.ClientCode,a.Newcode

select a.* from tb a,
(select ClientCode,max(QuoteDate) QuoteDate from tb where Newcode='Z0000005' group by ClientCode) b
where a.ClientCode = b.ClientCode and a.Newcode='Z0000005' and a.QuoteDate = b.QuoteDate
order by a.ClientCode,a.Newcode

drop table tb
/*
ID ClientCode Newcode Name NewlyQuote QuoteDate
-- ---------- -------- ------------------------- ---------- -----------------------
1 S000011 Z0000005 1015#18/105度AS銅芯線 白色 .72 2007-09-01 00:00:00.000
7 S000011 Z0000086 青銅螺母 1.70 2007-08-30 00:00:00.000
8 S000074 Z0000086 青銅螺母 1.50 2007-05-20 00:00:00.000
5 S000088 Z0000005 1015#18/105度AS銅芯線 白色 .89 2007-06-13 00:00:00.000
6 S000099 Z0000005 1015#18/105度AS銅芯線 白色 .94 2007-08-21 00:00:00.000

（所影响的行数为 5 行）

ID ClientCode Newcode Name NewlyQuote QuoteDate
-- ---------- -------- ------------------------- ---------- -----------------------
1 S000011 Z0000005 1015#18/105度AS銅芯線 白色 .72 2007-09-01 00:00:00.000
5 S000088 Z0000005 1015#18/105度AS銅芯線 白色 .89 2007-06-13 00:00:00.000
6 S000099 Z0000005 1015#18/105度AS銅芯線 白色 .94 2007-08-21 00:00:00.000

（所影响的行数为 3 行）

*/