Oracle选择查询优先考虑列值 [英] Oracle select query give priority to column value

问题描述

我在一个表Test中有三列

I have three columns in one table Test

name, type, region

样本值为:

john IT ny
john SALES ny
john FINANCE ny
lisa SALES ny
lisa FINANCE ny

在上述情况下，我该如何赋予IT优先级1，给SALES 2优先级以及给Finance 3优先级.

Out of the above condition how can i give priority 1 to IT, 2 to SALES and 3 to Finance.

对于上述示例记录，我应该给出2条记录

I mean for the above sample records it shuld give 2 records

John IT ny
Lisa SALES ny

如果员工记录与IT相关，则仅应显示，如果不显示SALES，则不应该显示财务.

If employee records are with IT than only those should show, if not than show SALES, if not than show Finance.

推荐答案

我建议添加一个表来存储TYPES及其关联的优先级-如果在表和类型表之间添加FOREIGN KEY约束，则您可以断言该类型始终具有优先级.

I would suggest adding a table to store TYPES and their associated priorities - if you add a FOREIGN KEY constraint between your table and the table of types then you can assert that the type always has a priority.

获得优先级的另一种方法是创建一个将类型作为输入并返回优先级的函数.它会提供与表类似的功能，但会更加模糊.

An alternate way of getting the priorities would be to create a function which takes a type as an input and returns a priority. It would give similar functionality as a table but is slightly more obfuscated.

除此之外-我同意@Ben的观点，认为解析函数是得出结果的最佳方法.

Other than that - I agree with @Ben that analytical functions are the best way to get the results out.

SQL提琴

Oracle 11g R2架构设置:

CREATE TABLE types (
  type VARCHAR2(20) PRIMARY KEY,
  priority NUMBER(3)
)
/

INSERT INTO types
          SELECT 'IT',        1 FROM DUAL
UNION ALL SELECT 'SALES',     2 FROM DUAL
UNION ALL SELECT 'FINANCE',   3 FROM DUAL
UNION ALL SELECT 'MARKETING', NULL FROM DUAL
/

CREATE TABLE minions (
  name   VARCHAR2(30),
  type   VARCHAR2(20),
  region VARCHAR2(2),
  FOREIGN KEY ( type ) REFERENCES types ( type )
)
/

INSERT INTO minions
          SELECT 'john',  'IT',        'ny' FROM DUAL
UNION ALL SELECT 'john',  'SALES',     'ny' FROM DUAL
UNION ALL SELECT 'john',  'FINANCE',   'ny' FROM DUAL
UNION ALL SELECT 'john',  'MARKETING', 'ny' FROM DUAL
UNION ALL SELECT 'kevin', 'MARKETING', 'ny' FROM DUAL
UNION ALL SELECT 'lisa',  'SALES',     'ny' FROM DUAL
UNION ALL SELECT 'lisa',  'FINANCE',   'ny' FROM DUAL
/

查询1 :

SELECT name,
       MAX( m.type ) KEEP ( DENSE_RANK FIRST ORDER BY priority ) AS type,
       MAX( region ) KEEP ( DENSE_RANK FIRST ORDER BY priority ) AS region
FROM   minions m
       INNER JOIN
       types t
       ON ( m.type = t.type )
WHERE  priority IS NOT NULL
GROUP BY name
ORDER BY name

结果 :

Results:

| NAME |  TYPE | REGION |
|------|-------|--------|
| john |    IT |     ny |
| lisa | SALES |     ny |

这篇关于Oracle选择查询优先考虑列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！