我已经创建了一个Oracle SQL视图,如下所示:
John Strange | Carnaby Street, S9 8EU | CS5 Degree | Sociology
John Strange | Carnaby Street, S9 8EU | CS5 Degree | Religious Studies
John Strange | Carnaby Street, S9 8EU | CS5 Degree | Environmental Studies
John Strange | Carnaby Street, S9 8EU | CS5 Degree | Management
John Strange | Carnaby Street, S9 8EU | CS5 Degree | Business Intelligence
为了只显示姓名、地址和学位名称一次,您建议使用什么?我使用了DISTINCT,但它不起作用。
发布于 2018-06-05 04:22:23
为什么它不起作用,您只需排除其他列,因为distinct
将应用于您拥有express的所有列/表达式。
select distinct displayname, address, degree
from <view> v1;
例如,最后一列具有不同的值。因此,如果您将include in select
语句与distinct
一起使用,则它将不起作用。
发布于 2018-06-05 04:40:10
http://sqlfiddle.com/#!4/0c113/3/0
您可以按如下方式定义视图查询:
设置-
CREATE TABLE Table1
(Name varchar2(12), Address varchar2(22), Degree varchar2(10), Subject varchar2(21))
;
INSERT ALL
INTO Table1 (Name, Address, Degree, Subject)
VALUES ('John Strange', 'Carnaby Street, S9 8EU', 'CS5 Degree', 'Sociology')
INTO Table1 (Name, Address, Degree, Subject)
VALUES ('John Strange', 'Carnaby Street, S9 8EU', 'CS5 Degree', 'Religious Studies')
INTO Table1 (Name, Address, Degree, Subject)
VALUES ('John Strange', 'Carnaby Street, S9 8EU', 'CS5 Degree', 'Environmental Studies')
INTO Table1 (Name, Address, Degree, Subject)
VALUES ('John Strange', 'Carnaby Street, S9 8EU', 'CS5 Degree', 'Management')
INTO Table1 (Name, Address, Degree, Subject)
VALUES ('John Strange', 'Carnaby Street, S9 8EU', 'CS5 Degree', 'Business Intelligence')
SELECT * FROM dual
;
查询-
select name, address, degree, listagg(Subject,' , ') within group (order by subject) as Subjects
from Table1
group by name, address, degree
输出-
NAME ADDRESS DEGREE SUBJECTS
John Strange Carnaby Street, S9 8EU CS5 Degree Business Intelligence , Environmental Studies , Management , Religious Studies , Sociology
发布于 2018-06-05 04:39:42
或者,您可能希望显示值的列表,而不是删除第四列。您可以使用LISTAGG来实现这一点,但请记住,它有4000个字符的限制。
select full_name, address, program,
listagg(course, ', ') within group (order by course)
from my_view
group by full_name, address, program;
https://stackoverflow.com/questions/50688172
复制相似问题