SQL 从多个月中提取最近日期的数据

huangapple go评论67阅读模式
英文:

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.

下面的截图显示了所有内容。

SQL 从多个月中提取最近日期的数据

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.

SQL 从多个月中提取最近日期的数据

http://sqlfiddle.com/#!9/1700b5/7/0

答案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 排序;

SQLFiddle

结果:

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 &lt;= CR.datefld
ORDER BY MT.datefld;

SQLFiddle

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
)

https://dbfiddle.uk/92RoM59i

英文:

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)

https://dbfiddle.uk/92RoM59i

huangapple
  • 本文由 发表于 2023年3月3日 23:59:59
  • 转载请务必保留本文链接:https://go.coder-hub.com/75629309.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定