MySQL - 选择当前周的最短日期，不一定是一周的第一天

Question

Using PHP/MySQL

I'm trying to create a select statement that gets the data from the least day of the current week (I'm using it to show data on a certain player 'this week'). The week starts on Sunday. Sundays's data may not always exist therefore if the Sunday data isn't found then it would use the next earliest day found, Monday, Tuesday, etc.

My date column is named 'theDate' and the datatype is 'DATE'

The query would need to be something like:

SELECT *
FROM table_name
WHERE name = '$username'
AND [...theDate = earliest day of data found for the current week week]
LIMIT 1

It would return a single row of data.

This is a query I tried for getting the 'this week' data, It doesn't seem to work correctly on Sunday's it shows nothing:

SELECT *
FROM table_name
WHERE playerName = '$username'
AND YEARWEEK(theDate) = YEARWEEK(CURRENT_DATE)
ORDER BY theDate;

This is the query that I'm using to get 'this months' data and it works even if the first day of the months data is not found, it will use the earliest date of data found in the current month/year (this query works perfect for me):

SELECT *
FROM table_name
WHERE playerName =  '$username'
AND theDate >= CAST( DATE_FORMAT( NOW(),'%Y-%m-01') AS DATE)
ORDER BY theDate
LIMIT 1

Answer 1

Without trying this, you probably need an inner query:

select * 
from table_name tn
where tn.the_date = 
(select min(the_date) 
from table_name 
where WEEKOFYEAR(the_date) = WEEKOFYEAR(CURDATE())
and YEAR(the_date) = YEAR(CURDATE()))

viz, give me the row(s) in the table with a date equal to the earliest date in the table in the current week and year.