使用子查询时应当注意的

最新推荐文章于 2022-02-24 10:05:08 发布

weixin_34331102

最新推荐文章于 2022-02-24 10:05:08 发布

阅读量265

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/jetz/archive/2005/09/30/247595.html

博客围绕SQL子查询展开，给出一个UPDATE查询示例，该查询中内部子查询单独运行正常，但整体运行时在group附近报错。经反复试验，发现问题与内部子查询有关，通过给子查询添加别名（如as c、DERIVEDTBL）可解决问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在一个查询中：

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'

均可。

转载于:https://www.cnblogs.com/jetz/archive/2005/09/30/247595.html