关闭

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

1037人阅读 评论(0) 收藏 举报
分类:
  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 
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:68721次
    • 积分:1653
    • 等级:
    • 排名:千里之外
    • 原创:103篇
    • 转载:0篇
    • 译文:0篇
    • 评论:11条
    最新评论