node.js - pg-promise - 组合多个查询，即使使用 DISTINCT 关键字，也会为同一主键提供多个结果
问题描述
我有一个结合多个查询的任务，它工作正常。
db.task(t => {
const a = studies => t.any ('SELECT facility_contacts.contact_type, facility_contacts.name, facility_contacts.email, facility_contacts.phone FROM facility_contacts WHERE facility_contacts.nct_id = $1', studies.nct_id).then(facility_contacts => {
studies.facility_contacts = facility_contacts;
return studies;
});
const b = studies => t.any ('SELECT eligibilities.criteria, eligibilities.gender FROM eligibilities WHERE eligibilities.nct_id = $1', studies.nct_id).then(eligibilities => {
studies.eligibilities = eligibilities;
return studies;
});
const c = studies => t.any ('SELECT interventions.intervention_type, interventions.name, interventions.description FROM interventions WHERE interventions.nct_id = $1', studies.nct_id).then(interventions => {
studies.interventions = interventions;
return studies;
});
const d = studies => t.any ('SELECT design_groups.group_type, design_groups.title, design_groups.description FROM design_groups WHERE design_groups.nct_id = $1', studies.nct_id).then(design_groups => {
studies.design_groups = design_groups;
return studies;
});
const e = studies => t.batch([a(studies), b(studies), c(studies), d(studies)]);
return t.map('SELECT DISTINCT ON (facilities.nct_id) studies.nct_id, studies.phase, studies.enrollment, studies.overall_status, facilities.city FROM studies INNER JOIN facilities ON facilities.nct_id = studies.nct_id AND facilities.country LIKE \'%Ireland%\' ORDER BY facilities.nct_id LIMIT 20 OFFSET ($1 - 1) * 20', [page],e).then(t.batch);
}).then(studies => {
res.send(studies);
}).catch(error => {
console.log(error);
});
但是在响应中，我多次获得相同的值，并且我已经单独尝试了查询，并且响应没有任何重复。我目前的回复如下（仅显示前 3 个）：
{
"nct_id": "NCT00002755",
"phase": "Phase 3",
"enrollment": 600,
"overall_status": "Completed",
"city": "Dublin",
"facility_contacts": [],
"eligibilities": [
{
"criteria": "\n DISEASE CHARACTERISTICS: Histologically confirmed stage II or IIIA breast cancer with at\n least 4 positive axillary nodes Definitive resection required, preferably within 4 weeks\n prior to entry No overt residual axillary nodal carcinoma after surgery Hormone receptor\n status: Not specified\n\n PATIENT CHARACTERISTICS: Age: Over 18 Sex: Female Menopausal status: Not specified\n Performance status: ECOG 0 or 1 Hematopoietic: Absolute neutrophil count greater than\n 1,500/mm3 Platelet count greater than 100,000/mm3 Hemoglobin greater than 9 g/dL PT and\n aPTT normal Hepatic: Bilirubin normal (unless benign congenital hyperbilirubinemia) Normal\n liver biopsy required in patients with active hepatitis B or C Renal: Creatinine normal\n Cardiovascular: No active heart disease Normal wall motion on MUGA or echocardiogram Other:\n Adequate nutritional status (i.e., more than 1,000 calories/day orally) HIV negative No\n serious medical or psychiatric disease No second malignancy except: Basal cell skin cancer\n Carinoma in situ of the cervix Not pregnant Negative pregnancy test\n\n PRIOR CONCURRENT THERAPY: At least 2 weeks since major surgery\n ",
"gender": "Female"
}
],
"interventions": [
{
"intervention_type": "Biological",
"name": "filgrastim",
"description": null
},
{
"intervention_type": "Drug",
"name": "CMF regimen",
"description": null
},
{
"intervention_type": "Drug",
"name": "cyclophosphamide",
"description": null
},
{
"intervention_type": "Drug",
"name": "doxorubicin hydrochloride",
"description": null
},
{
"intervention_type": "Drug",
"name": "fluorouracil",
"description": null
},
{
"intervention_type": "Drug",
"name": "methotrexate",
"description": null
},
{
"intervention_type": "Drug",
"name": "tamoxifen citrate",
"description": null
},
{
"intervention_type": "Drug",
"name": "thiotepa",
"description": null
},
{
"intervention_type": "Procedure",
"name": "autologous bone marrow transplantation",
"description": null
},
{
"intervention_type": "Procedure",
"name": "peripheral blood stem cell transplantation",
"description": null
},
{
"intervention_type": "Radiation",
"name": "low-LET cobalt-60 gamma ray therapy",
"description": null
},
{
"intervention_type": "Radiation",
"name": "low-LET electron therapy",
"description": null
},
{
"intervention_type": "Radiation",
"name": "low-LET photon therapy",
"description": null
}
],
"design_groups": []
},
{
"nct_id": "NCT00002755",
"phase": "Phase 3",
"enrollment": 600,
"overall_status": "Completed",
"city": "Dublin",
"facility_contacts": [],
"eligibilities": [
{
"criteria": "\n DISEASE CHARACTERISTICS: Histologically confirmed stage II or IIIA breast cancer with at\n least 4 positive axillary nodes Definitive resection required, preferably within 4 weeks\n prior to entry No overt residual axillary nodal carcinoma after surgery Hormone receptor\n status: Not specified\n\n PATIENT CHARACTERISTICS: Age: Over 18 Sex: Female Menopausal status: Not specified\n Performance status: ECOG 0 or 1 Hematopoietic: Absolute neutrophil count greater than\n 1,500/mm3 Platelet count greater than 100,000/mm3 Hemoglobin greater than 9 g/dL PT and\n aPTT normal Hepatic: Bilirubin normal (unless benign congenital hyperbilirubinemia) Normal\n liver biopsy required in patients with active hepatitis B or C Renal: Creatinine normal\n Cardiovascular: No active heart disease Normal wall motion on MUGA or echocardiogram Other:\n Adequate nutritional status (i.e., more than 1,000 calories/day orally) HIV negative No\n serious medical or psychiatric disease No second malignancy except: Basal cell skin cancer\n Carinoma in situ of the cervix Not pregnant Negative pregnancy test\n\n PRIOR CONCURRENT THERAPY: At least 2 weeks since major surgery\n ",
"gender": "Female"
}
],
"interventions": [
{
"intervention_type": "Biological",
"name": "filgrastim",
"description": null
},
{
"intervention_type": "Drug",
"name": "CMF regimen",
"description": null
},
{
"intervention_type": "Drug",
"name": "cyclophosphamide",
"description": null
},
{
"intervention_type": "Drug",
"name": "doxorubicin hydrochloride",
"description": null
},
{
"intervention_type": "Drug",
"name": "fluorouracil",
"description": null
},
{
"intervention_type": "Drug",
"name": "methotrexate",
"description": null
},
{
"intervention_type": "Drug",
"name": "tamoxifen citrate",
"description": null
},
{
"intervention_type": "Drug",
"name": "thiotepa",
"description": null
},
{
"intervention_type": "Procedure",
"name": "autologous bone marrow transplantation",
"description": null
},
{
"intervention_type": "Procedure",
"name": "peripheral blood stem cell transplantation",
"description": null
},
{
"intervention_type": "Radiation",
"name": "low-LET cobalt-60 gamma ray therapy",
"description": null
},
{
"intervention_type": "Radiation",
"name": "low-LET electron therapy",
"description": null
},
{
"intervention_type": "Radiation",
"name": "low-LET photon therapy",
"description": null
}
],
"design_groups": []
},
{
"nct_id": "NCT00002755",
"phase": "Phase 3",
"enrollment": 600,
"overall_status": "Completed",
"city": "Dublin",
"facility_contacts": [],
"eligibilities": [
{
"criteria": "\n DISEASE CHARACTERISTICS: Histologically confirmed stage II or IIIA breast cancer with at\n least 4 positive axillary nodes Definitive resection required, preferably within 4 weeks\n prior to entry No overt residual axillary nodal carcinoma after surgery Hormone receptor\n status: Not specified\n\n PATIENT CHARACTERISTICS: Age: Over 18 Sex: Female Menopausal status: Not specified\n Performance status: ECOG 0 or 1 Hematopoietic: Absolute neutrophil count greater than\n 1,500/mm3 Platelet count greater than 100,000/mm3 Hemoglobin greater than 9 g/dL PT and\n aPTT normal Hepatic: Bilirubin normal (unless benign congenital hyperbilirubinemia) Normal\n liver biopsy required in patients with active hepatitis B or C Renal: Creatinine normal\n Cardiovascular: No active heart disease Normal wall motion on MUGA or echocardiogram Other:\n Adequate nutritional status (i.e., more than 1,000 calories/day orally) HIV negative No\n serious medical or psychiatric disease No second malignancy except: Basal cell skin cancer\n Carinoma in situ of the cervix Not pregnant Negative pregnancy test\n\n PRIOR CONCURRENT THERAPY: At least 2 weeks since major surgery\n ",
"gender": "Female"
}
],
"interventions": [
{
"intervention_type": "Biological",
"name": "filgrastim",
"description": null
},
{
"intervention_type": "Drug",
"name": "CMF regimen",
"description": null
},
{
"intervention_type": "Drug",
"name": "cyclophosphamide",
"description": null
},
{
"intervention_type": "Drug",
"name": "doxorubicin hydrochloride",
"description": null
},
{
"intervention_type": "Drug",
"name": "fluorouracil",
"description": null
},
{
"intervention_type": "Drug",
"name": "methotrexate",
"description": null
},
{
"intervention_type": "Drug",
"name": "tamoxifen citrate",
"description": null
},
{
"intervention_type": "Drug",
"name": "thiotepa",
"description": null
},
{
"intervention_type": "Procedure",
"name": "autologous bone marrow transplantation",
"description": null
},
{
"intervention_type": "Procedure",
"name": "peripheral blood stem cell transplantation",
"description": null
},
{
"intervention_type": "Radiation",
"name": "low-LET cobalt-60 gamma ray therapy",
"description": null
},
{
"intervention_type": "Radiation",
"name": "low-LET electron therapy",
"description": null
},
{
"intervention_type": "Radiation",
"name": "low-LET photon therapy",
"description": null
}
],
"design_groups": []
}
即使使用了 DISTINCT 关键字，我也会多次得到相同的结果。我该如何解决这个问题？这是我的查询排序的一些问题吗？
解决方案
推荐阅读
- json - 不同 Json 结构的 Yew 回调
- java - 可以停止（返回）从超级方法继承的类的执行吗？
- python-3.x - 从 FastAPI 中的后台任务获取返回状态
- json - 什么可以使这段代码实现我在颤动中可视化这个饼图的目标？
- javascript - 在 Javascript 中，我希望代码只返回在 HTML 的不同行上具有相同 ID 的名称
- c# - Oracle 数据访问管理器异常
- flutter - URI 中未指定主机
- zurb-foundation - select2 选项列表在 Foundation 模式中更大程度地隐藏
- python-3.x - “utf-8”编解码器无法解码字节 0xd7：无效的继续字节
- sql - 按 3 列分区并找到最大值