check_status.sql
1.46 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
import pymssql
conn = pymssql.connect(
server='192.168.1.168',
port=1433,
database='wms4_global',
user='sa',
password='HHrobot123.'
)
cursor = conn.cursor(as_dict=True)
# 统计头表status=50的所有数据
cursor.execute("""
SELECT
COUNT(*) as total,
SUM(CASE WHEN d.status = 50 THEN 1 ELSE 0 END) as detail_50,
SUM(CASE WHEN d.status = 20 THEN 1 ELSE 0 END) as detail_20,
SUM(CASE WHEN d.status = 0 THEN 1 ELSE 0 END) as detail_0,
SUM(CASE WHEN d.status IS NULL THEN 1 ELSE 0 END) as detail_null,
SUM(CASE WHEN h.status = 50 AND (d.status IS NULL OR d.status != 50) THEN 1 ELSE 0 END) as need_sync
FROM receipt_container_header h
INNER JOIN receipt_container_detail d ON d.receipt_container_id = h.id
INNER JOIN receipt_header r ON r.id = d.receipt_id
WHERE h.status = 50
""")
row = cursor.fetchone()
print('=== 正式库：头表status=50的详情状态分布 ===')
for k, v in row.items():
print(f' {k}: {v}')
# 看看各类型的数量分布
cursor.execute("""
SELECT r.type, COUNT(*) as cnt
FROM receipt_container_header h
INNER JOIN receipt_container_detail d ON d.receipt_container_id = h.id
INNER JOIN receipt_header r ON r.id = d.receipt_id
WHERE h.status = 50 AND (d.status IS NULL OR d.status != 50) AND r.type NOT IN ('BMR','DBR','QTR','SCR','CPR','BCPR','YCLR','QTRKD')
GROUP BY r.type
""")
print()
print('=== 待同步的数据（按类型）===')
for row in cursor:
print(f' type={row["type"]}: {row["cnt"]}')
conn.close()