我有一个表products
,一个特定产品可能包含不同的模型,模型0代表基本模型:
Select *
From products
Where product_number = 12345;
结果:
id made_by product_number model
----------------------------------
122 U123 12345 0
123 U123 12345 1
124 U552 12345 2
现在,我只想在某些模型中只找到基本模型(模型= 0(made_by
值的不同之处?
在此示例中,我绝对有兴趣将此产品12345的结果。
我想到的是选择独特和数量,但也许有更好的方法?
存在是一种更好的方法,但是您可以使用self join
也可以像以下
Resterster样本
select t1.*
from products t1
inner join
products t2
on
t1.product_number=t2.product_number
and t1.made_by<>t2.made_by
and t1.model<>0
and t2.model=0;
样本数据
从产品中选择 *;
+-----+---------+----------------+-------+
| id | made_by | product_number | model |
+-----+---------+----------------+-------+
| 122 | U123 | 12345 | 0 |
| 123 | U123 | 12345 | 1 |
| 124 | U552 | 12345 | 2 |
| 125 | U5 | 12346 | 0 |
| 126 | U5 | 12346 | 1 |
| 127 | U6 | 12347 | 0 |
| 128 | U7 | 12347 | 1 |
+-----+---------+----------------+-------+
输出
+-----+---------+----------------+-------+
| id | made_by | product_number | model |
+-----+---------+----------------+-------+
| 124 | U552 | 12345 | 2 |
| 128 | U7 | 12347 | 1 |
+-----+---------+----------------+-------+
您可以使用exists
子查询检查相同product_number
的其他made_by
:
select product_number
from YourTable yt1
where yt1.model = 0
and exists
(
select *
from YourTable yt2
where yt1.product_number = yt2.product_number
and yt2.made_by <> yt1.made_by
)