表结构如下(Oracle和MySQL结构相同):CREATE TABLE "E_POPEDOMS" (
"POPEDOM_ID" VARCHAR2(32) NOT NULL,
"POPEDOM_NAME" VARCHAR2(40),
"POPEDOM_TYPE_ID" VARCHAR2(32),
"POPEDOM_DESC" VARCHAR2(200),
"POPEDOM_PROP" VARCHAR2(1000),
"POPEDOM_PIC" VARCHAR2(40),
"DOWN_PIC" VARCHAR2(40),
"POPEDOM_SIGN" VARCHAR2(1),
CONSTRAINT PK_E_POPEDOMS PRIMARY KEY ("POPEDOM_ID")
);CREATE TABLE "E_POPEDOM_TYPES" (
"POPEDOM_TYPE_ID" VARCHAR2(32) NOT NULL,
"POPEDOM_TYPE_NAME" VARCHAR2(40),
"POPEDOM_TYPE_DESC" VARCHAR2(200),
CONSTRAINT PK_E_POPEDOM_TYPES PRIMARY KEY ("POPEDOM_TYPE_ID")
);两表关联 POPEDOM_TYPE_IDMySQL下语句:
select b.popedom_type_id, b.popedom_type_name, a.popedom_name
from e_popedoms a
left join e_popedom_types b on b.popedom_type_id = a.popedom_type_id
group by b.popedom_type_idMySQL下查询结果:
popedom_type01 系统管理 新增用户
popedom_type02 题库管理 新增试题期望从Oracle中查询到同样结果,但是上述查询语句在Oracle中Group By出错,求解
"POPEDOM_ID" VARCHAR2(32) NOT NULL,
"POPEDOM_NAME" VARCHAR2(40),
"POPEDOM_TYPE_ID" VARCHAR2(32),
"POPEDOM_DESC" VARCHAR2(200),
"POPEDOM_PROP" VARCHAR2(1000),
"POPEDOM_PIC" VARCHAR2(40),
"DOWN_PIC" VARCHAR2(40),
"POPEDOM_SIGN" VARCHAR2(1),
CONSTRAINT PK_E_POPEDOMS PRIMARY KEY ("POPEDOM_ID")
);CREATE TABLE "E_POPEDOM_TYPES" (
"POPEDOM_TYPE_ID" VARCHAR2(32) NOT NULL,
"POPEDOM_TYPE_NAME" VARCHAR2(40),
"POPEDOM_TYPE_DESC" VARCHAR2(200),
CONSTRAINT PK_E_POPEDOM_TYPES PRIMARY KEY ("POPEDOM_TYPE_ID")
);两表关联 POPEDOM_TYPE_IDMySQL下语句:
select b.popedom_type_id, b.popedom_type_name, a.popedom_name
from e_popedoms a
left join e_popedom_types b on b.popedom_type_id = a.popedom_type_id
group by b.popedom_type_idMySQL下查询结果:
popedom_type01 系统管理 新增用户
popedom_type02 题库管理 新增试题期望从Oracle中查询到同样结果,但是上述查询语句在Oracle中Group By出错,求解
INSERT INTO e_popedom_types VALUES ('popedom_type02','题库管理','题库管理');
INSERT INTO e_popedom_types VALUES ('popedom_type03','出卷管理','出卷管理');
INSERT INTO e_popedom_types VALUES ('popedom_type04','考试管理','考试管理');
INSERT INTO e_popedom_types VALUES ('popedom_type05','综合查询','综合查询');
INSERT INTO e_popedoms VALUES ('popedom0001','新增用户','popedom_type01','新增用户','index1.htm','./images/js13.jpg','./images/js13.jpg',NULL);
INSERT INTO e_popedoms VALUES ('popedom0002','修改用户','popedom_type01','修改用户','index2.htm','./images/js13.jpg','./images/js13.jpg',NULL);
INSERT INTO e_popedoms VALUES ('popedom0003','新增试题','popedom_type02','新增试题','addQuestion.jsp','./images/js13.jpg','./images/js13.jpg',NULL);
2 from e_popedoms a
3 left join e_popedom_types b on b.popedom_type_id = a.popedom_type_id
4 /POPEDOM_TYPE_ID POPEDOM_TYPE_NA POPEDOM_NAME
--------------- --------------- ---------------
popedom_type01 系统管理 修改用户
popedom_type01 系统管理 新增用户
popedom_type02 题库管理 新增试题不敢说其他的,但是楼主尝试一下提供的语句在mysql里面可以执行么,最起码我尝试是不可以的
是不是多写了一句group by
popedom_type02 题库管理 新增试题
我执行的是这个结果,当然,这是在8.1.7中执行的,817里没有left join这样的语法,要在9i以后才行,你换用817的语法重写吧。
from e_popedoms a
left join e_popedom_types b on b.popedom_type_id = a.popedom_type_id
group by b.popedom_type_id,b.popedom_type_name,a.popedom_name;
那你就要写全了,Oracle语法比较严格。