代码之家 › 专栏 › 技术社区 › Robert Green MBA

确定值大于0的列的计数

having common-table-expression aggregate subquery tsql

Robert Green MBA · 技术社区 · 6 年前

好的,伙计们。。。

这是我想要的最终结果

n (3) 子查询。。。这并不理想。。。

declare @userId int = 436
select
    (select count(PRODUCT_ID) from sl_suggested_asset where PRODUCT_ID > 0 and user_id = @userId) As 'PRODUCT_ID',
    (select count(DIGI_DOC_ID) from sl_suggested_asset where DIGI_DOC_ID > 0 and user_id = @userId) As 'DIGI_DOC_ID',
    (select count(QR_CODE_ID) from sl_suggested_asset where qr_code_id > 0  and user_id = @userId) As 'QR_CODE_ID'

cte 或者一个 aggregate ...

select count(product_id), count(DIGI_DOC_ID), count(qr_code_id), user_id
from sl_suggested_asset
where user_id = 436
group by user_id

但这给了我行的总数。。。

所以我的 having 应该使用子句,但这意味着我需要有适当的 group by predicate ... 这就是我被困的地方。。。

select 
    count(PRODUCT_ID) As 'PRODUCT_ID'
    , count(DIGI_DOC_ID) As 'DIGI_DOC_ID'
    , count(QR_CODE_ID) As 'QR_CODE_ID'
    , user_id
from sl_suggested_asset
where 
    PRODUCT_ID > 0
or
    DIGI_DOC_ID > 0
or
    QR_CODE_ID > 0
and
    user_id = 436
group by user_id

我得到。。。

... 在这里我改变了 where 子句条件。。。

select 
    count(PRODUCT_ID) As 'PRODUCT_ID'
    , count(DIGI_DOC_ID) As 'DIGI_DOC_ID'
    , count(QR_CODE_ID) As 'QR_CODE_ID'
    , user_id
from sl_suggested_asset
where 
    user_id = 436
and
    PRODUCT_ID > 0
or
    DIGI_DOC_ID > 0
or
    QR_CODE_ID > 0  
group by user_id

... 我得到了和上面一样的结果。。。

哪里 = 0 得到相同的结果,也就是子句被完全忽略。。。

select 
    count(PRODUCT_ID) As 'PRODUCT_ID'
    , count(DIGI_DOC_ID) As 'DIGI_DOC_ID'
    , count(QR_CODE_ID) As 'QR_CODE_ID'
    , user_id
from sl_suggested_asset
where 
    user_id = 436
and
    PRODUCT_ID = 0
or
    DIGI_DOC_ID = 0
or
    QR_CODE_ID = 0  
group by user_id

... 最后,如果我改变 or s到 and 如果我没有结果。。。

select 
    count(PRODUCT_ID) As 'PRODUCT_ID'
    , count(DIGI_DOC_ID) As 'DIGI_DOC_ID'
    , count(QR_CODE_ID) As 'QR_CODE_ID'
    , user_id
from sl_suggested_asset
where 
    user_id = 436
and
    PRODUCT_ID = 0
and
    DIGI_DOC_ID = 0
and
    QR_CODE_ID = 0  
group by user_id

1 回复 | 直到 6 年前

Wiston Silva 6 年前

SELECT SUM( CASE WHEN PRODUCT_ID > 0 THEN 1 ELSE 0 END ) as PRODUCT_ID , SUM( CASE WHEN DIGI_DOC_ID>0 THEN 1 ELSE 0 END ) as DIGI_DOC_ID, SUM( CASE WHEN QR_CODE_ID>0 THEN 1 ELSE 0 END ) as QR_CODE_ID FROM sl_SUGGESTED_ASSET WHERE USER_ID =436

祝你有美好的一天!