我試圖將 3 個表連接在一起，它作業得很好但是當我嘗試按某個列對它們進行分組時，它顯示我不是按運算式分組：
我的桌子：
create table userss (
username varchar2(25)not null ,
password varchar2(25) ,
userTypeID number(8) not null,
gender char(1),
dateCreated date,
lname varchar2(10),
fname varchar2(10),
constraint username1_pk primary key (username),
CONSTRAINT gender1_ck CHECK (gender='M' or gender='F'),
constraint userTypeID_fk foreign key(userTypeID) references userType (userTypeId)
);
create table doctor (
docID number(8) not null ,
docBioData varchar2(50),
username varchar2(25)not null ,
SpecID number(3),
post_id number(5) not null ,
constraint docID_pk primary key(docID),
constraint username4_fk foreign key (username) references userss(username),
constraint specID2_fk foreign key (specID) references speciality(specID),
constraint post_id_fk foreign key (post_id) references positions(post_id)
);
create table rating (
ratID number(10) not null ,
rat_date date,
rat_comment varchar2(100),
rat_rating number(1) not null,
docID number(8) not null ,
patID number(8) not null,
constraint ratID_pk primary key(ratID),
constraint docID_fk foreign key (docID) references doctor(docID),
constraint patID2_fk foreign key (patID) references patient(patID),
constraint rating_ck check (rat_rating between 1 and 5)
);
我的代碼是：
select d.docid, (fname|| ' '|| lname) "Doctor Name", count(r.rat_rating), avg(r.rat_rating)
from userss u, doctor d, rating r
where u.username = d.username and d.docid = r.docid and rat_date > TO_DATE('01-JAN-2020', 'DD-MON-YYYY')
group by d.docid
order by d.docid desc;
uj5u.com熱心網友回復：
在您的和SELECT之前有 2 列，因此您必須使用這 2 列。根據您的版本，您不能按別名分組，您需要封裝您的第一個查詢。COUNTAVGGROUP BY
select id, Doctor_Name, COUNT(rat_rating), AVG(rat_rating) from (
select d.docid AS id, (fname|| ' '|| lname) AS Doctor_Name, r.rat_rating, r.rat_rating
from userss u, doctor d, rating r
where u.username = d.username and d.docid = r.docid and rat_date > TO_DATE('01-JAN-2020', 'DD-MON-YYYY')
) A
group by id, Doctor_Name
order by id desc
另一方面，您可以從查詢中洗掉“醫生姓名”列，它應該可以作業
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/462605.html
上一篇：如何通過單擊來切換div屬性？