每个搜索词显示一行，如果找不到则替换默认值 [英] Display one row per search term, subsitute default if not found

问题描述

查询：

SELECT product_id,
       name 
  FROM product 
 WHERE barcode in (681027, 8901030349379,
                   679046, 679047,
                   679082, 679228,
                   679230, 679235,
                   679236, 679238,
                   679328, 679330,
                   679528, 679608,
                   679609, 679647,
                   679727, 679728,
                   679730, 679808,
                   679809, 679828, 679830)

输出：

YES   -IF PRODUCT NAME IS PRESENT
NO    -IF PRODUCT NAME IS NOT PRESENT
NO
YES
YES
NO
...                    

如果特定条形码没有行，如何显示？在SQL中使用 IN 语句是一种好习惯吗？

How do I display if there is no row for the particular barcode? Is it good practice to use IN statement in sql?

我使用PostgreSQL 8.2.11。

I use PostgreSQL 8.2.11.

推荐答案

这可能有效：

select case when p.name is null then 'NO' else 'YES' end as barcode_exists, t.barcode
from product as p
right join (
    select 681027 as barcode union
    select 8901030349379 union
    select 679046 union
    select 679047 union
    select 679082 union
    select 679228 union
    select 679230 union
    select 679235 union
    select 679236 union
    select 679238 union
    select 679328 union
    select 679330 union
    select 679528 union
    select 679608
            -- all the rest barcodes
) as t on p.barcode = t.barcode

在工会中放入您要检查的所有条形码。

In union put all barcodes that you want to check.

Aded：

它将两列返回给ma tch条码和 answer ，因为除非指定了行顺序，否则您无法中继行顺序。

It returns two columns to match barcode and answer because you can't relay on rows order unless you specify one.

这篇关于每个搜索词显示一行，如果找不到则替换默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！