数据集 - 我的帆软

数据集

SELECT count(pt.pt_number)个数,DATE_FORMAT(ptvalue.input_time,"%Y-%m-%d")年月日,pt_number

FROM mom_pro_point pt INNER JOIN mom_pro_point_value${CONCATENATE("_",YEAR(MONTHDELTA(monthnumber+ "-01",1)))} ptvalue ON pt.id = ptvalue.point_id

WHERE ptvalue.is_deleted = 0

AND pt.pt_number IN ("KDM38DLKFKY_0004")

AND DATE_FORMAT(ptvalue.input_time,'%Y-%m-%d') >= '${DATEINMONTH(MONTHDELTA(monthnumber + "-01",-1),-1)}' AND DATE_FORMAT(ptvalue.input_time,'%Y-%m-%d') <='${DATEINMONTH(MONTHDELTA(monthnumber + "-01",0),-1)}'

AND (ptvalue.point_value > 0.0085 OR ptvalue.point_value < 0.005)

group by DATE_FORMAT(ptvalue.input_time,"%Y-%m-%d")

order by DATE_FORMAT(ptvalue.input_time,"%Y-%m-%d")asc

这个数据集我应该改那个地方能值查本月第一天带最后一天，不要上个月的最后一天

FineReport 帆软用户VB8LU1F5Yo 发布于 2023-7-13 17:20

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：3 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共3回答

最佳回答

Z4u3z1Lv6专家互助
发布于2023-7-13 17:24

举报收起评论(1)

帆软用户VB8LU1F5Yo(提问者) 一下就对了，谢大哥鼎力支持

2023-07-13 17:35

用户k6280494Lv6资深互助
发布于2023-7-13 17:21（编辑于 2023-7-13 17:33）

把时间字段格式化成年月

SELECT count(pt.pt_number)个数,DATE_FORMAT(ptvalue.input_time,"%Y-%m-%d")年月日,pt_number

FROM mom_pro_point pt INNER JOIN mom_pro_point_value${CONCATENATE("_",YEAR(MONTHDELTA(monthnumber+ "-01",1)))} ptvalue ON pt.id = ptvalue.point_id

WHERE ptvalue.is_deleted = 0

AND pt.pt_number IN ("KDM38DLKFKY_0004")

AND DATE_FORMAT(ptvalue.input_time,'%Y-%m') = '${FORMAT(DATEINMONTH(MONTHDELTA(monthnumber + "-01",-1),-1),"yyyy-MM")}'

AND (ptvalue.point_value > 0.0085 OR ptvalue.point_value < 0.005)

group by DATE_FORMAT(ptvalue.input_time,"%Y-%m")

order by DATE_FORMAT(ptvalue.input_time,"%Y-%m-%d")asc

举报收起评论(5)

帆软用户VB8LU1F5Yo(提问者) 能具体操作一下嘛

2023-07-13 17:22

用户k6280494 回复帆软用户VB8LU1F5Yo(提问者) 更新了，你看下

2023-07-13 17:23

帆软用户VB8LU1F5Yo(提问者) 查不出来了这个

2023-07-13 17:29

用户k6280494 回复帆软用户VB8LU1F5Yo(提问者) 把下面的分组改成按年月分组

2023-07-13 17:33

用户k6280494 回复帆软用户VB8LU1F5Yo(提问者) 如果你要的是按天分组，就用楼下的方法

2023-07-13 17:34

snrtuemcLv8专家互助
发布于2023-7-13 17:23

拷贝下面对的sql就可以，取月初月末的公式改下

SELECT count(pt.pt_number)个数,DATE_FORMAT(ptvalue.input_time,"%Y-%m-%d")年月日,pt_number

FROM mom_pro_point pt INNER JOIN mom_pro_point_value${CONCATENATE("_",YEAR(MONTHDELTA(monthnumber+ "-01",1)))} ptvalue ON pt.id = ptvalue.point_id

WHERE ptvalue.is_deleted = 0

AND pt.pt_number IN ("KDM38DLKFKY_0004")

AND DATE_FORMAT(ptvalue.input_time,'%Y-%m-%d') >= '${DATEINMONTH(MONTHDELTA(monthnumber + "-01",-1),1)}' AND DATE_FORMAT(ptvalue.input_time,'%Y-%m-%d') <='${DATEINMONTH(MONTHDELTA(monthnumber + "-01",-1),-1)}'

AND (ptvalue.point_value > 0.0085 OR ptvalue.point_value < 0.005)

group by DATE_FORMAT(ptvalue.input_time,"%Y-%m-%d")

order by DATE_FORMAT(ptvalue.input_time,"%Y-%m-%d")asc

=======

上月月末

DATEINMONTH(MONTHDELTA(TODAY(),-1),-1)

上月月初

DATEINMONTH(MONTHDELTA(TODAY(),-1),1)