mysql存储过程中无法识别的数据类型错误

I am trying to run this query but I keep getting Unrecognized datatype near C.id. What am I doing wrong?

我正在尝试运行此查询,但我一直在C.id附近获得无法识别的数据类型。我究竟做错了什么?

CREATE PROCEDURE `proc`
BEGIN
CREATE TABLE 
  temp_table  
AS (
  SELECT UNIQUE(C.id) FROM table1AS C
                INNER JOIN table2 AS CA ON CA.college_id in (8192)
                INNER JOIN table3 AS CS ON CS.skill_id in (2744)
)

select * from temp_table

END

Thanks.

3 个解决方案

#1

CREATE PROCEDURE `proc`()
BEGIN
      drop temporary table if exists temp_table;
      CREATE temporary TABLE temp_table  
      AS (
           SELECT distinct(C.id) FROM table1 AS C
           INNER JOIN table2 AS CA ON CA.college_id in (8192)
           INNER JOIN table3 AS CS ON CS.skill_id in (2744)
         );

      select * from temp_table;

END

#2

Try to use DISTINCT. There is nothing like UNIQUE keyword in MYSQL. try this:

尝试使用DISTINCT。 MYSQL中没有像UNIQUE关键字那样的东西。尝试这个:

CREATE PROCEDURE `proc`()
BEGIN
CREATE TABLE 
  temp_table  
AS (
  SELECT DISTINCT(C.id) FROM table1 AS C
                INNER JOIN table2 AS CA ON CA.college_id in (8192)
                INNER JOIN table3 AS CS ON CS.skill_id in (2744)
);

select * from temp_table;

END

#3

First you need to use DISTINCT instead of UNIQUE because UNIQUE is no more in MySQL and second one missing space table1AS so modified table1 AS and third one you are missing () to name of store procedure and last ; is missing before select query,

首先,您需要使用DISTINCT而不是UNIQUE,因为UNIQUE不再是MySQL,第二个缺少空间table1AS所以修改了table1 AS,第三个缺少()到商店过程名称和last;在选择查询之前丢失,

So modified query will be:

所以修改后的查询将是:

CREATE PROCEDURE `proc`()
BEGIN
CREATE TABLE 
  temp_table  
AS (
  SELECT DISTINCT(C.id) FROM table1 AS C
                INNER JOIN table2 AS CA ON CA.college_id in (8192)
                INNER JOIN table3 AS CS ON CS.skill_id in (2744)
);

select * from temp_table;

END