英文:
SQL Pulling data from nearest date for multiple months
问题
我可以帮你提取每个月最接近当前日期的数据和相应的颜色。以下是上面提到的代码的答案:
2023-01-31 red
2023-02-05 red
英文:
I have the following data of date and color in a mysql database
2023-01-28 red
2023-01-29 blue
2023-01-30 blue
2023-01-31 red
2023-02-01 blue
2023-02-02 blue
2023-02-03 blue
2023-02-04 blue
2023-02-05 red
How can I pull the closest date to current date from each month and the associated color? Example answer for above code would be
2023-01-31 red
2023-02-05 red
答案1
得分: 1
I do not have MySQL, therefore, I am going to use SQL Fiddle.
-- 1 - Create table
创建表格
CREATE TABLE IF NOT EXISTS colors
(
the_date DATE,
the_color VARCHAR(16)
) ENGINE=INNODB;
-- 2 - Add data
添加数据
INSERT INTO colors VALUES ('2023-01-28', 'red');
INSERT INTO colors VALUES ('2023-01-29', 'blue');
INSERT INTO colors VALUES ('2023-01-30', 'blue');
INSERT INTO colors VALUES ('2023-01-31', 'red');
INSERT INTO colors VALUES ('2023-02-01', 'blue');
INSERT INTO colors VALUES ('2023-02-02', 'blue');
INSERT INTO colors VALUES ('2023-02-03', 'blue');
INSERT INTO colors VALUES ('2023-02-04', 'blue');
INSERT INTO colors VALUES ('2023-02-05', 'red');
Steps 1 & 2 create the table and add the data. Step 3 will want to covert the date into YYYYMM format for grouping and aggregation. Then group by that new fields and the color, selecting the date that is the largest using the MAX() function.
第1和第2步创建表格并添加数据。第3步将日期转换为YYYYMM格式以进行分组和聚合。然后按新字段和颜色进行分组,使用MAX()函数选择最大的日期。
I could have left the new column in the final results, but you did not have it in the result set. Therefore, use a derived table to get the result in the () inner statement and select the two fields (columns) you want in the outer statement.
我本可以在最终结果中保留新列,但你没有在结果集中包括它。因此,使用派生表在内部语句中获取结果,并在外部语句中选择你想要的两个字段(列)。
-- 3 - Solve the problem
-- 3 - 解决问题
SELECT
the_color,
the_largest_dte
FROM
(
SELECT
DATE_FORMAT(the_date, '%Y%m') as the_yyyy_mm,
the_color,
max(the_date) as the_largest_dte
FROM colors
GROUP BY
DATE_FORMAT(the_date, '%Y%m'),
the_color
) AS D
The screen shot below shows everything together.
下面的截图显示了所有内容。
http://sqlfiddle.com/#!9/1700b5/7/0
英文:
I do not have MySQL, therefore, I am going to use SQL Fiddle.
-- 1 - Create table
CREATE TABLE IF NOT EXISTS colors
(
the_date DATE,
the_color VARCHAR(16)
) ENGINE=INNODB;
-- 2 - Add data
INSERT INTO colors VALUES ('2023-01-28', 'red');
INSERT INTO colors VALUES ('2023-01-29', 'blue');
INSERT INTO colors VALUES ('2023-01-30', 'blue');
INSERT INTO colors VALUES ('2023-01-31', 'red');
INSERT INTO colors VALUES ('2023-02-01', 'blue');
INSERT INTO colors VALUES ('2023-02-02', 'blue');
INSERT INTO colors VALUES ('2023-02-03', 'blue');
INSERT INTO colors VALUES ('2023-02-04', 'blue');
INSERT INTO colors VALUES ('2023-02-05', 'red');
Steps 1 & 2 create the table and add the data. Step 3 will want to covert the date into YYYYMM format for grouping and aggregation. Then group by that new fields and the color, selecting the date that is the largest using the MAX() function.
I could have left the new column in the final results, but you did not have it in the result set. Therefore, use a derived table to get the result in the () inner statement and select the two fields (columns) you want in the outer statement.
-- 3 - Solve the problem
SELECT
the_color,
the_largest_dte
FROM
(
SELECT
DATE_FORMAT(the_date, '%Y%m') as the_yyyy_mm,
the_color,
max(the_date) as the_largest_dte
FROM colors
GROUP BY
DATE_FORMAT(the_date, '%Y%m'),
the_color
) AS D
The screen shot below shows everything together.
答案2
得分: 1
正如我在评论中提到的,你需要使用 MAX 函数。
我不确定我是否理解你的要求,但我认为你想要获取日期最新的颜色实例...
使用 MD 作为
(
选择 MAX(datefld) 作为 maxdate
从 mytable 中
),
使用 CR 作为
(
选择 datefld, colorfld
从 mytable 作为 MT
内连接 MD
在 MT.datefld = MD.maxdate
)
选择 MT.*
从 CR 中
内连接 mytable 作为 MT
在 MT.colorfld = CR.colorfld
和 MT.datefld <= CR.datefld
按 MT.datefld 排序;
结果:
datefld | colorfld |
---|---|
2023-01-28 00:00:00 | red |
2023-01-31 00:00:00 | red |
2023-02-05 00:00:00 | red |
英文:
As i mentioned in the comment to the question, you have to use MAX function.
I'm not sure i understand your requirements, but i think you want to get all instance of color which date is the newest...
WITH MD AS
(
SELECT MAX(datefld) as maxdate
FROM mytable
),
CR AS
(
SELECT datefld, colorfld
FROM mytable AS MT
INNER JOIN MD
ON MT.datefld = MD.maxdate
)
SELECT MT.*
FROM CR
INNER JOIN mytable AS MT
ON MT.colorfld = CR.colorfld
AND MT.datefld <= CR.datefld
ORDER BY MT.datefld;
Result:
datefld | colorfld |
---|---|
2023-01-28 00:00:00 | red |
2023-01-31 00:00:00 | red |
2023-02-05 00:00:00 | red |
答案3
得分: 0
我获得了您想要的输出。
with td as (select *, datediff(CURRENT_DATE(), tdate) ds from temp_data)
select tdate, color from td where ds in
(
with tp as (
select month(tdate), min(datediff(CURRENT_DATE(), tdate)) as days from temp_data group by month(tdate)
)
select days from tp
)
英文:
I got your desired output.
with td as (select *,datediff(CURRENT_DATE(),tdate) ds from temp_data)
select tdate,color from td where ds in
(
with tp as(
select month(tdate), min(datediff(CURRENT_DATE(),tdate)) as days from temp_data group by month(tdate))
select days from tp)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论