我的表格中有两种类型的交易1)One way relocation deal and 2)Regular deal .我的数据库-车辆中有一个表。
vehicles table
=====================================================
id, pickuplocation, returnlocation, included location
现在，当用户选择One way relocation deal然后在 db 中时，只会插入id,pickuplocation,returnlocation，并且列included location将为空。当用户选择Regular deal时，列pickuplocation,returnlocation将为空，只有id and included location将设置的列。问题是我想使用它们的位置对整个数据进行排序。我试过这个查询——
SELECT *
FROM vehicles
where 1=1
ORDER BY pickuplocation ASC,returnlocation ASC
但它不会给出正确的结果，因为某些数据在包含的位置中具有 null。我如何对所有pickuplocation,returnlocation,exculded location进行排序
你想要IFNULL函数
尝试按排序方式
IFNULL(returnlocation, includedlocation)
得到了解决方案。我使用了这个查询
(SELECT id,pickuplocation,returnlocation,deal_type FROM `vehicles` where deal_type = 1)
UNION
(SELECT id,included_location,excluded_location,deal_type FROM `vehicles` where deal_type = 2)
order by pickuplocation,returnlocation
我希望这将帮助将来可能遇到类似问题的人。
SELECT *
FROM vehicles
where 1=1 AND ($condition)
ORDER BY pickuplocation ASC,returnlocation ASC
假设$condition会像field01=1或field2 >=1