3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
Sql高手請進
有一個表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'
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'
...全文
请发表友善的回复…
发表回复
PlayFort 2003-12-18
select distinct good,factno,dtslipdate,fprice from factgood
這樣一定不行的,大哥
這樣一定不行的,大哥
PlayFort 2003-12-18
iwantsay(吵闹) 大哥自信是好的,但是還是要run 一下是否能跑過,where 怎麼和group 在一起呢
azsoft 2003-12-18
select distinct good,factno,dtslipdate,fprice from factgood
PlayFort 2003-12-18
LOveSQl你的語句根本都run不過去,大哥自信是好的,但是還是要run 一下是否能跑過
iwantsay 2003-12-18
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.日期
from 报价 as a
inner join (select 厂商,货品,max(日期) as 日期 from 报价
where group by 厂商,货品
）as b on a.厂商=b.厂商 and a.货品=b.货品 and a.日期=b.日期
titan90 2003-12-18
select factno, good,max(dtslipdate),max(fprice) from factgood group by factno,good
tangyanjun1 2003-12-18
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
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
tangyanjun1 2003-12-18
select * from factgood
group by good,dtslipdate,fprice,factno
having dtslipdate = (select max(dtslipdate) from factgood b where b.good = table1.good)
group by good,dtslipdate,fprice,factno
having dtslipdate = (select max(dtslipdate) from factgood b where b.good = table1.good)
w_rose 2003-12-18
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.日期
from 报价 as a
inner join (select 货品,max(日期) as 日期 from 报价
where 厂商=@manufacture group by 货品
）as b on a.厂商=@manufacture and a.货品=b.货品 and a.日期=b.日期
LoveSQL 2003-12-18
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
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
victorycyz 2003-12-18
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
PlayFort 2003-12-18
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
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
zjcxc--个人微信公共号同名 2003-12-18
--上面错了.
--如果不满足条件:同一厂商,同一货品,如果最大的日期不重复,就需要在你原来的表中增加一个标识字段.然后用:
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
--如果不满足条件:同一厂商,同一货品,如果最大的日期不重复,就需要在你原来的表中增加一个标识字段.然后用:
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
zjcxc--个人微信公共号同名 2003-12-18
--如果不满足条件:同一厂商,同一货品,如果最大的日期不重复,就需要在你原来的表中增加一个标识字段.然后用:
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
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--个人微信公共号同名 2003-12-18
--下面是测试
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 行）
--*/
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--个人微信公共号同名 2003-12-18
--同一厂商,同一货品,如果最大的日期不重复,可以用:
select * from factgood a
where dtslipdate=(select max(dtslipdate) from factgood where factno=a.factno and good=a.good)
order by good
select * from factgood a
where dtslipdate=(select max(dtslipdate) from factgood where factno=a.factno and good=a.good)
order by good
devilwind 2003-12-18
select good,factno,dtslipdate fprice from factgood
where dtslipdate in (select max(cast(dtslipdate as int)) from factgood group by good)
这个OK的，测试过的！
where dtslipdate in (select max(cast(dtslipdate as int)) from factgood group by good)
这个OK的，测试过的！
PlayFort 2003-12-18
iwantsay(吵闹) 1ssp(新来的) 你們的思路是一樣都可以了 .請問不用join ,inner join有甚麼辦法得出結果
devilwind 2003-12-18
select good,factno,dtslipdate fprice from factgood
where dtslipdate in (select max(cast(dtslipdate as int)) from factgood group by good)
where dtslipdate in (select max(cast(dtslipdate as int)) from factgood group by good)
1ssp 2003-12-18
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
试试老兄
试试老兄