求大神看看SQL哪里有问题

Question

UPDATE
ent_emodel_energy_data_quarter,
(SELECT
EMODEL_ENERGY_DATA_ID,
ent_emodel_energy_data_quarter.EMODEL_ENERGY_ID AS EMODEL_ENERGY_ID,
DATA_TIME,
(SELECT
SUM(VALUE)
FROM
ent_emodel_energy_data_month
WHERE (
DATA_TIME = ent_emodel_energy_data_quarter.DATA_TIME
OR ent_emodel_energy_data_quarter.DATA_TIME BETWEEN '2015-08-01 00:00:00'
AND '2015-08-31 23:59:59'
OR ent_emodel_energy_data_quarter.DATA_TIME BETWEEN '2015-09-01 00:00:00'
AND '2015-09-30 23:59:59'
AND EMODEL_ENERGY_ID = ent_emodel_energy_data_quarter.EMODEL_ENERGY_ID
)) AS VALUE
FROM
ent_emodel_energy_data_quarter
INNER JOIN ent_emodel_energy
ON ent_emodel_energy_data_quarter.EMODEL_ENERGY_ID = ent_emodel_energy.EMODEL_ENERGY_ID
WHERE DATA_TIME = '2015-07-01'
AND IS_MANUAL_INPUT = 1
AND PERIOD_ID <= 8
AND IS_ENABLE = 1) AS changedata
SET
ent_emodel_energy_data_quarter.VALUE = changedata.VALUE
WHERE ent_emodel_energy_data_quarter.EMODEL_ENERGY_DATA_ID = changedata.EMODEL_ENERGY_DATA_ID

MySql为什么可以跑起来，SqlServer直接挂掉了。。。报错[Err] 42000 - [SQL Server]“,”附近有语法错误。
42000 - [SQL Server]关键字 'AS' 附近有语法错误。

Answer 1

格式化一下先：

UPDATE
  ent_emodel_energy_data_quarter,
  (
    SELECT
      EMODEL_ENERGY_DATA_ID,
      ent_emodel_energy_data_quarter.EMODEL_ENERGY_ID AS EMODEL_ENERGY_ID,
      DATA_TIME,
      (
        SELECT
          SUM(VALUE)
        FROM
          ent_emodel_energy_data_month
        WHERE
          (
            DATA_TIME = ent_emodel_energy_data_quarter.DATA_TIME
          OR ent_emodel_energy_data_quarter.DATA_TIME BETWEEN
            '2015-08-01 00:00:00' AND '2015-08-31 23:59:59'
          OR ent_emodel_energy_data_quarter.DATA_TIME BETWEEN
            '2015-09-01 00:00:00' AND '2015-09-30 23:59:59'
          AND EMODEL_ENERGY_ID =
            ent_emodel_energy_data_quarter.EMODEL_ENERGY_ID
          )
      ) AS VALUE
    FROM
      ent_emodel_energy_data_quarter
    INNER JOIN ent_emodel_energy
    ON
      ent_emodel_energy_data_quarter.EMODEL_ENERGY_ID =
      ent_emodel_energy.EMODEL_ENERGY_ID
    WHERE
      DATA_TIME         = '2015-07-01'
    AND IS_MANUAL_INPUT = 1
    AND PERIOD_ID      <= 8
    AND IS_ENABLE       = 1
  ) AS changedata
SET
  ent_emodel_energy_data_quarter.VALUE = changedata.VALUE
WHERE
  ent_emodel_energy_data_quarter.EMODEL_ENERGY_DATA_ID =
  changedata.EMODEL_ENERGY_DATA_ID

类似SQL在mysql里确实可以使用。
但在mssql和oracle，恐怕都不行。

MS SQL Server的语法：
update t1 set t1.tValue = t2.tValue
from t1
inner join t2 on t1.id = t2.id
例子：
update area
set area_phone_code = z.code
from area
inner join test.zipcode z
on z.areaid = a.area_code

而oracle中可以这么写：
UPDATE 表2
SET
表2.C = (SELECT B FROM 表1 WHERE 表1.A = 表2.A)
WHERE
EXISTS ( SELECT 1 FROM 表1 WHERE 表1.A = 表2.A)

根据不同的数据库你需要重新写sql，不要期望一个复杂sql多库通用，除非，你把他们拆成多个简单查询，在一个事务里运行才有可能。