两张表,用一张表来更新另外一张:
货品信息表T_HPXX的两个字段(HH,PJCB),HH是主键
XH HH PJCB
1 001 50.00
2 002 55.00 成本明细表T_PJCB_MX(HH,PJCB,CBSJ)
XH HH PJCB CBSJ
1 001 50 2012-08-01
2 001 52.23 2012-08-02
3 001 53.10 2012-08-03
-------两表查询的结果为--------------
Select a.HH,a.PJCB,B.PJCB,b.CBSJ From T_HPXX a,T_PJCB_MX b Where a.HH=b.HH and datediff(d,b.CBSJ,getdate())>=0 And a.HH='ZDX005'
的结果为
001 50 50 2012-08-01
001 50 52.23 2012-08-02
001 50 53.10 2012-08-03--------------------
我想要的结果是用T_PJCB_MX里的离当前日期最近的一天的PJCB值更新到T_HPXX的PJCB字段
Update a Set a.PJCB=b.PJCB
From T_HPXX a,T_PJCB_MX b Where a.HH=b.HH and datediff(d,b.CBSJ,getdate())>=0
And a.HH='ZDX005'
的结果不正确的,麻烦各位大虾帮我看下该语句要怎么改呢?
货品信息表T_HPXX的两个字段(HH,PJCB),HH是主键
XH HH PJCB
1 001 50.00
2 002 55.00 成本明细表T_PJCB_MX(HH,PJCB,CBSJ)
XH HH PJCB CBSJ
1 001 50 2012-08-01
2 001 52.23 2012-08-02
3 001 53.10 2012-08-03
-------两表查询的结果为--------------
Select a.HH,a.PJCB,B.PJCB,b.CBSJ From T_HPXX a,T_PJCB_MX b Where a.HH=b.HH and datediff(d,b.CBSJ,getdate())>=0 And a.HH='ZDX005'
的结果为
001 50 50 2012-08-01
001 50 52.23 2012-08-02
001 50 53.10 2012-08-03--------------------
我想要的结果是用T_PJCB_MX里的离当前日期最近的一天的PJCB值更新到T_HPXX的PJCB字段
Update a Set a.PJCB=b.PJCB
From T_HPXX a,T_PJCB_MX b Where a.HH=b.HH and datediff(d,b.CBSJ,getdate())>=0
And a.HH='ZDX005'
的结果不正确的,麻烦各位大虾帮我看下该语句要怎么改呢?
From T_HPXX a,(SELECT distinct HH,PJCB,MAX(CBSJ)CBSJ FROM T_PJCB_MX) b Where a.HH=b.HH
And a.HH='ZDX005'
-->try
update T_HPXX set PJCB=b.PJCB
from T_HPXX a
inner join
(select distinct t.HH, PJCB=(select top 1 PJCB from T_PJCB where t.HH=HH order by CBSJ desc)
from T_PJCB t) b
on a.HH=b.HH
我要的条件是离当前日期最近的一天的PJCB
( SELECT distinct HH,PJCB,MAX(CBSJ)CBSJ FROM T_PJCB_MX)
);
Update a Set a.PJCB=T.PJCB
From T_HPXX a,T Where a.HH=T.HH
And a.HH='ZDX005'
CREATE TABLE T_PJCB_MX(HH VARCHAR(32),PJCB DECIMAL(18,2),CBSJ DATE)
INSERT INTO T_PJCB_MX
SELECT '001', 50,'2012-08-01' UNION
SELECT '001', 52.23,'2012-08-02' UNION
SELECT '001', 53.10,'2012-08-03' SELECT * FROM T_PJCB_MX
/*
HH PJCB CBSJ
001 50.00 2012-08-01
001 52.23 2012-08-02
001 53.10 2012-08-03
*/CREATE TABLE T_HPXX(HH VARCHAR(32),PJCB DECIMAL(18,2))
INSERT INTO T_HPXX SELECT '001',50.00 UNION
SELECT'002',55.00
SELECT * FROM T_HPXX
/*
HH PJCB
001 50.00
002 55.00
*/
Update a Set a.PJCB=b.PJCB
From T_HPXX a,
(SELECT aa.* FROM T_PJCB_MX aa,(SELECT HH,MAX(PJCB)PJCB FROM T_PJCB_MX GROUP BY HH) bb
Where aa.HH=bb.HH and aa.PJCB=bb.PJCB )b
WHERE a.HH=B.HH
/*
HH PJCB
001 53.10
002 55.00
*/
请教一下:你的语句中SELECT HH,MAX(PJCB)PJCB FROM T_PJCB_MX GROUP BY HH---为什么要用MAX(PJCB)?我的条件是离“当前日期最近的那天的PJCB”,你用MAX(PJCB)不对啊
update T_HPXX set PJCB=b.PJCB
from T_HPXX a
inner join
(select distinct t.HH, PJCB=(select top 1 PJCB from T_PJCB where t.HH=HH and datediff(d,CBSJ,getdate())>=0 order by CBSJ desc)
from T_PJCB t) b
on a.HH=b.HH
我的那种写法是假定 当前日期是 顺序向下延伸 即按照正常日期往下排(比如 2012-09-3)如果你的当前日期是 参数(比如 2012-07-01) 我那种就不适合了。 这样的话应该要用到 DATEDIFF函数了暂时想到。更好的方法等高手出现吧
DROP TABLE T_HPXX
GO
create table T_HPXX(XH int,HH nvarchar(10),PCJB money)insert into T_HPXX
select 1,'001',50.00
union
select 2,'002',55.00
IF OBJECT_ID('T_PJCB_MX') IS NOT NULL
DROP TABLE T_PJCB_MX
GO
creAte table T_PJCB_MX(XH INT,HH nvarchar(10),PCJB money,CBSJ DATETIME)INSERT INTO T_PJCB_MX
SELECT 1,'001',50,'2012-08-01'
UNION
SELECT 1,'001',52.23,'2012-08-02'
UNION
SELECT 1,'001',53.23,'2012-08-03'
UPDATE T_HPXX SET PCJB=b.pcjb
from T_HPXX a,
(SELECT TOP 1 HH,PCJB,MIN(DATEDIFF(DAY,CBSJ,GETDATE())) CBSJ FROM T_PJCB_MX
GROUP BY HH,PCJB
ORDER BY CBSJ) as b
where a.hh=b.hh
DROP TABLE T_HPXX
GO
create table T_HPXX(XH int,HH nvarchar(10),PCJB money)insert into T_HPXX
select 1,'001',50.00
union
select 2,'002',55.00
IF OBJECT_ID('T_PJCB_MX') IS NOT NULL
DROP TABLE T_PJCB_MX
GO
creAte table T_PJCB_MX(XH INT,HH nvarchar(10),PCJB money,CBSJ DATETIME)INSERT INTO T_PJCB_MX
SELECT 1,'001',50,'2012-08-01'
UNION
SELECT 1,'001',52.23,'2012-08-02'
UNION
SELECT 1,'001',53.23,'2012-08-03'
UNION
SELECT 1,'001',55.23,'2012-09-05'
UPDATE T_HPXX SET PCJB=b.pcjb
from T_HPXX a,
(SELECT TOP 1 HH,PCJB,DATEDIFF(DAY,CBSJ,GETDATE()) CBSJ FROM T_PJCB_MX
order by cbsj
) as b
where a.hh=b.hh
作用是用原表关联提供数据的表,如果当前日期指是你输入的日期参数的话,那就要改成用datediff