sql group by 和 定义输出的小数位数

 

select job_name, ft_brch,LINE_cover,incre_cover,commit_id, compa_with,create_date
    from functest_buids_info,hole_functest,incre_functest 
    where functest_buids_info.id=hole_functest.build_info_id and functest_buids_info.id=incre_functest.build_info_id
    and incre_cover>=0 and functest_buids_info.job_name like "api%%" and ft_brch='master'
    and create_date > "2020-05-22" and create_date <= "2020-06-06"
    order by job_name, create_date desc) 

 

使用以上sql的结果，同一个job_name，结果记录有重复，为了一个job只拿一个最新的结果改为使用group by，详细如下：

 select job_name,ft_brch, max(LINE_cover) , max(incre_cover) , max(compa_with), max(create_date) from (

    (
    select job_name, ft_brch,LINE_cover,incre_cover,commit_id, compa_with,create_date
    from functest_buids_info,hole_functest,incre_functest 
    where functest_buids_info.id=hole_functest.build_info_id and functest_buids_info.id=incre_functest.build_info_id
    and incre_cover>=0 and functest_buids_info.job_name like "api%%" and ft_brch='master'
    and create_date > "2020-05-22" and create_date <= "2020-06-06"
    order by job_name, create_date desc) 
    as a )
    group by job_name

注：当使用group by时，select后面的字段的值如果是重复的，则可直接使用字段，如果不是重复的值的字段，则需要使用聚合函数。

如，max(LINE_cover)

结果 LINE_cover和incre_cover原本只是4位小数，搜索结果却成了N位小数，好吧，不明白。然后sql改为如下CAST(max(LINE_cover) AS decimal(9,4))，定义输出的小数为4位：

        select job_name,ft_brch, CAST(max(LINE_cover) AS decimal(9,4)), CAST(max(incre_cover) AS decimal(9,4)), max(compa_with), max(create_date) from (

    (
    select job_name, ft_brch,LINE_cover,incre_cover,commit_id, compa_with,create_date
    from functest_buids_info,hole_functest,incre_functest 
    where functest_buids_info.id=hole_functest.build_info_id and functest_buids_info.id=incre_functest.build_info_id
    and incre_cover>=0 and functest_buids_info.job_name like "api%%" and ft_brch='master'
    and create_date > "2020-05-22" and create_date <= "2020-06-06"
    order by job_name, create_date desc) 
    as a )
    group by job_name