开发者社区> 问答> 正文

Django Raw查询输出与pgAdmin输出不一致

一码平川MACHEL 2019-01-22 17:27:56 1216

我正在使用django和postgres 10 db引擎通过表单构建基于Web的搜索工具。当通过django中的原始SQL执行查询时,我在pgAdmin的Ouery工具中运行相同的SQL时只收到3条记录而不是902条。

我已经尝试使用django ORM来构建我的查询但是示例非常简单,我无法成功地将其用于我的需求。
Django Raw SQL语句

ProstateUpper = 'Prostate'
ProstateLower = 'prostate'
GenderMale = 'Male'
GenderAll = 'All'

Master_Query = Eligibilities.objects.raw('''SELECT DISTINCT ON (Eligibilities.nct_id) Eligibilities.id, Studies.brief_title, Eligibilities.nct_id, Conditions.name, Eligibilities.gender, Eligibilities.minimum_age, Eligibilities.maximum_age, Eligibilities.criteria, Interventions.intervention_type, Interventions.name, Facilities.city, Facilities.state, Facilities.country, Brief_Summaries.description, Facility_Contacts.name, Facility_Contacts.email FROM Eligibilities INNER JOIN Studies on Studies.nct_id = Eligibilities.nct_id INNER JOIN Conditions on Conditions.nct_id = Eligibilities.nct_id INNER JOIN Interventions on Interventions.nct_id = Eligibilities.nct_id INNER JOIN Facilities on Facilities.nct_id = Eligibilities.nct_id INNER JOIN Brief_Summaries on Brief_Summaries.nct_id = Eligibilities.nct_id INNER JOIN Facility_Contacts on Facility_Contacts.nct_id = Eligibilities.nct_id WHERE ((Conditions.name LIKE %s OR Conditions.name LIKE %s)) AND (gender LIKE %s OR gender LIKE %s) ''', [ProstateUpper, ProstateLower, GenderMale, GenderAll])[:1000]
pgAdmin SQL语句

SELECT DISTINCT ON (eligibilities.nct_id) eligibilities.id, studies.brief_title, eligibilities.nct_id, conditions.name, eligibilities.gender, eligibilities.minimum_age, eligibilities.maximum_age, eligibilities.criteria, interventions.intervention_type, interventions.name, facilities.city, facilities.state, facilities.country, brief_summaries.description, facility_contacts.name, facility_contacts.email
FROM ctgov.eligibilities
INNER JOIN ctgov.studies on studies.nct_id = eligibilities.nct_id
INNER JOIN ctgov.conditions on conditions.nct_id = eligibilities.nct_id
INNER JOIN ctgov.interventions on interventions.nct_id = eligibilities.nct_id
INNER JOIN ctgov.facilities on facilities.nct_id = eligibilities.nct_id
INNER JOIN ctgov.brief_summaries on brief_summaries.nct_id = eligibilities.nct_id
INNER JOIN ctgov.facility_contacts on facility_contacts.nct_id = eligibilities.nct_id
WHERE ((conditions.name LIKE '%Prostate%' OR conditions.name LIKE '%prostate%')) AND (gender LIKE '%Male%' OR gender LIKE '%All%')
;
我希望输出与记录的数量和内容相同,因为它们查询相同的源。

SQL 关系型数据库 Python
分享到
取消 提交回答
全部回答(1)
  • 一码平川MACHEL
    2019-07-17 23:26:22

    需要更改提供Where子句的变量以反映Conditions.name LIKE '%prostate%'在查询中,因此:

    ProstateUpper = '%Prostate%'
    ProstateLower = '%prostate%'
    GenderMale = '%Male%'
    GenderAll = '%All%'

    0 0
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

推荐文章
相似问题