求帮助一个sql语句

[terchy]

两个表 订单表YSDD:序号XH,产品编号ZBXH_CPBH,单价DJ;
产品盘盈表CPRK:标识序号ZBXH,产品编号ZBXH_CPBH,单价ZBXH_DJ
YSDD：
XH ZBXH_CPBH DJ
172 100M220 0.85
194 100M288 0.63
120 100M633 0.32
150 100M633 0.42
162 100M633 0.41
...................（若干上万行）

CPRK：
ZBXH ZBXH_CPBH ZBXH_DJ
15823 100M633
15823 100M220
15823 100M120
.....................(13行ZBXH都是一样15823)

现在的要求就是更新CPRK表里所有产品号的ZBXH_DJ,取值是从YSDD取得相应产品（比如100M633）的最新DJ(方法就是取得100M633在YSDD里XH最大的那一行的DJ) 也就是说一个产品在YSDD可能有很多行取XH最大那行单价赋值到CPRK表里,然后就是做个语句把CPRK表里的13行都填入相应的单价就行
小弟很少编程求指教谢谢!

[giftzheng]

查询语句 select a.DJ,d.DJ,* from CPRK a join ( select b.ZBXH_CPBH,b.DJ from YSDD b join (select ZBXH_CPBH, max(XH) as XH YSDD group by ZBXH_CPBH) c on b.ZBXH_CPBH=c.ZBXH_CPBH and b.XH=c.XH ) d on a.ZBXH_CPBH=d.ZBXH_CPBH 修改语句 UPDATE A SET a.DJ=d.DJ join ( select b.ZBXH_CPBH,b.DJ from YSDD b join (select ZBXH_CPBH, max(XH) as XH YSDD group by ZBXH_CPBH) c on b.ZBXH_CPBH=c.ZBXH_CPBH and b.XH=c.XH ) d on a.ZBXH_CPBH=d.ZBXH_CPBH

[giftzheng]

UPDATE a SET a.DJ=d.DJ from CPRK a join ( select b.ZBXH_CPBH,b.DJ from YSDD b join (select ZBXH_CPBH, max(XH) as XH YSDD group by ZBXH_CPBH) c on b.ZBXH_CPBH=c.ZBXH_CPBH and b.XH=c.XH ) d on a.ZBXH_CPBH=d.ZBXH_CPBH --少写了一句.

[terchy]

谢谢师傅 明天看看

[terchy]

你这个句子在sql2008里能用吗，join

[發糞塗牆]

引用 4 楼 terchy 的回复:

你这个句子在sql2008里能用吗，join

join即使在2000也能用，别说2008了

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2014-01-09 07:29:44
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[YSDD]
if object_id('[YSDD]') is not null drop table [YSDD]
go 
create table [YSDD]([XH] int,[ZBXH_CPBH] varchar(7),[DJ] numeric(3,2))
insert [YSDD]
select 172,'100M220',0.85 union all
select 194,'100M288',0.63 union all
select 120,'100M633',0.32 union all
select 150,'100M633',0.42 union all
select 162,'100M633',0.41
--> 测试数据：[CPRK]
if object_id('[CPRK]') is not null drop table [CPRK]
go 
create table [CPRK]([ZBXH] int,[ZBXH_CPBH] varchar(7),[ZBXH_DJ] sql_variant)
insert [CPRK]
select 15823,'100M633',null union all
select 15823,'100M220',null union all
select 15823,'100M120',null
--------------开始查询--------------------------

UPDATE  [CPRK]
SET     [ZBXH_DJ] = b.dj
FROM    ( SELECT    dj ,
                    [ZBXH_CPBH]
          FROM      [YSDD] a
          WHERE     EXISTS ( SELECT 1
                             FROM   ( SELECT    MAX(xh) xh ,
                                                [ZBXH_CPBH]
                                      FROM      [YSDD]
                                      GROUP BY  [ZBXH_CPBH]
                                    ) b
                             WHERE  a.[ZBXH_CPBH] = b.[ZBXH_CPBH]
                                    AND a.xh = b.xh )
        ) b
WHERE   b.[ZBXH_CPBH] = [CPRK].[ZBXH_CPBH]
----------------结果----------------------------
/* 
*/