用内连接sql查询计数

Hi all we have got two tables "Subscriptioninfo , activationinfo" and the structure is like this for the two tables

大家好我们有两个表“Subscriptioninfo,activationinfo”,这两个表的结构是这样的

Subscriptioninfo table

   cert_id (int, PK)
   customer_email(string)
   subscription_key(string)
   activated_on (datetime)

activationinfo Table:

    cert_id (int)
    activationcode(string)
    subscription_key(string , FK)
    activated_ts(datetime)

the below query will give the count as "one" if an entry to Subscriptioninfo with the same year and customer email ID (this is fine)

如果具有相同年份和客户电子邮件ID的Subscriptioninfo条目,则以下查询将计数为“一”(这很好)

SELECT COUNT(*) FROM Subscriptioninfo WITH (NOLOCK)
WHERE year(activated_On) = year(getdate()) AND customer_email =@CustomerEmail

we have one sp that will insert the data into both the tables (i.e) one entry to Subscriptioninfo where as four entries to activationinfo

我们有一个sp将数据插入到两个表中(即)一个条目到Subscriptioninfo,其中有四个条目到activationinfo

I need to return count as "0" if one entry to subscriptioninfo and two entries to activationinfo

如果对subscriptioninfo有一个条目,对activationinfo有两个条目,我需要将count返回为“0”

and i need to return count as "1" if one entry to subscriptioninfo and four entries to activationinfo..

我需要返回count为“1”,如果一个条目的subscriptioninfo和四个条目到activationinfo ..

Would any one please help on this query how can i get that count with join clause..

请问有谁请帮助这个查询如何通过join子句得到那个计数..

Many thanks in advance...

提前谢谢了...

tried with this query but it is giving the activationinfo entry count(i.e) 4 instead of 1

尝试使用此查询,但它给予activationinfo条目计数(即)4而不是1

 SELECT COUNT(*) FROM subscriptioninfo csi join activationinfo aci on csi.subscription_key = aci.subscription_key
 WHERE year(Activated_On)  = year(getdate()) AND customer_email = 'xxx@cc.com' group by csi.subscription_key

2 个解决方案

#1

Use CASE statements

使用CASE语句

SELECT CASE WHEN COUNT(*) = 4 THEN 1
            WHEN COUNT(*) < 4 THEN 0
       END CountResults
FROM subscriptioninfo csi 
join activationinfo aci on csi.subscription_key = aci.subscription_key
WHERE year(Activated_On)  = year(getdate()) AND customer_email = 'xxx@cc.com' group by csi.subscription_key

#2

i just tried to be different and may performance is better,

我只是试着变得不同,可能表现更好,

;WITH CTE
AS (
    SELECT *
        ,row_number() OVER (
            ORDER BY cert_id
            ) rn
    FROM @activationinfo A
    )
SELECT *
    ,1 AS CountResult
FROM @Subscriptioninfo S
WHERE EXISTS (
        SELECT cert_id
        FROM CTE A
        WHERE s.cert_id = a.cert_id
            AND rn = 4
        )

UNION ALL

SELECT *
    ,0 AS CountResult
FROM @Subscriptioninfo S
WHERE EXISTS (
        SELECT cert_id
        FROM CTE A
        WHERE s.cert_id = a.cert_id
            AND rn < 4
        )

#1