sql语句实现一个输入框下多条件查询
  1.    select oh.*,s.StatusName,st.Status,ps.StatusName AS PayStatusName from orderheader oh 
  2. LEFT JOIN orderdetail od on od.OrderID=oh.OrderID 
  3. LEFT JOIN customer c on oh.CustomerID = c.CustomerID
  4. LEFT JOIN (SELECT StatusName,StatusCode from `status` where StatusType='OrderStatus') as  s on s.StatusCode=oh.OrderStatus
  5.                LEFT JOIN (SELECT StatusName,StatusCode from `status` where StatusType='PayStatus') as  ps on ps.StatusCode=oh.PayStatus
  6. lEFT JOIN(select Status,OrderID from troublecase) as st on st.OrderID=oh.OrderID
  7. where 1=1
  8. <if test="pd.StartDate!=null and pd.StartDate!=''"  >
  9. and DATE_FORMAT(OrderDate,'%Y-%m-%d')>=DATE_FORMAT(#{pd.StartDate},'%Y-%m-%d')
  10. </if>
  11. <if test="pd.EndDate!=null and pd.EndDate!=''">
  12. and DATE_FORMAT(#{pd.EndDate},'%Y-%m-%d')>= DATE_FORMAT(OrderDate,'%Y-%m-%d')
  13. </if>
  14. <if test="pd.Customer!=null and pd.Customer!=''" > and (
  15. c.LoginID like CONCAT('%',#{pd.Customer},'%') or c.Email like CONCAT('%',#{pd.Customer},'%') or oh.Email like CONCAT('%',#{pd.Customer},'%')
  16. )</if>
  17. <if test="pd.ItemName!=null and pd.ItemName!=''"> and ItemName like CONCAT('%',#{pd.ItemName},'%')</if>

  18. <if test="pd.OrderNumber!=null and pd.OrderNumber!=''"> and (
  19. OrderNumber like CONCAT('%',#{pd.OrderNumber},'%') or RefOrderNumber like CONCAT('%',#{pd.OrderNumber},'%')
  20. )</if>
  21. <if test="pd.OrderStatus == null or pd.OrderStatus==''"> and oh.OrderStatus != 'Deleted'</if>
  22. <if test="pd.OrderStatus!=null and pd.OrderStatus!=''"> and OrderStatus=#{pd.OrderStatus}</if>
  23. <if test="pd.PayStatus!=null and pd.PayStatus!=''"> and PayStatus=#{pd.PayStatus}</if>
  24. <if test="pd.WareHouseId!=null and pd.WareHouseId!=''"> and WarehouseID=#{pd.WareHouseId}</if>
  25. <if test="pd.SalesMarket!=null and pd.SalesMarket!=''"> and SaleMarketID=#{pd.SalesMarket}</if>
  26. <if test="pd.StorageBarCode!=null and pd.StorageBarCode!=''">and StorageBarCode =#{pd.StorageBarCode}</if>
  27. <if test="pd.WareHouseID!=null and pd.WareHouseID!=''">and wareHouseId =#{pd.WareHouseID}</if>
  28. group by oh.OrderID
  29. order by DATE_FORMAT(oh.OrderDate, '%Y-%m-%d') desc,oh.CreatedDate DESC 
阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/zhao13083837081/article/details/52352049
个人分类: sql语句
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

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

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭