SELECT count(*)然后输出空或不为空，是或否 [英] SELECT count(*) then output empty or not empty with yes or no

问题描述

查找SALES部门的位置与TRANSPORT部门在完全相同的城市中"(假设空结果表示YES，任何非空结果表示NO)

Find if SALES department has its locations in exactly the same cities as TRANSPORT department"(assume that an empty result means YES and any nonempty result means NO)

在我的数据库中有此表.

In my database got this table.

//DEPTLOC
DNAME      CITY
----------------
SALES     LONDON
TRANSPORT LONDON
TRANSPORT BOSTON
SCIENCE   BOSTON

不确定如何编写查询，但至少我会尝试

Not sure how to write the query but at least i try

SELECT COUNT(*) FROM DEPTLOC WHERE 
     (DNAME='SALES' AND DNAME='TRANSPORT') AND 

我应该如何在AND运算符之后编写sql以获得类似这样的输出

How should i write after the AND operator in sql in order to get output something like this

//Display
YES . (<--- LONDON , got SALES and TRANSPORT in same cities)

推荐答案

您可以使用解码

SELECT Decode(Count(*), 0, 'No', 
                        'Yes') 
FROM   {rest of your query}

DECODE将expr与每个搜索值一一比较.如果expr等于搜索，则Oracle数据库返回相应的结果.如果找不到匹配项，则Oracle返回默认值.如果省略默认值，则Oracle返回null.

DECODE compares expr to each search value one by one. If expr is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null.

这篇关于SELECT count(*)然后输出空或不为空，是或否的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！