89 lines
2.6 KiB
SQL
89 lines
2.6 KiB
SQL
-- =====================================================
|
||
-- 验证脚本：检查联络单号和件号字段是否正确
|
||
-- 创建时间：2026-02-04
|
||
-- =====================================================
|
||
|
||
USE fullautowaterpressure;
|
||
|
||
-- 1. 检查normaltemperature表结构
|
||
SHOW COLUMNS FROM normaltemperature LIKE '%Contact%';
|
||
SHOW COLUMNS FROM normaltemperature LIKE '%Item%';
|
||
|
||
-- 2. 检查scandata表结构
|
||
SHOW COLUMNS FROM scandata LIKE '%Contact%';
|
||
SHOW COLUMNS FROM scandata LIKE '%Item%';
|
||
|
||
-- 3. 检查索引
|
||
SHOW INDEX FROM normaltemperature WHERE Key_name LIKE 'idx_%';
|
||
SHOW INDEX FROM scandata WHERE Key_name LIKE 'idx_%';
|
||
|
||
-- 4. 统计normaltemperature表数据
|
||
SELECT
|
||
'报表数据统计' as 表名,
|
||
COUNT(*) as 总记录数,
|
||
COUNT(ContactNumber) as 有联络单号,
|
||
COUNT(ItemNumber) as 有件号,
|
||
COUNT(CASE WHEN ContactNumber IS NOT NULL AND ItemNumber IS NOT NULL THEN 1 END) as 两者都有,
|
||
COUNT(CASE WHEN ContactNumber IS NULL OR ItemNumber IS NULL THEN 1 END) as 缺失数据
|
||
FROM normaltemperature;
|
||
|
||
-- 5. 统计scandata表数据
|
||
SELECT
|
||
'扫码数据统计' as 表名,
|
||
COUNT(*) as 总记录数,
|
||
COUNT(ContactNumber) as 有联络单号,
|
||
COUNT(ItemNumber) as 有件号,
|
||
COUNT(CASE WHEN ContactNumber IS NOT NULL AND ItemNumber IS NOT NULL THEN 1 END) as 两者都有,
|
||
COUNT(CASE WHEN ContactNumber IS NULL OR ItemNumber IS NULL THEN 1 END) as 缺失数据
|
||
FROM scandata;
|
||
|
||
-- 6. 查看normaltemperature最新10条记录
|
||
SELECT
|
||
Id,
|
||
barcode as 原条码,
|
||
ContactNumber as 联络单号,
|
||
ItemNumber as 件号,
|
||
CreateTime as 创建时间
|
||
FROM normaltemperature
|
||
ORDER BY Id DESC
|
||
LIMIT 10;
|
||
|
||
-- 7. 查看scandata最新10条记录
|
||
SELECT
|
||
Id,
|
||
barcode as 原条码,
|
||
ContactNumber as 联络单号,
|
||
ItemNumber as 件号,
|
||
CreateTime as 创建时间
|
||
FROM scandata
|
||
ORDER BY Id DESC
|
||
LIMIT 10;
|
||
|
||
-- 8. 检查数据一致性（barcode应该等于ContactNumber-ItemNumber）
|
||
SELECT
|
||
'数据一致性检查' as 检查项,
|
||
COUNT(*) as 不一致记录数
|
||
FROM normaltemperature
|
||
WHERE barcode IS NOT NULL
|
||
AND ContactNumber IS NOT NULL
|
||
AND ItemNumber IS NOT NULL
|
||
AND barcode != CONCAT(ContactNumber, '-', ItemNumber);
|
||
|
||
-- 9. 查找可能有问题的数据
|
||
SELECT
|
||
Id,
|
||
barcode,
|
||
ContactNumber,
|
||
ItemNumber,
|
||
CONCAT(ContactNumber, '-', ItemNumber) as 应该的barcode
|
||
FROM normaltemperature
|
||
WHERE barcode IS NOT NULL
|
||
AND ContactNumber IS NOT NULL
|
||
AND ItemNumber IS NOT NULL
|
||
AND barcode != CONCAT(ContactNumber, '-', ItemNumber)
|
||
LIMIT 10;
|
||
|
||
-- =====================================================
|
||
-- 验证完成
|
||
-- =====================================================
|