select *
from cgbjdzhb,cgbjdmxb
where 单据日期=(select max(单据日期) from cgbjdzhb a where cgbjdzhb.供应商ID=a.供应商ID)
and cgbjdzhb.单据编号=cgbjdmxb.单据编号
解决方案 »
- 求一个求和的SQL语句???
- 一超难的sql查询
- 一个关于FREETEXT及CONTAINS,还有FREETEXTTABLE及CONTAINSTABLE的问题.
- 菜鸟请教一个问题
- 这个trigger 有点问题。有时候执行不正确,情大家看看错在哪里?
- 我们公司最近将sqlserver7换成了sqlserver2000,出现了一些问题,急
- 一道SQL问题
- (急)大家好,问一个SQL语句,现在遇到困难了,谢谢大家!
- 请大家帮个忙!!
- 在SQL SERVER中,有使用关系的问题求解!!请各位大虾多多帮助!在线等待!!
- 关于更新字段信息的问题
- 当客户机连接不上MS SQL Server时,如何让它快速返回连接失败?
-------------------------------- ------------------------------------------------------ -----------
C08081100011 2008-08-11 00:00:00.000 650
C08081100012 2008-08-11 00:00:00.000 648
C08090300013 2008-09-03 00:00:00.000 648明细表
djbh wlid dj
-------------------------------- ----------- --------------------------
C08081100011 301 1.00000000
C08081100011 302 1.00000000
C08081100011 685 1.00000000
C08081100011 682 1.00000000
C08081100011 681 1.00000000
C08081100011 684 1.00000000
C08081100011 299 1.00000000
C08081100011 300 1.00000000
C08081100011 683 1.00000000
C08081100011 680 1.00000000
C08081100012 830 1.00000000
C08081100012 830 1.00000000
C08081100012 274 8.00000000
C08081100012 93 1.00000000
C08081100012 902 1.00000000
C08081100012 904 1.00000000
C08081100012 396 1.00000000
C08081100012 390 1.00000000
C08081100012 406 1.00000000
C08081100012 142 1.00000000
C08081100012 143 1.00000000
C08090300013 296 1.00000000
C08090300013 390 12.00000000这样行吗?
cgbjdmxb 这是明细表 含单据编号(外键) 物料ID 加工价字段 SELECT a.供应商ID,
b.物料ID,
(
SELECT TOP 1 加工价
FROM cgbjdzhb aa,cgbjdmxb bb
WHERE aa.单据编号=bb.单据编号
AND aa.供应商ID= a.供应商ID
AND bb.物料ID= b.物料ID
ORDER BY aa.单据日期 DESC
) as 最新加工价
FROM cgbjdzhb a,cgbjdmxb b
WHERE a.单据编号=b.单据编号
AND a.供应商ID=@要查询的供应商ID
create table 主表(djbh varchar(20),djrq datetime,gsid int)
insert into 主表
select 'C08081100011','2008-08-11 00:00:00.000',650
union all select 'C08081100012','2008-08-11 00:00:00.000',648
union all select 'C08090300013','2008-09-03 00:00:00.000',648 create table 明细表(djbh varchar(20),wlid int,dj money)
insert into 明细表
select 'C08081100011',301,1.00000000
union all select 'C08081100011',302,1.00000000
union all select 'C08081100011',685,1.00000000
union all select 'C08081100011',682,1.00000000
union all select 'C08081100011',681,1.00000000
union all select 'C08081100011',684,1.00000000
union all select 'C08081100011',299,1.00000000
union all select 'C08081100011',300,1.00000000
union all select 'C08081100011',683,1.00000000
union all select 'C08081100011',680,1.00000000
union all select 'C08081100012',830,1.00000000
union all select 'C08081100012',830,1.00000000
union all select 'C08081100012',274,8.00000000
union all select 'C08081100012',93, 1.00000000
union all select 'C08081100012',902,1.00000000
union all select 'C08081100012',904,1.00000000
union all select 'C08081100012',396,1.00000000
union all select 'C08081100012',390,1.00000000
union all select 'C08081100012',406,1.00000000
union all select 'C08081100012',142,1.00000000
union all select 'C08081100012',143,1.00000000
union all select 'C08090300013',296,1.00000000
union all select 'C08090300013',390,12.00000000 select a.wlid,a.dj
from 明细表 a
inner join 主表 b on a.djbh=b.djbh
where b.djrq=(select max(djrq) from 主表 c,明细表 d where b.djrq=c.djrq and c.djbh=d.djbh and d.wlid=a.wlid)
/*
wlid dj
----------- ---------------------
301 1.0000
302 1.0000
685 1.0000
682 1.0000
681 1.0000
684 1.0000
299 1.0000
300 1.0000
683 1.0000
680 1.0000
830 1.0000
830 1.0000
274 8.0000
93 1.0000
902 1.0000
904 1.0000
396 1.0000
390 1.0000
406 1.0000
142 1.0000
143 1.0000
296 1.0000
390 12.0000(所影响的行数为 23 行)
*/
select c.gsid,c.wlid,e.加工价 from
(
select gsid,wlid,djrq=max(djrq)
from cgbjdzhb a join cgbjdmxb b on a.djbh = b.djbh
group by gsid,wlid
) c
join cgbjdzhb d on d.gsid=c.gsid and d.djrq=c.djrq
join cgbjdmxb e on e.djbh=d.djbh