文章/答案/技术大牛

发布

问使用perl的Psql查询
EN

Stack Overflow用户

提问于 2013-08-19 09:41:32

回答 2查看 58关注 0票数 0

我正在写一个perltk，它与数据库交互。我希望在我的查询中有一个特定的条件，即字段myo_maps_study应该匹配regexMYO0-9*\$(如果存在myo_maps_study )。它可能永远不存在于某些行。如果myo_maps_study不存在，我仍然希望得到结果

下面是我当前的查询，它只检查MYO0-9*\$ regex。

my $query = "
      SELECT wtg_uid, pat_first_name, pat_last_name, pat_chi_no, 
        wtg_scantype, wtg_confirmed, 
        std_in_out_patient, 
        inc_uid, wtg_appeared,
        myo_maps_study, myx_event,
        inc_incident_required, inc_incident_done, 
        inc_gp_letter_required, inc_gp_letter_done,
        inc_reappt_required, inc_reappt_done,
        inc_comment
        FROM ((waiting INNER JOIN patients ON (wtg_pat_uid = pat_uid)) 
        LEFT OUTER JOIN studies ON (wtg_uid = std_wtg_uid)
        LEFT OUTER JOIN myx ON (std_uid = myx_std_uid)
        LEFT OUTER JOIN myo on (std_uid = myo_std_uid))
        LEFT OUTER JOIN incidents ON (wtg_uid = inc_wtg_uid)
        WHERE wtg_scan_date = '$date' AND myo_maps_study ~ 'MYO[0-9]*\$' 
        ORDER BY pat_last_name;";

17291 | AR         | MAX       | 1609 | E-R AND RNVG |               | O                  |         | Y            | MYO130430      |   
        |                       |                   |                        |                    |                     |                 | 
   17201 | ANN        | MCK       | 3011 | E-R AND RNVG |               | O                  |         | Y            | MYO134416      |   
        |                       |                   |                        |                    |                     |                 |

当我删除myo_maps_study条件时

SELECT wtg_uid, pat_first_name, pat_last_name, pat_chi_no,wtg_scantype, wtg_confirmed, std_in_out_patient, inc_uid, wtg_appeared, myo_maps_study, myx_event,inc_incident_required, inc_incident_done,inc_gp_letter_required, inc_gp_letter_done,inc_reappt_required, inc_reappt_done,inc_comment FROM ((waiting INNER JOIN patients ON (wtg_pat_uid = pat_uid))  LEFT OUTER JOIN studies ON (wtg_uid = std_wtg_uid) LEFT OUTER JOIN myx ON (std_uid = myx_std_uid) LEFT OUTER JOIN myo on (std_uid = myo_std_uid)) LEFT OUTER JOIN incidents ON (wtg_uid = inc_wtg_uid) WHERE wtg_scan_date = '19/08/13' ORDER BY pat_last_name;

我得到了

 17264 | KIS       | ASH        | 150 | E-R AND RNVG |               |                    |         | Y            |                |   
        |                       |                   |                        |                    |                     |                 | 
   17262 | WIL        | BE          | 1301 | E-R AND RNVG |               |                    |         |              |                |   
        |                       |                   |                        |                    |                     |                 | 
   17268 | ELI      | HAR       | 2105 | E-R AND RNVG |               |                    |         |              |                |   
        |                       |                   |                        |                    |                     |                 | 
   17291 | AR         | MAX       | 1609 | E-R AND RNVG |               | O                  |         | Y            | MYO130430T     |   
        |                       |                   |                        |                    |                     |                 | 
   17291 | AR         | MAX       | 1609 | E-R AND RNVG |               | O                  |         | Y            | MYO130430      |   
        |                       |                   |                        |                    |                     |                 | 
   17201 | ANN        | MCK       | 3011 | E-R AND RNVG |               | O                  |         | Y            | MYO134416T     |   
        |                       |                   |                        |                    |                     |                 | 
   17201 | ANN        | MCK       | 3011 | E-R AND RNVG |               | O                  |         | Y            | MYO134416      |   
        |                       |                   |                        |                    |                     |                 | 
   17351 | SHI        | MUL       | 2907 | R-R ONLY     |               |                    |         |              |                |   
        |                       |                   |                        |

注意，在第二个查询中，AR MAX和ANN MCK出现了两次。我想要的输出介于两者之间。我希望每个人只出现一次与每个查询。也就是说，如果存在一个myo_maps_study，它必须是格式为MYO[0-9]*\$的那个。对于那些没有myo_maps_study的人，我希望输出行的字段为空

我该怎么做？

sql

perl

postgresql

回答 2

Stack Overflow用户

回答已采纳

发布于 2013-08-19 12:00:10

select distinct on (pat_last_name, pat_first_name)
    wtg_uid, pat_first_name, pat_last_name, pat_chi_no, 
    wtg_scantype, wtg_confirmed, 
    std_in_out_patient, 
    inc_uid, wtg_appeared,
    myo_maps_study, myx_event,
    inc_incident_required, inc_incident_done, 
    inc_gp_letter_required, inc_gp_letter_done,
    inc_reappt_required, inc_reappt_done,
    inc_comment
from
    (
        waiting
        inner join
        patients on wtg_pat_uid = pat_uid
        left outer join
        studies on wtg_uid = std_wtg_uid
        left outer join
        myx on std_uid = myx_std_uid
        left outer join
        myo on std_uid = myo_std_uid
    )
    left outer join incidents on wtg_uid = inc_wtg_uid
where
    wtg_scan_date = '$date'
    and
    (myo_maps_study ~ 'myo[0-9]*\$'  or myo_maps_study is null)
order by pat_last_name

票数 1

Stack Overflow用户

发布于 2013-08-19 10:03:40

换个

AND myo_maps_study ~ 'MYO[0-9]*\$'

通过

AND (myo_maps_study ~ 'MYO[0-9]*\$' OR myo_maps_study = '')

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/18310732

复制

相似问题

问使用perl的Psql查询
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用perl的Psql查询EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用perl的Psql查询
EN