select a.供應商編號,a.供應商簡稱,a.生效日期=max(a.生效日期),a.報價單號,a.幣別,b.材料代碼,b.品名,b.規格,b.單位,b.單價 from BJZ a inner join BJC b on a.报价单号=b.报价单号 group by a.供應商編號
select a.供應商編號,a.供應商簡稱,a.生效日期=max(a.生效日期),a.報價單號,a.幣別,b.材料代碼,b.品名,b.規格,b.單位,b.單價 from BJZ a inner join BJC b on a.报价单号=b.报价单号
select a.供應商編號,a.供應商簡稱,a.生效日期=max(a.生效日期),a.報價單號,a.幣別,b.材料代碼,b.品名,b.規格,b.單位,b.單價 from BJZ a inner join BJC b on a.报价单号=b.报价单号
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
select 供應商編號、供應商簡稱、生效日期、a.報價單號,幣別,材料代碼、品名、規格、單位、單價
from bjz a join bjc b on a.報價單號=b.報價單號
and a.報價單號 in (select max(生效日期) from bjz group by 報價單號)
select 供應商編號、供應商簡稱、生效日期、a.報價單號,幣別,材料代碼、品名、規格、單位、單價
from bjz a join bjc b on a.報價單號=b.報價單號
and a.報價單號 in (select max(生效日期) from bjz group by 報價單號,供應商編號)
go
create table BJC (dm int ,pm varchar(20),gg varchar(40),dh int)
goinsert into BJZ
select 1,'华为','2005-02-01',1
union all
select 1,'华为','2005-03-01',2
union all
select 2,'中兴','2005-02-01',3
union all
select 3,'UTS','2005-04-21',4
union all
select 4,'TCL','2005-04-21',5
union all
select 5,'联想','2005-04-21',6
union all
select 3,'UTS','2005-04-21',7
goinsert into BJC select 1,'小灵通','蓝屏',1
union all select 1,'小灵通','蓝屏',2
union all select 1,'小灵通','蓝屏',3
union all select 1,'小灵通','蓝屏',4
union all select 2,'手机','彩屏',5
union all select 2,'手机','彩屏',6
union all select 2,'手机','彩屏',7
goselect BJZ.id,BJZ.name,BJZ.dt,BJC.dm,bjc.pm,bjc.gg from BJZ,BJC where BJZ.dh=BJC.dh and BJZ.dt=(select max(dt) from BJZ a,BJC b where a.dh=b.dh and BJZ.id=a.id and b.dm=BJC.dm group by a.id,b.dm)drop table BJZ
drop table BJcid name dt dm pm gg
----------- -------------------- ------------------------------------------------------ ----------- -------------------- ----------------------------------------
1 华为 2005-03-01 00:00:00.000 1 小灵通 蓝屏
2 中兴 2005-02-01 00:00:00.000 1 小灵通 蓝屏
3 UTS 2005-04-21 00:00:00.000 1 小灵通 蓝屏
4 TCL 2005-04-21 00:00:00.000 2 手机 彩屏
5 联想 2005-04-21 00:00:00.000 2 手机 彩屏
3 UTS 2005-04-21 00:00:00.000 2 手机 彩屏(所影响的行数为 6 行)