33 lines
1.8 KiB
SQL
33 lines
1.8 KiB
SQL
-- 测试处方库功能的SQL语句
|
|
|
|
-- 1. 查看表结构
|
|
DESC zyt_prescription_library;
|
|
|
|
-- 2. 插入测试数据
|
|
INSERT INTO `zyt_prescription_library`
|
|
(`prescription_name`, `herbs`, `is_public`, `creator_id`, `creator_name`, `create_time`, `update_time`)
|
|
VALUES
|
|
('六味地黄丸', '[{"name":"熟地黄","dosage":24},{"name":"山茱萸","dosage":12},{"name":"山药","dosage":12},{"name":"泽泻","dosage":9},{"name":"牡丹皮","dosage":9},{"name":"茯苓","dosage":9}]', 1, 1, '测试医生', UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
|
|
('补中益气汤', '[{"name":"黄芪","dosage":15},{"name":"党参","dosage":10},{"name":"白术","dosage":10},{"name":"当归","dosage":10},{"name":"陈皮","dosage":6},{"name":"升麻","dosage":6},{"name":"柴胡","dosage":6},{"name":"甘草","dosage":5}]', 1, 1, '测试医生', UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
|
|
('逍遥散', '[{"name":"柴胡","dosage":10},{"name":"当归","dosage":10},{"name":"白芍","dosage":10},{"name":"白术","dosage":10},{"name":"茯苓","dosage":10},{"name":"薄荷","dosage":6},{"name":"生姜","dosage":6},{"name":"甘草","dosage":5}]', 0, 1, '测试医生', UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
|
|
|
|
-- 3. 查询所有处方
|
|
SELECT * FROM zyt_prescription_library WHERE delete_time IS NULL;
|
|
|
|
-- 4. 查询公开的处方
|
|
SELECT * FROM zyt_prescription_library WHERE is_public = 1 AND delete_time IS NULL;
|
|
|
|
-- 5. 查询某个医生创建的处方
|
|
SELECT * FROM zyt_prescription_library WHERE creator_id = 1 AND delete_time IS NULL;
|
|
|
|
-- 6. 统计处方数量
|
|
SELECT
|
|
COUNT(*) as total,
|
|
SUM(CASE WHEN is_public = 1 THEN 1 ELSE 0 END) as public_count,
|
|
SUM(CASE WHEN is_public = 0 THEN 1 ELSE 0 END) as private_count
|
|
FROM zyt_prescription_library
|
|
WHERE delete_time IS NULL;
|
|
|
|
-- 7. 清理测试数据（可选）
|
|
-- DELETE FROM zyt_prescription_library WHERE creator_name = '测试医生';
|