如何通过选择“最大值”将表连接到两列上？sql服务器

wgx48brx 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(310)

我正在处理一个视图，并有下表，我试图修改它(sql服务器）
表x

ID    Value  End_time
1     10     2019-12-06T07:00:00+0000
2     15     2020-01-07T07:00:00+0000
2     20     2020-01-31T07:00:00+0000
3     25     2020-02-02T07:00:00+0000
3     30     2020-02-28T07:00:00+0000
4     35     2020-03-05T07:00:00+0000
4     40     2020-03-31T07:00:00+0000

[end_time]必须从varchar转换为date，并在月份和年份中进一步拆分，然后我必须根据年度和月份的数据选择最大日期和组。
所以我提出以下问题

SELECT        [ID], [Value], CONVERT(date, LEFT([End_time], LEN([End_time]) - 14)) AS [date], 
              -- convert to date

              MONTH(CONVERT(date, LEFT([End_time], LEN([End_time]) - 14))) AS Month_Name, --get month
              YEAR(CONVERT(date, LEFT([End_time], LEN([End_time]) - 14))) AS Year_Name --get year
FROM            X AS Table 
                INNER JOIN --join on itself
                (SELECT MAX([date]) AS MaxDate, MONTH([date]) AS Month_Name, YEAR(date) AS Year_Name
                 FROM X
                 GROUP BY MONTH([date]), YEAR([date])) AS D ON D.MaxDate = CONVERT(date, 
                                                         LEFT([End_time], LEN([End_time]) - 14))

但我得到一个错误，没有日期这样的列。我试着这样加入：

INNER JOIN --join on itself
                    (SELECT MAX(CONVERT(date, LEFT([End_time], LEN([End_time]) - 14))) AS MaxDate, 
                     MONTH([date]) AS Month_Name, YEAR(date) AS Year_Name
                     FROM X
                     GROUP BY MONTH([date]), YEAR([date])) AS D ON D.MaxDate = CONVERT(date, 
                                                             LEFT([End_time], LEN([End_time]) - 14))

但是我得到了一个错误，多部分标识符“x.end\u time”无法绑定。
所以我的理想结果是

ID    Value  Month_Name Year_Name
1     10     12         2019
2     35     01         2020
3     55     02         2020
4     75     03         2020

sql Join sql-server group-by

来源：https://stackoverflow.com/questions/62408634/how-to-join-a-table-on-two-columns-by-selecting-max-value-sql-server

2条答案

按热度按时间

j2qf4p5b1#

我只想用：

select id, value,
       substring(endtime, 5, 2) as month,
       left(endtime, 4) as year
from (select t.*,
             row_number() over (partition by id order by endtime desc) as seqnum
      from x
     ) x
where seqnum = 1;

尽管将日期/时间值存储为字符串是一个错误的表示选择，但您至少为该字符串选择了一种非常好的格式。您只需按字符串排序即可获得最新或最旧的行。

赞(0）回复(0）举报 2021-07-26

icnyk63a2#

SELECT MAX([table_alias.ID]), 
       SUM([table_alias.VALUE]), 
       [table_alias.Month_Name], 
       [table_alias.Year_Name]
FROM ( SELECT  [ID], [Value], 
        MONTH(CONVERT(date, LEFT([End_time], LEN([End_time]) - 14))) AS Month_Name,
        YEAR(CONVERT(date, LEFT([End_time], LEN([End_time]) - 14))) AS Year_Name 
FROM  X) AS table_alias 
group by [table_alias.Month_Name], 
       [table_alias.Year_Name]

赞(0）回复(0）举报 2021-07-26

我来回答

如何通过选择“最大值”将表连接到两列上？sql服务器

2条答案

相关问题

热门标签

最新问答