查询邀约时间和联系时间不在同一天的记录.sql
2.9 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
-- ============================================
-- 查询 lq_yaoyjl 表中邀约时间(yysj)和联系时间(lxsj)不在同一天的记录
-- ============================================
-- 说明：查询邀约时间和联系时间不在同一天的数据，用于数据校验或分析
--
-- 查询逻辑：
-- 1. 两个时间都不为空，但日期不同
-- 2. 其中一个为空，另一个不为空（视为不在同一天）
-- 3. 两个都为空的情况不包含在结果中（因为无法判断是否在同一天）
-- ============================================
-- 基础查询：查询不在同一天的记录
-- ============================================
SELECT
F_Id AS 邀约编号,
yyr AS 邀约人,
yysj AS 邀约时间,
DATE(yysj) AS 邀约日期,
lxsj AS 联系时间,
DATE(lxsj) AS 联系日期,
yykh AS 邀约客户,
yykhxm AS 邀约客户姓名,
F_CreateTime AS 创建时间
FROM lq_yaoyjl
WHERE (
-- 两个时间都不为空，但日期不同
(yysj IS NOT NULL AND lxsj IS NOT NULL AND DATE(yysj) != DATE(lxsj))
OR
-- 邀约时间为空，联系时间不为空
(yysj IS NULL AND lxsj IS NOT NULL)
OR
-- 联系时间为空，邀约时间不为空
(yysj IS NOT NULL AND lxsj IS NULL)
)
ORDER BY F_CreateTime DESC;
-- ============================================
-- 统计查询：统计不在同一天的记录数量
-- ============================================
SELECT
COUNT(*) AS 不在同一天的记录数,
COUNT(CASE WHEN yysj IS NOT NULL AND lxsj IS NOT NULL AND DATE(yysj) != DATE(lxsj) THEN 1 END) AS 两个时间都有但日期不同,
COUNT(CASE WHEN yysj IS NULL AND lxsj IS NOT NULL THEN 1 END) AS 邀约时间为空,
COUNT(CASE WHEN yysj IS NOT NULL AND lxsj IS NULL THEN 1 END) AS 联系时间为空
FROM lq_yaoyjl
WHERE (
(yysj IS NOT NULL AND lxsj IS NOT NULL AND DATE(yysj) != DATE(lxsj))
OR (yysj IS NULL AND lxsj IS NOT NULL)
OR (yysj IS NOT NULL AND lxsj IS NULL)
);
-- ============================================
-- 详细分析查询：按日期差异分组统计
-- ============================================
SELECT
CASE
WHEN yysj IS NULL AND lxsj IS NOT NULL THEN '邀约时间为空'
WHEN yysj IS NOT NULL AND lxsj IS NULL THEN '联系时间为空'
WHEN yysj IS NOT NULL AND lxsj IS NOT NULL THEN CONCAT('相差', DATEDIFF(lxsj, yysj), '天')
END AS 日期差异类型,
COUNT(*) AS 记录数
FROM lq_yaoyjl
WHERE (
(yysj IS NOT NULL AND lxsj IS NOT NULL AND DATE(yysj) != DATE(lxsj))
OR (yysj IS NULL AND lxsj IS NOT NULL)
OR (yysj IS NOT NULL AND lxsj IS NULL)
)
GROUP BY
CASE
WHEN yysj IS NULL AND lxsj IS NOT NULL THEN '邀约时间为空'
WHEN yysj IS NOT NULL AND lxsj IS NULL THEN '联系时间为空'
WHEN yysj IS NOT NULL AND lxsj IS NOT NULL THEN CONCAT('相差', DATEDIFF(lxsj, yysj), '天')
END
ORDER BY 记录数 DESC;