使用子查询时应当注意的
在一个查询中：
UPDATE a
SET a.scts = b.v1, a.YCYL = b.v2, a.YCSL = b.v3
FROM kfdbsyy a,
(SELECT f_wellnumber, COUNT(*) AS v1, SUM(f_fule) AS v2,
SUM(f_totalliquid - f_fule) AS v3
FROM (SELECT *
FROM CY_WELLRECORD
WHERE f_reporttime LIKE '2004-06%')
GROUP BY f_wellnumber) b
WHERE a.JH = b.f_wellnumber AND a.ny = '200406' 红色在子查询单独运行没有问题，但整个运行，老是报告group附近有错。反复试验，发现跟内部的子查询有关。改为
UPDATE a
SET a.scts = b.v1, a.YCYL = b.v2, a.YCSL = b.v3
FROM kfdbsyy a,
(SELECT f_wellnumber, COUNT(*) AS v1, SUM(f_fule) AS v2,
SUM(f_totalliquid - f_fule) AS v3
FROM (SELECT *
FROM CY_WELLRECORD
WHERE f_reporttime LIKE '2004-06%') as c
GROUP BY c.f_wellnumber) b
WHERE a.JH = b.f_wellnumber AND a.ny = '200406' 或者
UPDATE a
SET a.scts = b.v1, a.YCYL = b.v2, a.YCSL = b.v3
FROM kfdbsyy a,
(SELECT f_wellnumber, COUNT(*) AS v1, SUM(f_fule) AS v2,
SUM(f_totalliquid - f_fule) AS v3
FROM (SELECT *
FROM CY_WELLRECORD
WHERE f_reporttime LIKE '2004-06%') DERIVEDTBL
GROUP BY f_wellnumber) b
WHERE a.JH = b.f_wellnumber AND a.ny = '200406' 均可。
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2005-09-30 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读