当我有条件时,获取下一个结果 [英] Get the next result when i have a condition
本文介绍了当我有条件时,获取下一个结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个名为Order的表和另一个名为Store的表。
表的DDL和一些数据作为Bellow:
创建表commande( - 订单表
id int,
libelle varchar(50),
dateC datetime,
store int
)
插入commande值( 1,'Cmd1','2019-06-01 21:00:00.000',1)
创建表存储(
id int,
dateopen datetime,
is_op int
)
插入商店值(1,'2019-06-01 08:00:00.000',0)
插入商店值(1,' 2019-06-01 09:00:00.000',0)
插入商店值(1,'2019-06-01 10:00:00.000',1)
插入商店值(1) ,'2019-06-01 11:00:00.000',1)
插入商店值(1,'2019-06-01 19:00:00.000',0)
插入商店值(1,'2019-06-01 20:00:00.000',0)
插入商店值(1,'2019-06-01 21:00:00.000',0)
insert into商店值(1,'2019-06-02 08:00:00.000',0)
插入商店值(1,'2019-06-02 09:00:00.000',0)
插入商店值(1,'2019-06-02 10:00:00.000',1)
在我的情况下,我使用加入usin g id和日期如下文
select * from commande c
内部联接商店s
在c.store = s上。 id和dateopen = dateC
我想要的是 is_OP = 0 我想从表商店中选择下一个日期is_Op = 1,在我的情况下是
2019-06-02 10:00:00.000
我想要一个像bellow一样的结果
id libelle dateC store id dateopen is_op DateOpeFromStore
1 Cmd1 2019-06-01 21:00:00.000 1 1 2019-06-01 21:00:00.000 0 2019-06-02 10:00:00.000
我该怎么做?
谢谢
解决方案
您好YassirCool,
您可以参考以下查询查看是否有效
< pre class ="prettyprint">选择s.id,c.libelle,c.dateC,c.store,c.id,c.dateC作为dateopen,min(s.dateopen)over(按s.id顺序分区s .dateopen)作为DateOpeFromStore,来自商店的s.is_op在s.id = c.id上加入commande c其中datec< dateopen和is_op = 1
Best此致,以
Zoe Zhi
Hi,
I have a table called Order and another table called Store .
The DDL of the tables and with some Data as Bellow :
create table commande( -- Order Table id int, libelle varchar(50), dateC datetime, store int ) insert into commande values(1,'Cmd1','2019-06-01 21:00:00.000',1) create table store( id int, dateopen datetime, is_op int ) insert into store values(1,'2019-06-01 08:00:00.000',0) insert into store values(1,'2019-06-01 09:00:00.000',0) insert into store values(1,'2019-06-01 10:00:00.000',1) insert into store values(1,'2019-06-01 11:00:00.000',1) insert into store values(1,'2019-06-01 19:00:00.000',0) insert into store values(1,'2019-06-01 20:00:00.000',0) insert into store values(1,'2019-06-01 21:00:00.000',0) insert into store values(1,'2019-06-02 08:00:00.000',0) insert into store values(1,'2019-06-02 09:00:00.000',0) insert into store values(1,'2019-06-02 10:00:00.000',1)
In my case i use a join using id and date like as bellow
select * from commande c inner join store s on c.store=s.id and dateopen=dateC
What i want is when is_OP=0 i want select the next date from the table store where is_Op=1, in my case is
2019-06-02 10:00:00.000
I want a result like a bellow
id libelle dateC store id dateopen is_op DateOpeFromStore 1 Cmd1 2019-06-01 21:00:00.000 1 1 2019-06-01 21:00:00.000 0 2019-06-02 10:00:00.000
How can i do that ?
Thanks
解决方案
Hi YassirCool,
You could refer to below query to see whether it work or not
select s.id,c.libelle,c.dateC,c.store,c.id,c.dateC as dateopen ,min(s.dateopen) over (partition by s.id order by s.dateopen ) as DateOpeFromStore,s.is_op from store s join commande c on s.id=c.id where datec<dateopen and is_op=1Best Regards,
Zoe Zhi
这篇关于当我有条件时,获取下一个结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文