postgresql - SQL 条件语句

Question

我试图从 trans_type='RM' 的表中获取所有记录，但如果没有 trans_type='RM' 我想返回 trans_type = 'AD' 的所有记录

从技术上讲，我在 PostgreSQL 服务器上使用 xtupls MetaSQL，所以使用其中任何一个的解决方案都很棒我可以上传我的 metaSQL 语句，但我真的只需要一种方法来做

-- Group: lotserial 
-- Name: detail
-- Notes: 
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/EULA for the full text of the software license.

SELECT ls_number,
   ls_notes, 
   formatlotserialnumberbarcode(ls_number) AS lotserial_barcode,
   item_number, 
   item_descrip1, 
   item_descrip2,
   charass_char_id,
   charass_value,
   poitem_id,
   poitem_vend_item_descrip,
   char_name, 
   formatqty(itemloc_qty) as lotqty,
   lshist.*
FROM   
   itemloc, 
   ls
   JOIN item ON (item_id=ls_item_id)
   LEFT JOIN charass ON (charass_target_id=ls_id)
   LEFT JOIN "char" ON (char_id=charass_char_id),
   lshist (<? value("itemid") ?>,<? value("warehouseid") ?>,ls_number,
           <? value("pattern") ?>,<? value("transType") ?>,<? value("startDate") ?>,
           <? value("endDate") ?>,<? value("trace") ?>,1)


     LEFT JOIN pohead ON(pohead_number=(TRIM(SUBSTRING(lshist_ordernumber FROM '-.*-'),'-')))

     LEFT JOIN poitem ON(poitem_pohead_id=pohead_id)

   <? if exists('ls_id') ?>
   WHERE ls_id=<? value("ls_id") ?>
   <? endif ?>
   <? if exists('ls_number') ?>
   WHERE ls_number=<? value("ls_number") ?>
   <? endif ?>


   AND lshist_warehous_code='PS'

   <? if exists(TRIM(SUBSTRING(lshist_ordernumber FROM '.*-'),'-')='PO')?>
   AND poitem_linenumber = CAST(TRIM(SUBSTRING(lshist_ordernumber FROM '[^-]*$'),'-') AS INTEGER)
   <? endif ?>

   AND ls_id = itemloc_ls_id
   AND charass_target_type = 'LS'


 /*
   <? if exists(lshist_transtype='RM')?>
   AND lshist_transtype='RM'
   <? elseif exists(lshist_transtype='AD')?>
   AND lshist_transtype='AD'
   <? elseif exists(lshist_transtype='RL')?>
   AND lshist_transtype='RL' 
   <? elseif exists(lshist_transtype='SH')?>
   AND lshist_transtype='SH'  
   <? elseif exists(lshist_transtype='IM')?>
   AND lshist_transtype='IM'
    <? elseif exists(lshist_transtype='TR')?>
   AND lshist_transtype='TR'
   <? elseif exists(lshist_transtype='RP')?>
   AND lshist_transtype='RP' 
   <? endif ?>

Answer 1

您可以使用通用表表达式:

WITH rm AS (
  SELECT * FROM my_table WHERE trans_type = 'RM'
)
SELECT *
FROM data
UNION ALL
SELECT * FROM my_table
WHERE trans_type = 'AD'
AND NOT EXISTS (
  SELECT * FROM rm
)

这将避免在 EXPLAIN ANALYZE 调用中看到的第二次扫描，但与在客户端中做出决定相比，仍然有一些开销，这可能是由于 CTE 物化(这是特定于 PostgreSQL)。

I've benchmarked this for an small data set. There seems to be a 5% - 10% overhead in PostgreSQL over running two queries from pgplsql.所以，在大多数情况下，对于像这样的简单查询，Laurenz's solution是可取的。

可能会有更复杂的查询设置，其中单个查询比两个单独的查询更可取，因为单个查询可以重复使用中间结果。