116 lines
3.9 KiB
Python
116 lines
3.9 KiB
Python
import psycopg2
|
|
import sys
|
|
|
|
sys.stdout.reconfigure(encoding="utf-8")
|
|
|
|
conn = psycopg2.connect(
|
|
host="192.168.110.252",
|
|
port=15432,
|
|
database="postgresql",
|
|
user="postgresql",
|
|
password="Jchl1528",
|
|
)
|
|
|
|
cursor = conn.cursor()
|
|
cursor.execute("SET search_path TO hisdev, public")
|
|
|
|
print("检查SQL查询的列顺序...")
|
|
print()
|
|
|
|
cursor.execute("""
|
|
SELECT
|
|
3 AS advice_type, -- 0
|
|
'test-id' AS request_id, -- 1
|
|
'test-key' AS unique_key, -- 2
|
|
123 AS requester_id, -- 3
|
|
NOW() AS request_time, -- 4
|
|
'1' AS biz_request_flag, -- 5
|
|
'{}' AS content_json, -- 6
|
|
null AS skin_test_flag, -- 7
|
|
null AS inject_flag, -- 8
|
|
null AS group_id, -- 9
|
|
'test-name' AS advice_name, -- 10
|
|
'' AS volume, -- 11
|
|
'' AS lot_number, -- 12
|
|
1 AS quantity, -- 13
|
|
'次' AS unit_code, -- 14
|
|
1 AS status_enum, -- 15
|
|
'' AS method_code, -- 16
|
|
'' AS rate_code, -- 17
|
|
NULL AS dose, -- 18
|
|
'' AS dose_unit_code, -- 19
|
|
'ci-id' AS charge_item_id, -- 20
|
|
100 AS total_price, -- 21
|
|
1 AS charge_status, -- 22
|
|
'pos-id' AS position_id, -- 23
|
|
'pos-name' AS position_name, -- 24
|
|
null AS dispense_per_duration, -- 25
|
|
1 AS part_percent, -- 26
|
|
'' AS condition_definition_name, -- 27
|
|
COALESCE(null, 2) AS therapyEnum, -- 28 <- 这里
|
|
99 AS sort_number, -- 29
|
|
null AS based_on_id -- 30
|
|
""")
|
|
|
|
row = cursor.fetchone()
|
|
print(f"therapyEnum 列索引: 28, 值: {row[28]}")
|
|
print()
|
|
|
|
# 现在用实际的SQL查询
|
|
cursor.execute("""
|
|
SELECT
|
|
3 AS advice_type,
|
|
T1.id AS request_id,
|
|
T1.id || '-3' AS unique_key,
|
|
T1.requester_id AS requester_id,
|
|
T1.create_time AS request_time,
|
|
CASE WHEN T1.requester_id = 1980296166230962178 THEN '1' ELSE '0' END AS biz_request_flag,
|
|
T1.content_json AS content_json,
|
|
null AS skin_test_flag,
|
|
null AS inject_flag,
|
|
null AS group_id,
|
|
COALESCE(T2.NAME, T1.content_json::jsonb->>'surgeryName') AS advice_name,
|
|
'' AS volume,
|
|
'' AS lot_number,
|
|
T1.quantity AS quantity,
|
|
T1.unit_code AS unit_code,
|
|
T1.status_enum AS status_enum,
|
|
'' AS method_code,
|
|
'' AS rate_code,
|
|
NULL AS dose,
|
|
'' AS dose_unit_code,
|
|
T3.id AS charge_item_id,
|
|
T3.total_price AS total_price,
|
|
T3.status_enum AS charge_status,
|
|
ao.id AS position_id,
|
|
ao.name AS position_name,
|
|
null AS dispense_per_duration,
|
|
1 AS part_percent,
|
|
'' AS condition_definition_name,
|
|
COALESCE(T1.therapy_enum, 2) AS therapyEnum,
|
|
99 AS sort_number,
|
|
T1.based_on_id AS based_on_id
|
|
FROM wor_service_request AS T1
|
|
LEFT JOIN wor_activity_definition AS T2
|
|
ON T2.ID = T1.activity_id AND T2.delete_flag = '0'
|
|
LEFT JOIN adm_charge_item AS T3 ON T3.service_id = T1.ID AND T3.delete_flag = '0' AND
|
|
T3.service_table = 'wor_service_request'
|
|
LEFT JOIN adm_organization AS ao ON ao.ID = T1.org_id AND ao.delete_flag = '0'
|
|
WHERE T1.delete_flag = '0' AND T1.generate_source_enum = 1
|
|
AND T1.parent_id IS NULL
|
|
AND T1.encounter_id = 2038823905749327873
|
|
AND T1.category_enum = 4
|
|
""")
|
|
|
|
row = cursor.fetchone()
|
|
if row:
|
|
print("实际查询结果:")
|
|
print(f" advice_name (索引10): {row[10]}")
|
|
print(f" therapyEnum (索引28): {row[28]}")
|
|
print(f" status_enum (索引15): {row[15]}")
|
|
else:
|
|
print("未找到记录")
|
|
|
|
cursor.close()
|
|
conn.close()
|