MySql查询需要帮助！ [英] MySql query Help need!

问题描述

您好，需要有关此查询的帮助.如果我运行它并转到"REPRES"部分，它会完美返回.问题是我还需要在"date_entered"列中的这两个日期之间进行选择.

如果仅在状态"列中选择了具有"REDIR"和"REPRES"的数据，但是如果没有筛选出仅在以下两者之间进行选择，则从2个名为"fine"和"charge"的表中获取数据那两个日期.

请帮忙.

Hi Need help on this query. If I run it up and to the ''REPRES'' Section it comes back perfectly. The problem is I need to select between those 2 dates in the "date_entered" column as well.

I getting my data from 2 tables named "fine" and "charge" if selects only the data that has "REDIR" and "REPRES" in the ''status'' column but if''s not filtering out the to select only between those 2 dates.

Please help.

SELECT fine.case, fine.regnum, fine.issuing_authority, fine.date_entered, charge.amount, fine.status
FROM fine INNER JOIN charge ON fine.case=charge.case
WHERE fine.status = 'REDIR' OR fine.status = 'REPRES'
AND fine.date_entered >= DATE_FORMAT(fine.date_entered, '%d-%m-%Y') = "1/1/2010"
AND fine.date_entered <= DATE_FORMAT(fine.date_entered, '%d-%m-%Y') = "31/12/2010";

这就是结果；如您所见，日期对此没有影响.

''1179430141159012''，``ZHD107GP''，``ALBERTON''，``9/2/2011''，``100''，``REDIR''
''BF80287274110''，``YZD473GP''，``BELFAST''，``9/2/2011''，``750''，``REDIR''
''1167289141134730''，``VPH261GP''，``ALBERTON''，``9/2/2011''，``100''，``REDIR''
''0240240055398532''，``YZD461GP''，``JHB''，``9/2/2011''，``750''，``REDIR''
''0240240054323059''，``ZKW597GP''，``JHB''，``9/2/2011''，``1500''，``REDIR''
``N80049240120''，``ZWB136GP''，``NELSPRUIT''，``9/2/2011''，``750''，``REDIR''
``MB80029645105''，``XPT612GP''，``MIDDELBURG''，``9/2/2011''，``100''，``REDIR''
''0240240057503759''，``XXK353GP''，``JHB''，``9/2/2011''，``750''，``REDIR''
''0240240054295984''，``YZF152GP''，``JHB''，``9/2/2011''，``750''，``REDIR''
''0240240054291614''，``SHD923GP''，``JHB''，``9/2/2011''，``1500''，``REDIR''
''0240240054215571''，``JDG518GP''，``JHB''，``9/2/2011''，``750''，``REDIR''
''0240240055373045''，``SJV048GP''，``JHB''，``9/2/2011''，``1500''，``REDIR''
''0240240055326163''，``DDJ186GP''，``JHB''，``9/2/2011''，``750''，``REDIR''
''0240240055404932''，``SJV048GP''，``JHB''，``15/3/2011''，``4500''，``REDIR''


谢谢你.任何帮助将不胜感激.

:-)

This is the result; As you can see the dates has no effect on it.

''1179430141159012'', ''ZHD107GP'', ''ALBERTON'', ''9/2/2011'', ''100'', ''REDIR''
''BF80287274110'', ''YZD473GP'', ''BELFAST'', ''9/2/2011'', ''750'', ''REDIR''
''1167289141134730'', ''VPH261GP'', ''ALBERTON'', ''9/2/2011'', ''100'', ''REDIR''
''0240240055398532'', ''YZD461GP'', ''JHB'', ''9/2/2011'', ''750'', ''REDIR''
''0240240054323059'', ''ZKW597GP'', ''JHB'', ''9/2/2011'', ''1500'', ''REDIR''
''N80049240120'', ''ZWB136GP'', ''NELSPRUIT'', ''9/2/2011'', ''750'', ''REDIR''
''MB80029645105'', ''XPT612GP'', ''MIDDELBURG'', ''9/2/2011'', ''100'', ''REDIR''
''0240240057503759'', ''XXK353GP'', ''JHB'', ''9/2/2011'', ''750'', ''REDIR''
''0240240054295984'', ''YZF152GP'', ''JHB'', ''9/2/2011'', ''750'', ''REDIR''
''0240240054291614'', ''SHD923GP'', ''JHB'', ''9/2/2011'', ''1500'', ''REDIR''
''0240240054215571'', ''JDG518GP'', ''JHB'', ''9/2/2011'', ''750'', ''REDIR''
''0240240055373045'', ''SJV048GP'', ''JHB'', ''9/2/2011'', ''1500'', ''REDIR''
''0240240055326163'', ''DDJ186GP'', ''JHB'', ''9/2/2011'', ''750'', ''REDIR''
''0240240055404932'', ''SJV048GP'', ''JHB'', ''15/3/2011'', ''4500'', ''REDIR''


Thank you. Any help will be appreciated.

:-)

推荐答案

MySQL遵循"YYYY-MM-DD"格式作为DateTime的默认格式.
http://dev.mysql.com/doc/refman/5.0/en/datetime.html

尝试通过如下修改查询.

MySQL follows "YYYY-MM-DD" format as default for DateTime.
http://dev.mysql.com/doc/refman/5.0/en/datetime.html

Try by modifying your query as below.

SELECT fine.case, fine.regnum, fine.issuing_authority, fine.date_entered, charge.amount, fine.status
FROM fine INNER JOIN charge ON fine.case=charge.case
WHERE fine.status = 'REDIR' OR fine.status = 'REPRES'
AND (fine.date_entered BETWEEN '2010-1-1' AND '2010-12-31');

编辑-

Edited -

SELECT fine.case, fine.regnum, fine.issuing_authority, fine.date_entered, charge.amount, fine.status
FROM fine INNER JOIN charge ON fine.case=charge.case
WHERE (fine.status = 'REDIR' OR fine.status = 'REPRES')
AND (fine.date_entered BETWEEN '1-1-2010' AND '31-12-2010');

您的查询应如下

your query should be as follow

SELECT fine.case, fine.regnum, fine.issuing_authority, fine.date_entered, charge.amount, fine.status
FROM fine INNER JOIN charge ON fine.case=charge.case
WHERE fine.status in ('REDIR',  'REPRES')
AND fine.date_entered between DATE_FORMAT(fine.date_entered, '%d-%m-%Y') = "1/1/2010"
AND DATE_FORMAT(fine.date_entered, '%d-%m-%Y') = "31/12/2010";

>

试试这个

Try this

这篇关于MySql查询需要帮助！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！