>我有一个看起来像这样的数据集
parent_id address_type
1 billing
1 shipping
8 billing
429 billing
429 shipping
429 vendor
如何选择那些address_type='运输'的行如果没有"运输"address_type请选择address_type="计费"
结果应如下所示:
parent_id address_type
1 shipping
8 billing
429 shipping
提前非常感谢
得出此结果的一种方法是使用UNION ALL
SELECT * FROM Table t
WHERE t.address_type = 'shipping'
UNION aLL
SELECT * FROM Table t
WHERE t.address_type = 'billing'
AND NOT EXISTS (SELECT 1 FROM Table t1
WHERE t.id = t1.id AND t1.address_type = 'shipping')
尝试这样的事情:
select parent_id, address_type
from YourTable
where address_type = 'shipping'
union all
select parent_id, address_type
from YourTable
where address_type = 'billing'
and parent_id not in (select parent_id, address_type
from YourTable
where address_type = 'shipping')
order by parent_id;