一个update语句，求简化
/*感觉写的有点太繁琐了吧，求简化
1.把生成的号码@cfbh写入明细表中,且当ksbh, ysbh, hlks, hlr, ssid, bcbh, yryz, youhui不同时，@cfbh+排序号，保存不重复

*/ 
DECLARE @cfbh VARCHAR(20),@yrzyls VARCHAR(20),@rq DATETIME,@zyls VARCHAR(20)
SELECT @rq=GETDATE()
SELECT @cfbh=CONVERT(VARCHAR(20),@rq,102),@yrzyls='',@zyls='ABC'
UPDATE t2
SET t2.by1 = @cfbh+cast(t3.rowid as varchar(10))
FROM
zy_brcfmx_ys t2,
(
SELECT ksbh, ysbh, hlks, hlr, ssid, bcbh, yryz, youhui,
ROW_NUMBER() OVER (ORDER BY ksbh, ysbh, hlks, hlr, ssid, bcbh, yryz, youhui ASC) AS ROWID
FROM
(
SELECT
t2.ksbh,t2.ysbh,t2.hlks,t2.hlr,t2.ssid,t2.bcbh,t2.yryz,t2.youhui
FROM zybrzl t1,zy_brcfmx_ys t2
WHERE t1.zyls = t2.zyls
AND t2.shbz = 1
AND t2.zhscrq = @rq
AND t1.zyls = @zyls
AND isnull(t2.yryz,'') = @yrzyls
GROUP BY t1.blh,t1.xm,t1.ickh,t1.nl,t1.nldw,t1.sflx,t1.xb,
t2.ksbh,t2.ysbh,t2.hlks,t2.hlr,t2.ssid,t2.bcbh,t2.yryz,t2.youhui
UNION
SELECT
t2.ksbh,t2.ysbh,t2.hlks,t2.hlr,t2.ssid,t2.bcbh,t2.yryz,t2.youhui
FROM zybrzl t1,zy_brcfmx_ys_fjxm t2
WHERE t1.zyls = t2.zyls
AND t2.shbz = 1
AND t2.zhscrq = @rq
AND t1.zyls = @zyls
AND isnull(t2.yryz,'') = @yrzyls
GROUP BY t1.blh,t1.xm,t1.ickh,t1.nl,t1.nldw,t1.sflx,t1.xb,
t2.ksbh,t2.ysbh,t2.hlks,t2.hlr,t2.ssid,t2.bcbh,t2.yryz,t2.youhui
) t
)
t3
WHERE t2.ksbh = t3.ksbh
AND t2.ysbh = t3.ysbh
AND t2.youhui = t3.youhui
AND isnull(t2.hlks,'') = isnull(t3.hlks,'')
AND isnull(t2.hlr,'') = isnull(t3.hlr,'')
AND isnull(t2.ssid,'') = isnull(t3.ssid,'')
AND isnull(t2.yryz,'') = isnull(t3.yryz,'')
AND isnull(t2.bcbh,'') = isnull(t3.bcbh,'')
AND t2.shbz = 1
AND t2.zhscrq = @rq
AND t2.zyls = @zyls
and t2.cflx=10
AND isnull(t2.yryz,'') = @yrzyls

------解决方案--------------------
膜拜写这个语句的人。

那么多子查询。
------解决方案--------------------
能说明一下这样写的目的吗？？
------解决方案--------------------
岂止是繁琐!
比如:

SQL code

    SELECT t2.ksbh,t2.ysbh,t2.hlks,t2.hlr,t2.ssid,t2.bcbh,t2.yryz,t2.youhui
    FROM zybrzl t1,zy_brcfmx_ys t2
    WHERE t1.zyls = t2.zyls AND t2.shbz = 1 AND t2.zhscrq = @rq    AND t1.zyls = @zyls    AND isnull(t2.yryz,'') = @yrzyls
    GROUP BY t1.blh,t1.xm,t1.ickh,t1.nl,t1.nldw,t1.sflx,t1.xb,t2.ksbh,t2.ysbh,t2.hlks,t2.hlr,t2.ssid,t2.bcbh,t2.yryz,t2.youhui

------解决方案--------------------
你还不如多加几个临时表，用临时表去更新。

------解决方案--------------------
用临时表处理比较好，如果不是实时的数据更新的话，还是比较一个比较好的选择。

------解决方案--------------------

探讨


his程序中的一个转换步骤，把2个明细表生成cfbh,
然后合并汇总插入到 另一处方表中去，处方表中（cfbh是主键)有ksbh, ysbh, hlks, hlr, ssid, bcbh, yryz, youhui这些字段的,
然后再把明细插入到另外的处方明细表中
上面的语句可以简化一点，子查询是为了得到rownumber()


呵呵，出去吃饭了，下午再解释


------解决方案--------------------

探讨


看着有点别扭，所以想改进一下
2005中好像有排名函数或over可以利用一下
吧，具体怎么写没用过
主要是想获得
select  ksbh, ysbh, hlks, hlr, ssid, bcbh, yryz, youhui,sort_id --(sort_id,是按前面几列汇总后的排序号)
form t