SQL Oracle-每列仅显示一次值 [英] SQL Oracle - display values only once per column

问题描述

我正在尝试格式化选择语句.作业指定必须采用这种方式进行格式化.

I'm trying to format a select statement. The assignment specifies that it has to be formatted this way.

我有一个有关出租车服务的数据库.我必须将视图与公司名称，乘客姓名和出租车号码放在一起.简单.但是，输出指定公司名称应仅在输出中出现一次，位于其自身组的顶部.所以我有:

I have a database regarding a taxi service. I have to put together a view with the company name, passenger name, and taxi number. Easy. However, the output specifies that the company name should only appear once in the output, at the top of it's own group. So I have:

CREATE VIEW TAXITRIPS(COMPANYNAME, PASSENGERNAME, TAXI#) AS
(SELECT COMPANY.NAME, BOOKING.NAME, VEHICLES.TAXI#
FROM BOOKING JOIN VEHICLES ON BOOKING.TAXI# = VEHICLES.TAXI#
RIGHT OUTER JOIN COMPANY ON VEHICLES.NAME = COMPANY.NAME);

正确的外部联接是这样，因此仍显示未记录预订的公司.如果我现在运行:

The right outer join is so that companies with no booking recorded are still displayed. If I now run:

SELECT * FROM TAXITRIPS ORDER BY COMPANYNAME ASC;

它会给我类似的东西

COMPANYNAME          PASSENGERNAME      TAXI#
---------------------------------------------
ABC TAXIS            DAVE                192
LEGION CABS
PREMIER CABS         SHANE               2154
PREMIER CABS         TIM                 2169
SILVER SERVICE       DAVE                18579
SILVER SERVICE       TIM                 18124
SILVER SERVICE       AARON               18917

军用出租车无结果，所有字段均已显示，等等.作业规范说必须看起来像这样.

No result for legion cabs, all field displayed, et cetera. Assignment specification says it has to look like this.

COMPANYNAME          PASSENGERNAME      TAXI#
---------------------------------------------
ABC TAXIS            DAVE                192
LEGION CABS
PREMIER CABS         SHANE               2154
                     TIM                 2169
SILVER SERVICE       DAVE                18579
                     TIM                 18124
                     AARON               18917

公司名称只能显示在其第一行. DISTINCT没有帮助.有什么建议吗?

The company name should only be displayed on its first row. DISTINCT is not helping. Any advice?

推荐答案

通常，您将在应用程序层执行此操作，因为结果集取决于行的顺序，这在SQL中是一件很糟糕的事情.

Normally, you would do this at the application layer, because the result set relies on the ordering of the rows -- a bad thing in SQL.

但是您可以按照以下方式进行操作:

But you can do it as:

SELECT (CASE WHEN ROW_NUMBER() OVER (PARTITION BY c.NAME ORDER BY v.TAXI#) = 1
             THEN c.NAME
        END) as CompanyName, b.NAME, v.TAXI#
FROM COMPANY c LEFT JOIN
     VEHICLES v
     ON v.NAME = c.NAME LEFT JOIN
     BOOKING b 
     ON b.TAXI# = v.FLIGHT#
ORDER BY c.name, v.taxi#;

注意:我将join重新排列为LEFT JOIN.大多数人发现它比RIGHT JOIN更容易理解.

Note: I rearranged the joins to be LEFT JOINs. Most people find that easier to follow than RIGHT JOINs.

这篇关于SQL Oracle-每列仅显示一次值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！