mysql问题创建视图

42fyovps 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(506)

我有这个问题

SET @regex_bl = (SELECT GROUP_CONCAT(DISTINCT word ORDER BY word DESC SEPARATOR '|') FROM t_black_list);
SET @regex_sl = (SELECT GROUP_CONCAT(DISTINCT word ORDER BY word DESC SEPARATOR '|') FROM t_suspect_list);
SELECT
p_f_A.ID,
p_f_A.id_st,
p_f_A.source_query,
p_f_A.st_text,
if (p_f_A.st_image regexp 'http' = 1,p_f_A.st_image,'NO IMAGE') AS st_IMAGE_URL,
if (p_f_A.st_text regexp @regex_bl = 1,'YES','NO') AS BLACK_LIST,
if (p_f_A.st_text regexp @regex_sl = 1,'YES','NO') AS SUSPECT_LIST,
(select f_f_image_recog.TAG from f_f_image_recog WHERE (f_f_image_recog.id_st=p_f_A.ID AND f_f_image_recog.TAG NOT LIKE '%Failure%' ) ORDER BY f_f_image_recog.value DESC  LIMIT 1) AS CLARIFAI_1ST_TAG,
(SELECT "X" as X from f_f_tensor_ws WHERE (f_f_tensor_ws.id_st=p_f_A.ID) AND (f_f_tensor_ws.value > 0.98) LIMIT 1 ) AS TS_B_GREEN_LABEL_98,
(SELECT f_f_text_recog.matched_text from f_f_text_recog WHERE (f_f_text_recog.id_st =p_f_A.ID) AND (f_f_text_recog.type LIKE '%Money%') LIMIT 1) AS NLP_MONEY_VALUE,
(SELECT f_f_text_recog.matched_text from f_f_text_recog WHERE (f_f_text_recog.id_st =p_f_A.ID) AND (f_f_text_recog.type LIKE '%Company%') LIMIT 1) AS NLP_COMPANY
from p_f_A

如果我在我的工具（heidisql）的查询框中运行它，所有的工作都和预期的一样。尝试创建视图时出现错误：
erroresql（1064）：您的sql语法有错误；请查看与您的mysql服务器版本对应的手册，以了解在第1行的“set@regex\u bl=（select group\u concat（distinct word order by word desc separator）”附近使用的正确语法

mysql mysql-error-1064

来源：https://stackoverflow.com/questions/50152650/sql-error-1064-mysql-issue-creating-view

2条答案

按热度按时间

0h4hbjxa1#

你好，我对查询做了如下修改，现在可以工作了：

SELECT
p_f_A.ID,
p_f_A.id_st,
p_f_A.source_query,
p_f_A.st_text,
if (p_f_A.st_image regexp 'http' = 1,p_f_A.st_image,'NO IMAGE') AS st_IMAGE_URL,
if (p_f_A.st_text regexp (SELECT GROUP_CONCAT(DISTINCT word ORDER BY word DESC SEPARATOR '|') FROM t_black_list) = 1,'YES','NO') AS BLACK_LIST,
if (p_f_A.st_text regexp (SELECT GROUP_CONCAT(DISTINCT word ORDER BY word DESC SEPARATOR '|') FROM t_suspect_list) = 1,'YES','NO') AS SUSPECT_LIST,
(select f_f_image_recog.TAG from f_f_image_recog WHERE (f_f_image_recog.id_st=p_f_A.ID AND f_f_image_recog.TAG NOT LIKE '%Failure%' ) ORDER BY f_f_image_recog.value DESC  LIMIT 1) AS CLARIFAI_1ST_TAG,
(SELECT "X" as X from f_f_tensor_ws WHERE (f_f_tensor_ws.id_st=p_f_A.ID) AND (f_f_tensor_ws.value > 0.98) LIMIT 1 ) AS TS_B_GREEN_LABEL_98,
(SELECT f_f_text_recog.matched_text from f_f_text_recog WHERE (f_f_text_recog.id_st =p_f_A.ID) AND (f_f_text_recog.type LIKE '%Money%') LIMIT 1) AS NLP_MONEY_VALUE,
(SELECT f_f_text_recog.matched_text from f_f_text_recog WHERE (f_f_text_recog.id_st =p_f_A.ID) AND (f_f_text_recog.type LIKE '%Company%') LIMIT 1) AS NLP_COMPANY
from p_f_A

赞(0）回复(0）举报 2021-06-21

hs1rzwqc2#

我不能从你的代码中看到你如何声明你的变量，我只看到你设置的值。这会有很大的不同。
问题可能是因为在视图中使用变量的方式与在存储过程或函数中使用变量的方式不同。
请看下面的帖子中的一些选项。sql视图-没有变量？
试着从上面提到的帖子中给出这个答案：

CREATE VIEW MyView

AS

  WITH MyVars (SomeVar, Var2)

  AS (

    SELECT

      'something' AS 'SomeVar',

      123 AS 'Var2'

  )

  SELECT *

  FROM MyTable

  WHERE x = (SELECT SomeVar FROM MyVars)

赞(0）回复(0）举报 2021-06-21

我来回答

mysql问题创建视图

2条答案

相关问题

热门标签

最新问答