mysql - 错误 42000 Mydac TMyquery sql 脚本错误解析

Question

需要帮助Mydac TMyquery 无法使用此脚本在 navicat 和 sqlfiddle 中工作，但 tmyquery 不工作

SET @@group_concat_max_len = 32000;
SELECT
  group_concat(concat('sum(ifnull(if(s.id=',s.id,',m.qty,0),0))`',s.sizes,'`'))eval,
  group_concat(concat('i.`',s.sizes,'`'))list
INTO @eval, @list
from(
  SELECT DISTINCT s.id, s.sizes
  FROM property p
  JOIN size_goods s ON s.id=p.id_sizes
  WHERE p.id_goods IN (6,7,8)
  ORDER BY s.id
  )s;
SELECT group_concat(p.id)
INTO @where
  FROM property p
  WHERE p.id_goods IN (6,7,8)
  ;
SET @sql=concat_ws(' ',
  'select g.id, g.name, g.model,',
  @list,',i.Total,i.price,i.cargo_payment,i.Cost from(select p.id_goods id,',@eval,
  ',sum(ifnull(m.qty,0))Total',
  ',ifnull(sum(price*qty)/sum(qty),0)price',
  ',ifnull(sum(cargo_payment*qty)/sum(qty),0)cargo_payment',
  ',sum(ifnull(m.qty*(m.price+m.cargo_payment),0))Cost',
  'from property p',
  'join size_goods s on s.id=p.id_sizes',
  'left join (',
    'select id_property, id_actions, qty*(3-2*id_actions)qty, price, cargo_payment from moves',
    ') m on m.id_property=p.id',
  'where p.id in (',@where,')',
  'group by p.id_goods',
  ')i left join  goods g on g.id=i.id;'
  );  
SELECT @sql;
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

错误是最接近的 42000 sql 语法

Answer 1

关键是MySQL无法准备这样的SQL语句。您不能使用 API 调用来准备 PREPARE、EXECUTE 或 DEALLOCATE PREPARE 语句。更多信息请引用http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html 。为了避免此问题，您应该调用 TMyQuery.Open 方法而不调用 TMyQuery.Prepare 方法。要获取结果值，可以使用 AsString 属性。这是一个代码示例:

MyQuery.Open;
ShowMessage(MyQuery.Fields[0].AsString);