thinkphp+mysql 完成多条件排序

福尔摩斯对我笑 2019-02-22 2939 次 14037条

需求:将数据按照以下规则进行自动排序

需求分解:

  • 由于数据库中存储的日期为时间戳,所以需要先将时间戳转为日期形式,要用到两个函数

    DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。 date 参数是合法的日期;format 参数用来规定日期的显示格式

    DATE_FORMAT(NOW(), "%Y-%m-%d") // 得出当前的日期 eg: 2019-09-22
    

    FROM_UNIXTIME(unix_timestamp,format) //将MYSQL中以INT(11)存储的时间以"YYYY-MM-DD"格式来显示,也就是可以将时间戳转为日期 unix_timestamp 参数 是时间戳;format 参数规定日期的格式

    FROM_UNIXTIME("1550799985", %Y-%m-%d") //得出 2019-02-22
    
  • 求出正在召开的会议

SELECT
   *
FROM
   m_meeting
WHERE
   date_format(
   	from_unixtime(start_time),
   	'%Y-%m-%d'
   ) <= date_format(now(), '%Y-%m-%d')
AND date_format(
   from_unixtime(end_time),
   '%Y-%m-%d'
) >= date_format(now(), '%Y-%m-%d')
ORDER BY
   start_time ASC
  • 未召开的会议
SELECT
	*
FROM
	m_meeting
WHERE
	date_format(now(), '%Y-%m-%d') < date_format(
		from_unixtime(start_time),
		'%Y-%m-%d'
	)
ORDER BY
	start_time ASC
  • 已召开的会议
SELECT
	*
FROM
	m_meeting
WHERE
	date_format(now(), '%Y-%m-%d') > date_format(
		from_unixtime(start_time),
		'%Y-%m-%d'
	)
ORDER BY
	start_time DESC

需求合并 将查询条件组合为sql

SELECT
	*
FROM
	m_meeting
ORDER BY
	date_format(
		from_unixtime(start_time),
		'%Y-%m-%d'
	) >= date_format(now(), '%Y-%m-%d')
AND date_format(
	from_unixtime(end_time),
	'%Y-%m-%d'
) <= date_format(now(), '%Y-%m-%d') DESC,
 date_format(now(), '%Y-%m-%d') < date_format(
	from_unixtime(start_time),
	'%Y-%m-%d'
) DESC,
 date_format(now(), '%Y-%m-%d') > date_format(
	from_unixtime(start_time),
	'%Y-%m-%d'
) DESC
标签: 无

非特殊说明,本博所有文章均为博主原创。

头像也是LOGO
未来博客 博主大人
到了这个年纪,不应该再彷徨!

发表评论



 


已有 14037 条评论 1


  1. redmi service center in kannur

    This website was… how do you say it? Relevant!!
    Finally I’ve found something which helped me. Cheers!

    回复 2020年2月26日
  2. wayback machine downloader free

    Hello there! This is my 1st comment here so I just wanted to give a quick shout out and say I truly enjoy reading through your
    articles. Can you suggest any other blogs/websites/forums that go over the same subjects?
    Thank you so much!

    回复 2020年2月26日
  3. sky city casino entertainment

    If some one needs to be updated with latest technologies therefore he must be go to see
    this site and be up to date all the time.

    回复 2020年2月26日
  4. Cork Bags

    First of all I would like to say excellent blog! I had a quick question which I’d like to ask if you don’t
    mind. I was curious to know how you center yourself and clear your thoughts prior
    to writing. I’ve had a difficult time clearing my
    thoughts in getting my ideas out there. I truly do enjoy writing but it
    just seems like the first 10 to 15 minutes are lost just trying to figure out how
    to begin. Any ideas or tips? Appreciate it!

    回复 2020年2月26日
  5. shoe suitcase

    What’s up, all the time i used to check website posts
    here in the early hours in the dawn, because i like to gain knowledge
    of more and more.

    回复 2020年2月26日
  6. 우리카지노

    So by adding all these you’ll be able to add an enchantment to your house solely.
    Other issues that you simply may give are the double doorways, cylindrical locks
    or burglar alarm. http://www.bonniesdelights.com/index.php?a=stats&u=connieliriano

    回复 2020年2月26日
  7. 우리카지노

    So by adding all these you’ll be able to add an enchantment to your house solely.

    Other issues that you simply may give are the double doorways, cylindrical locks or
    burglar alarm. http://www.bonniesdelights.com/index.php?a=stats&u=connieliriano

    回复 2020年2月26日
  8. share text and code

    I am not sure the place you’re getting your info,
    but great topic. I must spend a while finding out much more or figuring out more.
    Thanks for fantastic information I used to be in search of this info for my mission.

    回复 2020年2月26日
  9. buy used bmw parts

    Car REPAIR AND SERVICE CENTERES IN SHARJAH.

    回复 2020年2月26日
  10. live roulette vs online roulette

    Very quickly this website will be famous among all blog users, due
    to it’s good articles or reviews

    回复 2020年2月26日
  11. จีคลับ

    Awesome! Its really awesome paragraph, I have got much clear idea regarding from this piece of writing.

    回复 2020年2月26日
  12. SEO Services in MD

    After checking out a handful of the blog posts on your website, I truly like your way of writing a blog. I saved as a favorite it to my bookmark site list and will be checking back in the near future. Please visit my website too and tell me how you feel.

    回复 2020年2月26日
  13. Royal Online V2

    Greetings! Very helpful advice in this particular article!
    It is the little changes that will make the most significant changes.
    Thanks a lot for sharing!

    回复 2020年2月26日
  14. Free Robux

    Thank you for some other great post. The place else may just anyone get that type of information in such a perfect means of writing?

    I have a presentation next week, and I’m on the look for such info.

    回复 2020年2月26日
  15. 포월드

    선물옵션,대여계좌,포월드, 해외선물, 대여계좌 임대, 선물옵션커뮤니티
    Ref. Descriptions : 선물옵션,대여계좌,포월드, 해외선물, 대여계좌 임대, 선물옵션커뮤니티 빌리입니다.

    안전하고 신속한 선물계좌, 국내선물,
    해외선물 비대면계좌 신청을 원하시는 분은 선물 커뮨티의 달인 빌리 선물옵션 커뮤니티를 찾아주세요.

    回复 2020年2月26日
  16. xxx free porn movies

    I know this if off topic but I’m looking into starting my own weblog and was wondering what all is required
    to get set up? I’m assuming having a blog like yours would cost a
    pretty penny? I’m not very web smart so I’m not 100% certain. Any tips or advice would be greatly appreciated.
    Thanks

    回复 2020年2月26日
  17. Baltimore MD SEO

    Aw, this was an extremely nice post. Finding the time and actual effort to produce a superb article… but what can I say… I procrastinate a lot and don’t manage to get nearly anything done.

    回复 2020年2月26日
  18. fintech news

    What’s Going down i am new to this, I stumbled upon this
    I have found It positively helpful and it has helped me out loads.
    I hope to contribute & assist different customers like its helped me.
    Good job.

    回复 2020年2月26日
  19. Tramadol

    whoah this weblog is excellent i like reading your posts.

    Stay up the good work! You know, many people are looking around
    for this info, you could aid them greatly.

    回复 2020年2月26日
  20. click here

    I don’t even know how I stopped up here, but I thought this publish was once great.
    I do not know who you are but certainly you’re going to a famous
    blogger should you are not already. Cheers!

    回复 2020年2月26日
  21. pokerqq

    I ɡօ to see everyday sߋme blogs and blogs to read content,
    excеpt thіs blog ρrovides feature based posts.

    回复 2020年2月26日
  22. Jokergaming slot

    Thanks designed for sharing such a nice idea, article is nice, thats why i
    have read it completely

    回复 2020年2月26日
  23. CSS абсолютно относительное позиционирование

    Heya i am for the first time here. I came across this board and I find It really useful & it helped me out much.
    I hope to give something back and aid others like you helped me.

    回复 2020年2月26日
  24. Hyjykjc.com

    Can I simply say what a relief to discover someone who genuinely understands what they are
    talking about on the internet. You actually realize how
    to bring an issue to light and make it important. More people ought to look at this and
    understand this side of the story. I was surprised you’re not more popular given that you surely possess the gift.

    回复 2020年2月26日
  25. kenya web experts

    all the time i used to read smaller articles or reviews
    that also clear their motive, and that is also happening with this
    piece of writing which I am reading at this place.

    回复 2020年2月26日
  26. judi bola online

    Oh my goodness! Awesome article dude! Thank you
    so much, However I am having difficulties with your RSS.
    I don’t know the reason why I am unable to join it. Is there anybody else getting the same RSS problems?
    Anyone who knows the solution can you kindly respond?
    Thanks!!

    回复 2020年2月26日
  27. sound proof a home

    I think this is among the such a lot important information for
    me. And i’m glad reading your article. However should remark
    on some common issues, The site style is perfect, the articles is truly
    excellent : D. Just right task, cheers

    回复 2020年2月26日
  28. http://www.secrettantric.com/

    I have been browsing online more than 4 hours today, yet I
    never found any interesting article like yours. It is pretty worth enough for me.
    In my view, if all webmasters and bloggers made good content as
    you did, the web will be much more useful than ever before.

    回复 2020年2月26日
  29. Formax Lean

    Hi there, after reading this remarkable piece of writing i am as well delighted to share my familiarity here with mates.

    回复 2020年2月26日
  30. Victor

    Hurrah! In the end I got a weblog from where I can genuinely take valuable facts concerning
    my study and knowledge.

    回复 2020年2月26日
  31. เช่าเก้าอี้

    Howdy, i read your blog occasionally and i own a similar one and
    i was just curious if you get a lot of spam remarks?
    If so how do you prevent it, any plugin or anything you can suggest?
    I get so much lately it’s driving me mad so any help is very
    much appreciated.

    回复 2020年2月26日
  32. Heriberto

    Fantastic beat ! I wish to apprentice whilst you amend your site, how could
    i subscribe for a blog web site? The account helped me
    a acceptable deal. I have been tiny bit familiar of this your broadcast offered shiny
    clear idea

    回复 2020年2月26日
  33. Текущие прогнозы

    Greetings from Florida! I’m bored to tears at work so I decided to check out your website on my iphone during lunch break.
    I enjoy the info you provide here and can’t wait to take a look when I
    get home. I’m shocked at how quick your blog loaded on my phone
    .. I’m not even using WIFI, just 3G .. Anyways, great blog!

    回复 2020年2月26日
  34. Andheri Escorts

    My family every time say that I am killing my time here at net,
    except I know I am getting experience all the time by reading such nice articles
    or reviews.

    回复 2020年2月26日
  35. Royal Online V2

    Thank you for sharing your info. I truly appreciate your efforts and I will be waiting for your
    next write ups thanks once again.

    回复 2020年2月26日
  36. scr888 ios

    Oh my goodness! Incredible article dude! Thanks, However I am having troubles with
    your RSS. I don’t understand why I can’t subscribe to
    it. Is there anybody having identical RSS problems?

    Anybody who knows the solution can you kindly respond? Thanks!!

    回复 2020年2月26日
  37. w888

    Pretty portion of content. I just stumbled upon your weblog and in accession capital to assert
    that I acquire actually loved account your weblog posts.
    Any way I’ll be subscribing for your augment or even I success you get admission to constantly rapidly.

    回复 2020年2月26日
  38. Обработка винограда от вредителей и болезней

    Excellent, what a web site it is! This web site presents valuable facts
    to us, keep it up.

    回复 2020年2月26日
  39. best free live cams

    We offer tons of free online chat rooms where you can chat
    with strangers from around the world. Plain spy cam pussy shoots are just
    all over the place here but they are far not everything that we offer.

    The same thing, I was soaking pussy. If you are a
    boy who is looking for nude girls, enter Gaypage sex chat platform
    and look for hot young babes. ” line and relays the improbability of one of the other five fucking Yonases in the country looking exactly like him. Can be seen walking up to the house in one shot and looking around the kitchen in another. Can be seen walking up to the house in at least one shot during the episode, and being waved over to the counter by CH in another. Very inspiring. But overall the response has been very helpful and positive, hopefully I can repay the support in the future.

    回复 2020年2月26日
  40. supperlite.net

    Pretty element of content. I simply stumbled upon your website and in accession capital to
    claim that I acquire in fact enjoyed account your weblog posts.
    Any way I will be subscribing in your augment
    and even I achievement you get entry to constantly fast.

    回复 2020年2月26日
  41. slot online Deposit Via pulsa 5000

    Greetings I am so excited I found your site,
    I really found you by accident, while I was researching on Askjeeve for something else, Regardless I am here
    now and would just like to say kudos for a incredible post and a all round interesting blog (I
    also love the theme/design), I don’t have time to read through it all at the minute but I have bookmarked it and also included your RSS feeds,
    so when I have time I will be back to read more, Please do keep up the fantastic work.

    回复 2020年2月26日
  42. free fortnite skins Generator

    It’s the best time to make a few plans for the future and it’s time to be happy.

    I have learn this publish and if I may just I wish to counsel you some
    fascinating things or suggestions. Perhaps you can write next articles referring to this article.
    I want to learn more issues approximately it!

    回复 2020年2月26日
  43. taobao cosplay

    Very good post! We will be linking to this particularly great
    content onn our site. Keep up the great writing.

    回复 2020年2月26日
  44. Abbruch aus Lübeck

    This is a topic that’s close to my heart… Cheers! Where are your contact details though?

    回复 2020年2月26日
  45. Free Japan Adult Video

    Good day! I know this is kind of off topic but I was wondering which
    blog platform are you using for this website? I’m getting tired of WordPress because I’ve
    had issues with hackers and I’m looking at alternatives for another platform.

    I would be great if you could point me in the direction of a good platform.

    回复 2020年2月26日
  46. chaterba

    Why break the law when I can spend $0 on free porn websites .
    Talk about job less girls thinking their bodies are worth anything}

    回复 2020年2月26日
  47. Review smartphone vivo y83

    Really no matter if someone doesn’t be aware of then its up to other people that they will help, so here it happens.

    回复 2020年2月26日
  48. Awareness

    This article provides clear idea for the new visitors of
    blogging, that in fact how to do blogging.

    回复 2020年2月26日
  49. ???? open sky 777

    Yes! Finally something about sky777.

    回复 2020年2月26日
  50. Викифония: Вики для наушников

    We are a group of volunteers and opening a new scheme in our community.

    Your site offered us with valuable information to work on. You
    have done an impressive job and our entire community will be grateful
    to you.

    回复 2020年2月26日