sql语句实现一个输入框下多条件查询

来源：互联网发布：http传输数据大小编辑：程序博客网时间：2024/05/23 01:19

select oh.*,s.StatusName,st.Status,ps.StatusName AS PayStatusName from orderheader oh
LEFT JOIN orderdetail od on od.OrderID=oh.OrderID
LEFT JOIN customer c on oh.CustomerID = c.CustomerID
LEFT JOIN (SELECT StatusName,StatusCode from `status` where StatusType='OrderStatus') as s on s.StatusCode=oh.OrderStatus
LEFT JOIN (SELECT StatusName,StatusCode from `status` where StatusType='PayStatus') as ps on ps.StatusCode=oh.PayStatus
lEFT JOIN(select Status,OrderID from troublecase) as st on st.OrderID=oh.OrderID
where 1=1
<if test="pd.StartDate!=null and pd.StartDate!=''" >
and DATE_FORMAT(OrderDate,'%Y-%m-%d')>=DATE_FORMAT(#{pd.StartDate},'%Y-%m-%d')
</if>
<if test="pd.EndDate!=null and pd.EndDate!=''">
and DATE_FORMAT(#{pd.EndDate},'%Y-%m-%d')>= DATE_FORMAT(OrderDate,'%Y-%m-%d')
</if>
<if test="pd.Customer!=null and pd.Customer!=''" > and (
c.LoginID like CONCAT('%',#{pd.Customer},'%') or c.Email like CONCAT('%',#{pd.Customer},'%') or oh.Email like CONCAT('%',#{pd.Customer},'%')
)</if>
<if test="pd.ItemName!=null and pd.ItemName!=''"> and ItemName like CONCAT('%',#{pd.ItemName},'%')</if>
<if test="pd.OrderNumber!=null and pd.OrderNumber!=''"> and (
OrderNumber like CONCAT('%',#{pd.OrderNumber},'%') or RefOrderNumber like CONCAT('%',#{pd.OrderNumber},'%')
)</if>
<if test="pd.OrderStatus == null or pd.OrderStatus==''"> and oh.OrderStatus != 'Deleted'</if>
<if test="pd.OrderStatus!=null and pd.OrderStatus!=''"> and OrderStatus=#{pd.OrderStatus}</if>
<if test="pd.PayStatus!=null and pd.PayStatus!=''"> and PayStatus=#{pd.PayStatus}</if>
<if test="pd.WareHouseId!=null and pd.WareHouseId!=''"> and WarehouseID=#{pd.WareHouseId}</if>
<if test="pd.SalesMarket!=null and pd.SalesMarket!=''"> and SaleMarketID=#{pd.SalesMarket}</if>
<if test="pd.StorageBarCode!=null and pd.StorageBarCode!=''">and StorageBarCode =#{pd.StorageBarCode}</if>
<if test="pd.WareHouseID!=null and pd.WareHouseID!=''">and wareHouseId =#{pd.WareHouseID}</if>
group by oh.OrderID
order by DATE_FORMAT(oh.OrderDate, '%Y-%m-%d') desc,oh.CreatedDate DESC

0 0