Oracle SQL 函数

发布于 2024-10-03 07:05:58 字数 785 浏览 2 评论 0原文

我有一个表，其中有一列名为 Type，该列可以具有三个值（name1、name2、name3）。

我可以编写一个查询，首先返回 Type = name1 的记录，然后返回值为 name2 和 name3 的行> 带有 WHERE 子句，这样我就可以通过 CreationDate 来过滤它们？

这意味着，返回 01/01/2000 天：

row  'name1'
row  'name1'
(rest of the rows)

Id   Type    CreationDate  
1,  'name1', '2000/01/01'  
8,  'name1', '2000/01/01'  
18, 'name3', '2000/01/01'  
82, 'name2', '2000/01/01'  
11, 'name2', '2000/01/01'  
12, 'name3', '2000/01/01'  
2,  'name1', '2000/01/02'  
4,  'name1', '2000/01/02'  
98, 'name2', '2000/01/02'

每天，首先获取“name1”类型的记录，然后获取其余类型的记录，无顺序。

谢谢你！乌多.

原文

I have a table with a column called Type, which can have three values (name1, name2, name3).

Can I write a query which first returns the records with Type = name1 and then the rows with values name2 and name3 with a WHERE clause, so I can filter them by CreationDate for instance?

That means, return for day 01/01/2000:

row  'name1'
row  'name1'
(rest of the rows)

Id   Type    CreationDate  
1,  'name1', '2000/01/01'  
8,  'name1', '2000/01/01'  
18, 'name3', '2000/01/01'  
82, 'name2', '2000/01/01'  
11, 'name2', '2000/01/01'  
12, 'name3', '2000/01/01'  
2,  'name1', '2000/01/02'  
4,  'name1', '2000/01/02'  
98, 'name2', '2000/01/02'

For every day, get records of type 'name1' first and then the rest of the types with just no order.

Thank you! Udo.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情场扛把子 2024-10-10 07:05:58

您可以按创建日期订购，然后输入。如果您需要定义类型的顺序，可以使用案例。

SELECT id, Type, CreationDate
FROM  "table"
ORDER BY CreationDate ASC,
         CASE WHEN Type = 'name1' THEN 1
              ELSE 2
         END ASC

You can order by create date then type. If you need to define the order of the type you can use a case.

SELECT id, Type, CreationDate
FROM  "table"
ORDER BY CreationDate ASC,
         CASE WHEN Type = 'name1' THEN 1
              ELSE 2
         END ASC

回复收藏 0 原文

如果没有你 2024-10-10 07:05:58

PMV 的上述答案应该可以正常工作，将其作为替代选项。

SELECT id, Type, CreationDate
FROM  "table" 
ORDER BY CreationDate, decode(Type , 'name1', 0, 1), Type ASC

PMV's answer above should work fine Putting this in just as an alternate option.

SELECT id, Type, CreationDate
FROM  "table" 
ORDER BY CreationDate, decode(Type , 'name1', 0, 1), Type ASC

回复收藏 0 原文

流云如水 2024-10-10 07:05:58

听起来您可能只想根据 Date 和 type='name1' 确定排序的优先级，并且特别需要剩余的顺序本质上是随机的。如果是这种情况，您可以将 dbms_random.value 添加为 PMV 或 Insane 查询中 ORDER BY 中的第三项。示例表和示例如下：

create table Table1 (Id Number(2), Type Varchar2(5), CreationDate Date);
insert into Table1 Values ( 1,'name1',to_date('01/01/2000','MM/DD/YYYY'));
insert into Table1 Values ( 8,'name1',to_date('01/01/2000','MM/DD/YYYY'));
insert into Table1 Values (18,'name3',to_date('01/01/2000','MM/DD/YYYY'));
insert into Table1 Values (82,'name2',to_date('01/01/2000','MM/DD/YYYY'));
insert into Table1 Values (11,'name2',to_date('01/01/2000','MM/DD/YYYY'));
insert into Table1 Values (12,'name3',to_date('01/01/2000','MM/DD/YYYY'));
insert into Table1 Values ( 2,'name1',to_date('01/02/2000','MM/DD/YYYY'));
insert into Table1 Values ( 4,'name1',to_date('01/02/2000','MM/DD/YYYY'));
insert into Table1 Values (98,'name2',to_date('01/02/2000','MM/DD/YYYY'));

SELECT Id, Type, CreationDate FROM Table1
ORDER BY CreationDate, decode(Type , 'name1', 0, 1) ASC, dbms_random.value;

It sounds like you might want to prioritize the ordering only based on Date and type='name1' and specifically need the remaining order to be random in nature. If this is the case you can add dbms_random.value as the third item in the ORDER BY in either PMV or Insane's query. Sample table and example follow:

create table Table1 (Id Number(2), Type Varchar2(5), CreationDate Date);
insert into Table1 Values ( 1,'name1',to_date('01/01/2000','MM/DD/YYYY'));
insert into Table1 Values ( 8,'name1',to_date('01/01/2000','MM/DD/YYYY'));
insert into Table1 Values (18,'name3',to_date('01/01/2000','MM/DD/YYYY'));
insert into Table1 Values (82,'name2',to_date('01/01/2000','MM/DD/YYYY'));
insert into Table1 Values (11,'name2',to_date('01/01/2000','MM/DD/YYYY'));
insert into Table1 Values (12,'name3',to_date('01/01/2000','MM/DD/YYYY'));
insert into Table1 Values ( 2,'name1',to_date('01/02/2000','MM/DD/YYYY'));
insert into Table1 Values ( 4,'name1',to_date('01/02/2000','MM/DD/YYYY'));
insert into Table1 Values (98,'name2',to_date('01/02/2000','MM/DD/YYYY'));

SELECT Id, Type, CreationDate FROM Table1
ORDER BY CreationDate, decode(Type , 'name1', 0, 1) ASC, dbms_random.value;

回复收藏 0 原文

~没有更多了~