时间戳

首页 > 解决方案 > 甲骨文。应用条件排序。如果 >=2 行有重复数据，则这些行必须遵循与没有重复数据的行不同的条件

sql - 甲骨文。应用条件排序。如果 >=2 行有重复数据，则这些行必须遵循与没有重复数据的行不同的条件

问题描述

Select custno, custname, location
From customer
Order by

结果的顺序必须符合以下标准：

custno、custnames 和位置按位置升序排列。如果 2 行或更多行具有相同的位置，则仅按客户名升序列出这些行。

我什至不知道从问题的第二部分从哪里开始。我发现了一些与此类似的问题，建议将 case 与 when 一起使用，但我以前从未使用过，也不知道从什么开始。这是我到目前为止的位置

Select custno, custname, location
From customer
Order by
Case 
    when count(location) >= 2
    then custname asc
    else location asc
    end

这是他们提供的结果的图片。

以及提供的陈述。

create table GRADE (
gradeID varchar2(2),
description varchar2(20),
Primary Key (gradeID) );
create table TYPE (
TypeCode number,
Title varchar2(20),
gradeID varchar2(2),
category varchar2(1),
Primary Key (TypeCode),
Foreign Key (gradeID) references GRADE );
create table customer(
custno number,
custname varchar2(20),
typecode number,
sales number(7,2),
location varchar2(20),
Primary Key (custno),
Foreign Key (typecode) references TYPE );

Insert into grade (gradeID,description) values ('WC','Women and
Children');
Insert into grade (gradeID,description) values ('M','Men');
Insert into TYPE (TypeCode,Title,gradeID,category) values (1,'Under
20','WC','X');
Insert into TYPE (TypeCode,Title,gradeID,category) values (2,'Under
20','M','Z');
Insert into TYPE (TypeCode,Title,gradeID,category) values (3,'20
plus','WC','Z');
Insert into TYPE (TypeCode,Title,gradeID,category) values (4,'20
plus','M','X');
Insert into TYPE (TypeCode,Title,gradeID,category) values
(5,'Special','M','S');
Insert into customer (custno, custname, TypeCode,sales,location) values
(21,'Wendy',1,300,'Haw');
Insert into customer (custno, custname, TypeCode,sales,location) values
(24,'Sue',3,700,'Haw');
Insert into customer (custno, custname, TypeCode,sales,location) values
(27,'Nick',2,NULL,'Kew');
Insert into customer (custno, custname, TypeCode,sales,location) values
(22,'Dave',4,95,'Richmond');
Insert into customer (custno, custname, TypeCode,sales,location) values
(25,'Jenny',1,525,'Haw');
Insert into customer (custno, custname, TypeCode,sales,location) values
(29,'Bob',4,15,'Kew');
Insert into customer (custno, custname, TypeCode,sales,location) values
(23,'Helen',3,200,'Haw');
Insert into customer (custno, custname, TypeCode,sales,location) values
(28,'Linda',1,25,'Kew');
Insert into customer (custno, custname, TypeCode,sales,location) values
(26,'Xena',3,Null, 'Richmond');

标签： sqloracle

解决方案

从返回 nr 个相同行的查询开始，然后只选择 nr > 1 的行：

select ...
  from customer c,
       (
         Select count(*) nr, location
         From customer
         Group by location
       ) n
where n.nr > 1
  and c.l = n.l

这不是一个完整的查询，但你明白了。如果没有你的桌子，我很难做到这一点。

推荐阅读