sqlite 如果没有行返回则打印 0 [英] sqlite if no rows returned print 0

问题描述

我有以下查询:

 SELECT user_tag, device_type,  ip_address

 FROM
 devices 

 WHERE (user_tag like '|%frt%|'
 OR user_tag like '|%bck%|'
 OR user_tag like '|%ppp%|'
 OR user_tag like '|%smes%|'
 OR user_tag like '|%gm%|')

现在，如果只为 user_tag 'frt' 返回一条记录，我只会收到一行(如您所料).有没有办法为空白行设置默认值，例如，对于不返回任何实际数据的行，我会收到一个0"或类似的值?

Right now if a record is only returned for the user_tag 'frt' I only receive one row (as you would expect). Is there a way to set a default value for the blank rows, so for example I would receive a value of '0' or something similar for the rows that don't return any real data?

推荐答案

这个查询是一个草稿".由于我使用所有类型的 SQL，有时我会遇到 SQLite 特定语法的问题，而且我现在没有任何方法来测试这个查询，所以它可能需要一些语法修改.

This query is a 'rough draft'. Since I work with all types of SQL, I have trouble with SQLite-specific syntax sometimes, and I don't have any way to test this query right now, so it may need some syntax modification.

但它的想法是交叉的... 使用您的硬编码值列表，使其成为子查询并对其进行左连接而不是位置.

But it gets the idea cross... Using your list of hard-coded values, make it a subquery and do a left join on it instead of a where.

user_tag_exists 当给定类型没有任何用户标签时应为 0.

user_tag_exists should be 0 when there aren't any user tags for the type given.

Select CASE WHEN d.user_tag is null THEN '0' ELSE '1' END AS user_tag_exists,
       tagTypes.tagType,
       d.user_tag, d.device_type, d.ip_address
From
(
    Select '%frt%' as tagType
    UNION
    Select '%bck%' as tagType
    UNION
    Select '%ppp%' as tagType
    UNION
    Select '%smes%' as tagType
    UNION
    Select '%gm%' as tagType
) as tagTypes
left join
    devices d
        on d.device_type like tagTypes.tagType

这篇关于sqlite 如果没有行返回则打印 0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！