114 lines
4.2 KiB
SQL
114 lines
4.2 KiB
SQL
-- =================================================================-- 验证7：检查临时表是否已清理
|
||
SELECT
|
||
'临时表清理检查' as check_type,
|
||
CASE
|
||
WHEN EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'temp_user_ids')
|
||
THEN '临时表仍存在'
|
||
ELSE '临时表已清理'
|
||
END as cleanup_status;
|
||
|
||
-- 验证8：检查配送任务分配逻辑
|
||
SELECT
|
||
'配送任务分配检查' as check_type,
|
||
COUNT(DISTINCT dt.driver_id) as assigned_drivers,
|
||
COUNT(*) as total_tasks,
|
||
ROUND(AVG(tasks_per_driver.task_count), 2) as avg_tasks_per_driver
|
||
FROM public.ml_delivery_tasks dt
|
||
CROSS JOIN (
|
||
SELECT driver_id, COUNT(*) as task_count
|
||
FROM public.ml_delivery_tasks
|
||
GROUP BY driver_id
|
||
) as tasks_per_driver;============
|
||
-- mock_data_insert.sql 修复验证脚本
|
||
-- 用途: 验证修复后的模拟数据插入脚本是否能正常执行
|
||
-- =====================================================================================
|
||
|
||
-- 验证1：检查商品价格数据完整性
|
||
SELECT
|
||
'商品价格检查' as check_type,
|
||
COUNT(*) as total_products,
|
||
COUNT(CASE WHEN base_price IS NULL THEN 1 END) as null_base_price_count,
|
||
COUNT(CASE WHEN base_price > 0 THEN 1 END) as valid_price_count
|
||
FROM public.ml_products;
|
||
|
||
-- 验证2：检查SKU价格数据完整性
|
||
SELECT
|
||
'SKU价格检查' as check_type,
|
||
COUNT(*) as total_skus,
|
||
COUNT(CASE WHEN price IS NULL THEN 1 END) as null_price_count,
|
||
COUNT(CASE WHEN price > 0 THEN 1 END) as valid_price_count
|
||
FROM public.ml_product_skus;
|
||
|
||
-- 验证3：测试商品-SKU价格查询逻辑
|
||
SELECT
|
||
'价格查询逻辑测试' as check_type,
|
||
p.name as product_name,
|
||
p.base_price,
|
||
s.price as sku_price,
|
||
COALESCE(s.price, p.base_price) as final_price,
|
||
CASE
|
||
WHEN s.price IS NOT NULL THEN 'SKU价格'
|
||
ELSE '基础价格'
|
||
END as price_source
|
||
FROM public.ml_products p
|
||
LEFT JOIN public.ml_product_skus s ON p.id = s.product_id
|
||
ORDER BY p.name, s.sku_code
|
||
LIMIT 10;
|
||
|
||
-- 验证4：检查订单商品价格是否存在NULL值
|
||
SELECT
|
||
'订单商品价格检查' as check_type,
|
||
COUNT(*) as total_order_items,
|
||
COUNT(CASE WHEN price IS NULL THEN 1 END) as null_price_count,
|
||
COUNT(CASE WHEN price > 0 THEN 1 END) as valid_price_count,
|
||
MIN(price) as min_price,
|
||
MAX(price) as max_price
|
||
FROM public.ml_order_items;
|
||
|
||
-- 验证5：检查订单关联的商家ID是否正确
|
||
SELECT
|
||
'订单商家关联检查' as check_type,
|
||
COUNT(DISTINCT o.merchant_id) as unique_merchants,
|
||
COUNT(*) as total_orders,
|
||
COUNT(CASE WHEN u.role = 'merchant' THEN 1 END) as valid_merchant_orders
|
||
FROM public.ml_orders o
|
||
LEFT JOIN public.ak_users u ON o.merchant_id = u.id;
|
||
|
||
-- 验证6：检查配送任务唯一性
|
||
SELECT
|
||
'配送任务唯一性检查' as check_type,
|
||
COUNT(*) as total_delivery_tasks,
|
||
COUNT(DISTINCT order_id) as unique_orders,
|
||
COUNT(*) - COUNT(DISTINCT order_id) as duplicate_order_count,
|
||
CASE
|
||
WHEN COUNT(*) = COUNT(DISTINCT order_id) THEN '✓ 无重复订单'
|
||
ELSE '✗ 存在重复订单配送任务'
|
||
END as uniqueness_status
|
||
FROM public.ml_delivery_tasks;
|
||
|
||
-- 验证7：检查临时表是否已清理
|
||
SELECT
|
||
'临时表清理检查' as check_type,
|
||
CASE
|
||
WHEN EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'temp_user_ids')
|
||
THEN '临时表仍存在'
|
||
ELSE '临时表已清理'
|
||
END as cleanup_status;
|
||
|
||
-- 输出总体验证结果
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE '=======================================================';
|
||
RAISE NOTICE '模拟数据插入脚本修复验证完成';
|
||
RAISE NOTICE '=======================================================';
|
||
RAISE NOTICE '请检查以上查询结果：';
|
||
RAISE NOTICE '1. 商品和SKU价格应无NULL值';
|
||
RAISE NOTICE '2. 订单商品价格应无NULL值';
|
||
RAISE NOTICE '3. 订单应正确关联到商家用户';
|
||
RAISE NOTICE '4. 配送任务应无重复订单';
|
||
RAISE NOTICE '5. 临时表应已清理';
|
||
RAISE NOTICE '=======================================================';
|
||
RAISE NOTICE '如所有检查通过，说明修复有效';
|
||
RAISE NOTICE '=======================================================';
|
||
END $$;
|