问题描述
我有一个名为Order的表和另一个名为Order Detail的表。订单可以有一个
或多个订单明细
表格的DDL和一些数据作为Bellow:
create table commande( -- Order Table
id int,
libelle varchar(50)
)
insert into commande values(1,'Cmd1'),(2,'Cmd2'),(3,'Cmd3'),(4,'Cmd4'),(5,'Cmd5'),(6,'Cmd6'),
(7,'Cmd7'),(8,'Cmd7')
create table detailCommande( - 订单明细表
id_detailCommande int,
id_commande int,
类别
)
插入detailCommande值
(10,1,1012),
(11,1,11),(15, 2,1012),(12,3,1013),(13,3,11),(14,4,999 9),(15,4,11),(16,6,1012),(17,7,1012),(18,5,11),(19,5,9999),(20,5,1012)
(7,'Cmd7'),(8,'Cmd7')create table detailCommande( -- Order Detail Tableid_detailCommande int,id_commande int,categorie int)insert into detailCommande values(10,1,1012),(11,1,11),(15,2,1012),(12,3,1013),(13,3,11),(14,4,9999),(15,4,11),(16,6,1012),(17,7,1012),(18,5,11),(19,5,9999),(20,5,1012)
我想选择所有包含类别11的订单,但它是 至少包含订单明细 值为 1012或1013。
I want to select all order that have a categorie 11 but it's contain at least on Order Detail with value 1012 or 1013 .
我想要一个像下面这样的结果:
I want a result like as bellow :
id libelle id_detailCommande id_commande categorie
1 Cmd1 10 1 1012
1 Cmd1 11 1 11
2 Cmd2 15 2 1012
3 Cmd3 12 3 1013
3 Cmd3 13 3 11
6 Cmd6 16 6 1012
7 Cmd7 17 7 1012
5 Cmd5 18 5 11
5 Cmd5 20 5 1012
我尝试了下面的查询,它给我正确的结果只是性能很差
当我使用Exists和查询需要更多时间因为表很大。当我删除存在查询快速工作
select * from commande
inner join detailCommande on id=id_commande
where (categorie in(1012,1013)
or categorie=11 and exists(select 1 from detailCommande a where a.id_commande=id and categorie in(1012,1013))
)
有什么建议吗?
我怎么写同样的查询方式不同?
How Can i write the same query differently ?
感谢您的帮助
推荐答案
select * from commande
inner join detailCommande on id=id_commande
where ( (categorie in(1012,1013) or categorie=11 )
and exists(select 1 from detailCommande a where a.id_commande=id and categorie in(1012,1013))
)
这篇关于使用特定过滤器返回数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!