字符集不匹配合并

Question

我对这个查询有问题，它告诉我错误

ORA-00932: inconsistent datatypes: expected NCHAR got NUMBER
00932.00000 - "inconsistent datatypes: expected% s got% s"
* Cause:
* Action:
Error in line: 54, column: 43

子查询返回学生性别的字母"f“或"m”，这是可行的，但问题是如何将该字母分配给变量，其中genero_a =

SELECT NOMBRE, COALESCE(DIRECCION,CORREO, 10) comm
FROM ESTUDIANTE
WHERE GENERO_A =  
    (SELECT GENERO_A
    FROM ESTUDIANTE
    GROUP BY GENERO_A
    HAVING COUNT(GENERO_A) = 
        (SELECT MAX(COUNT(*)) as CONTAR
        FROM ESTUDIANTE
        GROUP BY GENERO_A))
ORDER BY NOMBRE;

我一点也不精通sql，我很感激你的理解。

-塔布拉河口

CREATE TABLE ESTUDIANTE(
    ID_LECTOR VARCHAR2(50) NOT NULL,
    NOMBRE VARCHAR2(50),
    APELLIDO VARCHAR2(50),
    GENERO_A CHAR(1) CONSTRAINT CH_GENERO_A CHECK(GENERO_A IN ('f', 'm')),
    DIRECCION NVARCHAR2(100),
    CORREO NVARCHAR2(50)
);
ALTER TABLE ESTUDIANTE ADD CONSTRAINT PK_ESTUDIANTE PRIMARY KEY(ID_LECTOR);

Answer 1

错误发生在COALESCE(DIRECCION,CORREO, 10)上，因为所有字段的数据类型都不相同。

解决方案：，您必须将NUMBER 10转换为方向和CORREO的类似数据类型。只需将上述代码更改为COALESCE(DIRECCION,CORREO, CAST(10 as NVARCHAR2(2)))即可。

CAST函数将值从一种数据类型转换为另一种数据类型。

更新的查询：

SELECT NOMBRE, COALESCE(DIRECCION,CORREO, CAST(10 as NVARCHAR2(2))) comm
FROM ESTUDIANTE
WHERE GENERO_A =  
    (SELECT GENERO_A
    FROM ESTUDIANTE
    GROUP BY GENERO_A
    HAVING COUNT(GENERO_A) = 
        (SELECT MAX(COUNT(*)) as CONTAR
        FROM ESTUDIANTE
        GROUP BY GENERO_A))
ORDER BY NOMBRE;

或者，从你提供的link，

SELECT NOMBRE, COALESCE(DIRECCION,CORREO, N'10') comm
FROM ESTUDIANTE
WHERE GENERO_A =   
   (SELECT GENERO_A
    FROM ESTUDIANTE
    GROUP BY GENERO_A
    HAVING COUNT(GENERO_A) = 
        (SELECT MAX(COUNT(*)) as CONTAR
        FROM ESTUDIANTE
        GROUP BY GENERO_A))
ORDER BY NOMBRE;

或,

SELECT NOMBRE, COALESCE(DIRECCION,CORREO, N''||10) comm
FROM ESTUDIANTE
WHERE GENERO_A =   
   (SELECT GENERO_A
    FROM ESTUDIANTE
    GROUP BY GENERO_A
    HAVING COUNT(GENERO_A) = 
        (SELECT MAX(COUNT(*)) as CONTAR
        FROM ESTUDIANTE
        GROUP BY GENERO_A))
ORDER BY NOMBRE;