Sql高手請進

[PlayFort]

有一個表factgood 字段如下(factno 廠商編號
good 貨品編號
dtslipdate 報價日期
fprice 價格)請用一條sql語句求出某一廠商的所有貨品的最近一次報價
例如record1 : '001','001stk1','031215','20'
record2 : '001','001stk2','031215','20'
record3 : '001','001stk3','031215','20'
record4 : '001','001stk1','031216','20'
record5 : '001','001stk3','031217','20'
record6 : '001','001stk2','031218','20'
record7 : '001','001stk1','031219','20'
record8 : '001','001stk2','031220','20'
應得的結果是
'001','001stk3','031217','20'


'001','001stk1','031219','20'

'001','001stk2','031220','20'

[1ssp]

select a.* from factgood a inner join (select good,max(cast dtslipdat as int)as rq from factgood where factno='1' group by good) b on a.good =b.good and a.dtslipdat=b.rq
试试老兄

[devilwind]

select good,factno,dtslipdate fprice from factgood
where dtslipdate in (select max(cast(dtslipdate as int)) from factgood group by good)

[PlayFort]

iwantsay(吵闹) 1ssp(新来的) 你們的思路是一樣都可以了 .請問不用join ,inner join有甚麼辦法得出結果

[devilwind]

select good,factno,dtslipdate fprice from factgood
where dtslipdate in (select max(cast(dtslipdate as int)) from factgood group by good)
这个OK的，测试过的！

[zjcxc]

--同一厂商,同一货品,如果最大的日期不重复,可以用:

select * from factgood a
where dtslipdate=(select max(dtslipdate) from factgood where factno=a.factno and good=a.good)
order by good

[zjcxc]

--下面是测试

declare @factgood table(factno varchar(3),good varchar(10)
,dtslipdate varchar(8),fprice int)
insert into @factgood
select '001','001stk1','031215','21'
union all select '001','001stk2','031215','22'
union all select '001','001stk3','031215','23'
union all select '001','001stk1','031216','24'
union all select '001','001stk3','031217','25'
union all select '001','001stk2','031218','26'
union all select '001','001stk1','031219','27'
union all select '001','001stk2','031220','28'

--查询
select * from @factgood a
where dtslipdate=(select max(dtslipdate) from @factgood where factno=a.factno and good=a.good)
order by good

/*--测试结果
factno good dtslipdate fprice
------ ---------- ---------- -----------
001 001stk1 031219 27
001 001stk2 031220 28
001 001stk3 031217 25

（所影响的行数为 3 行）
--*/

[zjcxc]

--如果不满足条件:同一厂商,同一货品,如果最大的日期不重复,就需要在你原来的表中增加一个标识字段.然后用:

select * from factgood a
where id=(select max(id) from factgood where factno=a.factno and good=a.good order by dtslipdate desc)
order by good

[zjcxc]

--上面错了.
--如果不满足条件:同一厂商,同一货品,如果最大的日期不重复,就需要在你原来的表中增加一个标识字段.然后用:

select * from factgood a
where id=(select top 1 from factgood where factno=a.factno and good=a.good order by dtslipdate desc,id desc)
order by good

[PlayFort]

select good,factno,max(cast(dtslipdate as int),fprice from factgood
Group by good,factno,fprice 大哥這樣一定是不行的如果是如下數據那是錯的,我只不過上邊的數據給的巧而已.
例如record1 : '001','001stk1','031215','21'
record2 : '001','001stk2','031215','22'
record3 : '001','001stk3','031215','23'
record4 : '001','001stk1','031216','24'
record5 : '001','001stk3','031217','25'
record6 : '001','001stk2','031218','26'
record7 : '001','001stk1','031219','27'
record8 : '001','001stk2','031220','28'
這樣就是錯的大哥YOu try

[victorycyz]

select a.*
from factgood join
( select factno,good,max(dtslipdate)
from factgood
group by factno,good
where factno='要查询的厂商'
) b
on a.factno=b.factno and a.good=b.good and a.dtslipdate=b.dtslipdate

[LoveSQL]

select a.factno , a.good ,max(a.dtslipdate),a.fprice
from factgood a,(select distinct factno ,good from factgood )b
where a.factno=b.factno and a.good=b.good
order by a.factno,a.good

[w_rose]

select a.货品,a.日期,a.价格
from 报价 as a
inner join (select 货品,max(日期) as 日期 from 报价
where 厂商=@manufacture group by 货品
）as b on a.厂商=@manufacture and a.货品=b.货品 and a.日期=b.日期

[tangyanjun1]

select * from factgood
group by good,dtslipdate,fprice,factno
having dtslipdate = (select max(dtslipdate) from factgood b where b.good = table1.good)

[tangyanjun1]

insert into table1 values( '001','001stk1','031215','20')
insert into table1 values( '001','001stk2','031215','21')
insert into table1 values( '001','001stk3','031215','22')
insert into table1 values( '001','001stk1','031216','23')
insert into table1 values( '001','001stk3','031217','24')
insert into table1 values( '001','001stk2','031218','25')
insert into table1 values( '001','001stk1','031219','26')
insert into table1 values( '001','001stk2','031220','27')

测试结果如下：
001 001stk1 031219 26
001 001stk2 031220 27
001 001stk3 031217 24

[titan90]

select factno, good,max(dtslipdate),max(fprice) from factgood group by factno,good

[iwantsay]

select a.厂商,a.货品,a.日期,a.价格
from 报价 as a
inner join (select 厂商,货品,max(日期) as 日期 from 报价
where group by 厂商,货品
）as b on a.厂商=b.厂商 and a.货品=b.货品 and a.日期=b.日期

[PlayFort]

LOveSQl你的語句根本都run不過去,大哥自信是好的,但是還是要run 一下是否能跑過

[azsoft]

select distinct good,factno,dtslipdate,fprice from factgood

[PlayFort]

iwantsay(吵闹) 大哥自信是好的,但是還是要run 一下是否能跑過,where 怎麼和group 在一起呢

[PlayFort]

select distinct good,factno,dtslipdate,fprice from factgood
這樣一定不行的,大哥