ID TNO TNAME MeasureUnit date month pirce
1143 M22-003 电源端子 PCS 2011-7-28 0:00 2011-7 0.21
1144 M24-003 螺丝 PCS 2011-8-22 0:00 2011-8 0.03
1144 M24-003 螺丝 PCS 2011-8-25 0:00 2011-8 0.035
1157 D01-027 纸箱 PCS 2011-7-29 0:00 2011-7 6.73
1157 D02-034 纸板 PCS 2011-7-29 0:00 2011-7 2.38
1157 D02-040 纸卡 PCS 2011-7-29 0:00 2011-7 0.75
1157 D02-041 纸卡 PCS 2011-7-29 0:00 2011-7 0.55
1157 D02-042 纸板 PCS 2011-7-29 0:00 2011-7 0.85
1158 C07-079 电子线 M 2011-8-3 0:00 2011-8 0.279
1158 C07-080 电子线 M 2011-8-3 0:00 2011-8 0.279
1158 C07-081 电子线 M 2011-8-3 0:00 2011-8 0.279
如何找出记录中TNO在每一个月中最新的一个PIRCE
1143 M22-003 电源端子 PCS 2011-7-28 0:00 2011-7 0.21
1144 M24-003 螺丝 PCS 2011-8-22 0:00 2011-8 0.03
1144 M24-003 螺丝 PCS 2011-8-25 0:00 2011-8 0.035
1157 D01-027 纸箱 PCS 2011-7-29 0:00 2011-7 6.73
1157 D02-034 纸板 PCS 2011-7-29 0:00 2011-7 2.38
1157 D02-040 纸卡 PCS 2011-7-29 0:00 2011-7 0.75
1157 D02-041 纸卡 PCS 2011-7-29 0:00 2011-7 0.55
1157 D02-042 纸板 PCS 2011-7-29 0:00 2011-7 0.85
1158 C07-079 电子线 M 2011-8-3 0:00 2011-8 0.279
1158 C07-080 电子线 M 2011-8-3 0:00 2011-8 0.279
1158 C07-081 电子线 M 2011-8-3 0:00 2011-8 0.279
如何找出记录中TNO在每一个月中最新的一个PIRCE
where not exists (select 1 from tb
where t.tno=tno
and t.id =id
and t.date<date)
select *
from tb t
where id = (select top 1 id from tb where tno = t.tno
and convert(varchar(4),date,120) = convert(varchar(4),t.date,120) order by date)
from tb t
where id = (select top 1 id from tb where tno = t.tno
and convert(varchar(4),date,120) = convert(varchar(4),t.date,120) order by date)
declare @T table (ID int,TNO varchar(7),TNAME varchar(8),MeasureUnit varchar(3),date datetime,month varchar(6),pirce numeric(4,3))
insert into @T
select 1143,'M22-003','电源端子','PCS','2011-7-28 0:00','2011-7',0.21 union all
select 1144,'M24-003','螺丝','PCS','2011-8-22 0:00','2011-8',0.03 union all
select 1144,'M24-003','螺丝','PCS','2011-8-25 0:00','2011-8',0.035 union all
select 1157,'D01-027','纸箱','PCS','2011-7-29 0:00','2011-7',6.73 union all
select 1157,'D02-034','纸板','PCS','2011-7-29 0:00','2011-7',2.38 union all
select 1157,'D02-040','纸卡','PCS','2011-7-29 0:00','2011-7',0.75 union all
select 1157,'D02-041','纸卡','PCS','2011-7-29 0:00','2011-7',0.55 union all
select 1157,'D02-042','纸板','PCS','2011-7-29 0:00','2011-7',0.85 union all
select 1158,'C07-079','电子线','M','2011-8-3 0:00','2011-8',0.279 union all
select 1158,'C07-080','电子线','M','2011-8-3 0:00','2011-8',0.279 union all
select 1158,'C07-081','电子线','M','2011-8-3 0:00','2011-8',0.279select * from @T a where date=(
select max(date) from @T where TNO=a.TNO
and convert(varchar(7),[month],120)=convert(varchar(7),a.[month],120))
/*
ID TNO TNAME MeasureUnit date month pirce
----------- ------- -------- ----------- ----------------------- ------ ---------------------------------------
1143 M22-003 电源端子 PCS 2011-07-28 00:00:00.000 2011-7 0.210
1144 M24-003 螺丝 PCS 2011-08-25 00:00:00.000 2011-8 0.035
1157 D01-027 纸箱 PCS 2011-07-29 00:00:00.000 2011-7 6.730
1157 D02-034 纸板 PCS 2011-07-29 00:00:00.000 2011-7 2.380
1157 D02-040 纸卡 PCS 2011-07-29 00:00:00.000 2011-7 0.750
1157 D02-041 纸卡 PCS 2011-07-29 00:00:00.000 2011-7 0.550
1157 D02-042 纸板 PCS 2011-07-29 00:00:00.000 2011-7 0.850
1158 C07-079 电子线 M 2011-08-03 00:00:00.000 2011-8 0.279
1158 C07-080 电子线 M 2011-08-03 00:00:00.000 2011-8 0.279
1158 C07-081 电子线 M 2011-08-03 00:00:00.000 2011-8 0.279
*/
*
from
tb t
where
date=(select max(date) from tb where TNO=t.TNO and convert(varchar(7),[month],120)=convert(varchar(7),t.[month],120))
*
from
tb t
where
date=(select max(date) from tb where TNO=t.TNO and convert(varchar(7),[month],120)=convert(varchar(7),t.[month],120))
上边的解题思路就是;从表里面取出每条记录—>已这个记录的TNO、月份为准,同表关联查询->取关联查询后最大的时间->在以此时间为准select数据。select * from Test a where date=(
select max(date) from Test where TNO=a.TNO
and [month]=a.[month])
月份自己去取,这个就是去重复值
insert into @T
select 1143,'M22-003','电源端子','PCS','2011-7-28 0:00','2011-7',0.21 union all
select 1144,'M24-003','螺丝','PCS','2011-8-22 0:00','2011-8',0.03 union all
select 1144,'M24-003','螺丝','PCS','2011-8-25 0:00','2011-8',0.035 union all
select 1157,'D01-027','纸箱','PCS','2011-7-29 0:00','2011-7',6.73 union all
select 1157,'D02-034','纸板','PCS','2011-7-29 0:00','2011-7',2.38 union all
select 1157,'D02-040','纸卡','PCS','2011-7-29 0:00','2011-7',0.75 union all
select 1157,'D02-041','纸卡','PCS','2011-7-29 0:00','2011-7',0.55 union all
select 1157,'D02-042','纸板','PCS','2011-7-29 0:00','2011-7',0.85 union all
select 1158,'C07-079','电子线','M','2011-8-3 0:00','2011-8',0.279 union all
select 1158,'C07-080','电子线','M','2011-8-3 0:00','2011-8',0.279 union all
select 1158,'C07-081','电子线','M','2011-8-3 0:00','2011-8',0.279
select ID,TNO,TNAME,MeasureUnit,date,month,pirce From(
select ID,TNO,TNAME,MeasureUnit,date,month,pirce,rowid=ROW_NUMBER() over(partition by TNO,month order by date desc) From @T) a
where a.rowid=1
order by ID
declare @T table (ID int,TNO varchar(7),TNAME varchar(8),MeasureUnit varchar(3),date datetime,month varchar(6),pirce numeric(4,3))
insert into @T
select 1143,'M22-003','电源端子','PCS','2011-7-28 0:00','2011-7',0.21 union all
select 1144,'M24-003','螺丝','PCS','2011-8-22 0:00','2011-8',0.03 union all
select 1144,'M24-003','螺丝','PCS','2011-8-25 0:00','2011-8',0.035 union all
select 1157,'D01-027','纸箱','PCS','2011-7-29 0:00','2011-7',6.73 union all
select 1157,'D02-034','纸板','PCS','2011-7-29 0:00','2011-7',2.38 union all
select 1157,'D02-040','纸卡','PCS','2011-7-29 0:00','2011-7',0.75 union all
select 1157,'D02-041','纸卡','PCS','2011-7-29 0:00','2011-7',0.55 union all
select 1157,'D02-042','纸板','PCS','2011-7-29 0:00','2011-7',0.85 union all
select 1158,'C07-079','电子线','M','2011-8-3 0:00','2011-8',0.279 union all
select 1158,'C07-080','电子线','M','2011-8-3 0:00','2011-8',0.279 union all
select 1158,'C07-081','电子线','M','2011-8-3 0:00','2011-8',0.279
select ID,TNO,TNAME,MeasureUnit,date,month,pirce From(
select ID,TNO,TNAME,MeasureUnit,date,month,pirce,rowid=ROW_NUMBER() over(partition by TNO,month order by date desc) From @T) a
where a.rowid=1
order by ID