python测试开发班——SQL答疑帖

将你的问题写在回复里

视频中老师新建表格,名字和字段名加了单引号可以运行成功
为什么在我本地新建表格,名字和字段名加上单引号运行不成功,不加单引号就运行成功呢?
image
image

看起来上面的sql最后一个标点符号不太对,所有符号都应该是英文格式下的

1 Like

最后一个符号看着不太对

这位同学观察的很仔细

天天测数据报表,这样的sql一大堆…看多了也能看出开发水平…
SELECT
a.time time,(
CASE
WHEN 2 =3 THEN
‘’
ELSE
a.reqUserCount
END
) requestUserCount,
(
CASE
WHEN 2 =3 THEN
ROUND(
a.matchCount / a.downUserCount,
2
)
ELSE
ROUND(
a.reqCount / a.reqUserCount,
2
)
END
) requestUserAvgCount,
a.downUserCount downUserCount,
ROUND(
a.downCount / a.downUserCount,
2
) downUserAvgCount,
a.showUserCount showUserCount,
ROUND(
a.showCount / a.showUserCount,
2
) showUserAvgCount,
a.clickUserCount clickUserCount,
ROUND(
a.clickCount / a.clickUserCount,
2
) clcikUserAvgCount
FROM
(
SELECT
(
CASE
WHEN ‘DAY’ =‘DAY’ THEN
DATE_FORMAT(asu.start_time, ‘%Y-%m-%d’)
WHEN ‘WEEK’ =‘DAY’ THEN
DATE_FORMAT(asu.start_time, ‘%Y%u’)
WHEN ‘MONTH’ =‘DAY’ THEN
DATE_FORMAT(asu.start_time, ‘%Y-%m’)
END
) time,
asu.req_user_count reqUserCount,
sum(asf.req_count) reqCount,
0 matchCount,
asu.down_user_count downUserCount,
sum(asf.down_count) downCount,
asu.show_user_count showUserCount,
sum(asf.show_count) showCount,
asu.click_user_count clickUserCount,
sum(asf.click_count) clickCount
FROM
advert_stats_user_flow asu
LEFT JOIN advert_stats_flow asf ON DATE_FORMAT(asu.start_time, ‘%Y-%m-%d’) = DATE_FORMAT(asf.start_time, ‘%Y-%m-%d’)
AND asf.stats_type = ‘DAY’
LEFT JOIN ssp_ad_place sap ON sap.place_no = asf.place_no
LEFT JOIN ssp_ad_media sam ON sam.media_no = asf.media_no
LEFT JOIN advert_user_info aui ON aui.user_no = asf.media_owner_no
WHERE
asu.stats_type =‘DAY’
AND asu.start_time >= ‘2020-04-09 00:00:00’
AND asu.end_time <= ‘2020-04-15 23:59:59’
GROUP BY
time
) a

是的,实际业务中,业务sql查询,尤其是报表统计相关的,sql写法与查询性能有很大关系,能体现出sql质量

关闭