select * from tablename a where id in(select top 1 id from where a.产品=产品 order by 加入时间 asc )
更新的要求是 ID 产品 加入时间 数量 1 A 1-1 2 B 1-2 3 C 1-3 4 A 1-3 5 A 1-4 6 C 1-2 7 B 1-3 我要从表B中产品名相同的数量更新至这张表
alter table tablename add 数量 int update tablename set 数量=isnull(b.数量,0) from tablename a left join b on a.产品=b.产品 and a.加人时间=b.加入时间
Create table Test(id int,产品 varchar(1),加入时间 varchar(5)) insert into Test select 1, 'A' , '1-1' union all select 2, 'B' , '1-2' union all select 3, 'C' , '1-3' union all select 4, 'A' , '1-3' union all select 5, 'A' , '1-4' union all select 6, 'C' , '1-2' union all select 7, 'B', '1-3' -----------------實現語句----------- select test.* from test inner join (select 产品,pp= min(cast(replace(加入时间,'-','') as int)) from test group by 产品) a on test.产品=a.产品 and pp=cast(replace(test.加入时间,'-','') as int) ----------------結果------------------1 A 1-1 2 B 1-2 6 C 1-2
--------樓主修改的 例子 Create table Test(id int,产品 varchar(1),加入时间 varchar(5) ,qty int) insert into Test select 1, 'A' , '1-1' ,0 union all select 2, 'B' , '1-2',0 union all select 3, 'C' , '1-3',0 union all select 4, 'A' , '1-3',0 union all select 5, 'A' , '1-4',0 union all select 6, 'C' , '1-2',0 union all select 7, 'B', '1-3',0 --------------假如有b表 Create Table BC(产品 varchar(1),cc int) insert into BC select 'A',10 union select 'B',20 union select 'C',30 -----------------實現語句----------- update test set qty=BC.cc from test inner join (select 产品,pp= min(cast(replace(加入时间,'-','') as int)) from test group by 产品) a on test.产品=a.产品 and pp=cast(replace(test.加入时间,'-','') as int) inner join BC on(bc.产品=test.产品) ----------------結果------------------ select * from test 1 A 1-1 10 2 B 1-2 20 3 C 1-3 0 4 A 1-3 0 5 A 1-4 0 6 C 1-2 30 7 B 1-3 0 drop table test
from tablename a
where id in(select top 1 id from where a.产品=产品 order by 加入时间 asc )
ID 产品 加入时间 数量
1 A 1-1
2 B 1-2
3 C 1-3
4 A 1-3
5 A 1-4
6 C 1-2
7 B 1-3
我要从表B中产品名相同的数量更新至这张表
update tablename
set 数量=isnull(b.数量,0)
from tablename a
left join b
on a.产品=b.产品
and a.加人时间=b.加入时间
insert into Test
select 1, 'A' , '1-1' union all
select 2, 'B' , '1-2' union all
select 3, 'C' , '1-3' union all
select 4, 'A' , '1-3' union all
select 5, 'A' , '1-4' union all
select 6, 'C' , '1-2' union all
select 7, 'B', '1-3'
-----------------實現語句-----------
select test.* from test inner join
(select 产品,pp= min(cast(replace(加入时间,'-','') as int)) from test group by 产品) a
on test.产品=a.产品 and pp=cast(replace(test.加入时间,'-','') as int)
----------------結果------------------1 A 1-1
2 B 1-2
6 C 1-2
Create table Test(id int,产品 varchar(1),加入时间 varchar(5) ,qty int)
insert into Test
select 1, 'A' , '1-1' ,0 union all
select 2, 'B' , '1-2',0 union all
select 3, 'C' , '1-3',0 union all
select 4, 'A' , '1-3',0 union all
select 5, 'A' , '1-4',0 union all
select 6, 'C' , '1-2',0 union all
select 7, 'B', '1-3',0
--------------假如有b表
Create Table BC(产品 varchar(1),cc int)
insert into BC
select 'A',10 union select 'B',20 union select 'C',30
-----------------實現語句-----------
update test set qty=BC.cc from test inner join
(select 产品,pp= min(cast(replace(加入时间,'-','') as int)) from test group by 产品) a
on test.产品=a.产品 and pp=cast(replace(test.加入时间,'-','') as int)
inner join BC on(bc.产品=test.产品)
----------------結果------------------
select * from test
1 A 1-1 10
2 B 1-2 20
3 C 1-3 0
4 A 1-3 0
5 A 1-4 0
6 C 1-2 30
7 B 1-3 0
drop table test