如何基于两个条件获取COUNT(*) [英] How to get the COUNT(*) based on two conditions

问题描述

我无法根据条件获取count(*).

I am having trouble in getting the count(*) based on the condition.

以下是我的数据

id | user_id | key        | value
---+---------+------------+-------------------------
 1 |    3434 | first_name | Brandon
 2 |    3434 | last_name  | Johnson,Brett,Jack
 3 |    3434 | street_add | 123 main
 4 |    3434 | city       | ocean beach
 5 |    3434 | state      | Texas

我的查询是

SELECT
    COUNT(*)
from
    CUSTOMER c
where
    c.key = 'last_name'
    and
    c.value;

我无法将c.value传递给类似c.value = Johnson,Brett,Jack的查询，因为它每次都会随机更改.因此，它必须是通用的.

I can't pass c.value to the query like c.value = Johnson,Brett,Jack as it can change randomly each time. So it has to be generic.

我希望该值应该为3，而Johnson，Brett，Jack的值为3.但是我每次都会得到不同的错误.

I am expecting the value should be 3 it has 3 value Johnson,Brett,Jack. But I keep getting different errors each time.

第二个问题

我也想将其分配给如下所示的变量

I also want to assign it to a variable Like below

DECLARE
idNumber PLS_INTEGER;

BEGIN

    Select
        COUNT(*)
    into
        idNumber
    from 
        CUSTOMER c
    where
        c.key = 'last_name'
        and
        c.value;

    DBMS_OUTPUT.PUT_LINE('ID NUMBER ' || idNumber);

END;

推荐答案

我相信您需要类似的东西

I believe you need something like

select count(*)
  from customer c
 where c.key = 'lastName'
   and c.value in ('Johnson', 'Brett', 'Jack')

，因此您可以计算所有customers的姓氏，包括Johnson，Brett和Jack.

so you can count all the customers with last names including Johnson, Brett and Jack.

在使用当前数据模型时，如果要查找具有特定firstName和lastName的所有人，则必须将customer表的两个实例作为

With you current data model, if you want to find all people with a specific firstName and lastName you must join two instances of customer table as

select count(*)
  from customer c1
  join customer c2 on c1.user_id = c2.user_id
 where c1.key = 'firstName'
   and c2.key = 'lastName'
   and c1.value = 'John'
   and c2.value = 'Doe'

，如果您需要搜索更多字段，它将变得更加复杂.

and it goes more complex if you need to search upon more fields.

顺便说一句，为什么在 RDBMS 中使用这样的数据模型?如果您有一个 schema-less 模型，那么为什么不使用Mongo这样的NoSQL数据库呢?

By the way, why do you use such a data-model in an RDBMS? If you have an schema-less model, why don't you use a NoSQL database such as Mongo?

这篇关于如何基于两个条件获取COUNT(*)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！