3個表
A: Aid,Serialid,Sequenceid
1 1 1
2 2 2
B: Bid,Serialid, date1, date2
1 1 1988-8-2
2 2 1988-8-2 1999-8-8
3 2 1988-8-2
C: CID,Serialid,ADDACC
1 1 8
2 2 7
我要寫一個加总 現在是這樣寫的select A.Sequenceid,C.ADDACC,COUNT(A.date1) as num1,A.Serialid FROM B LEFT JOIN A ON A.Serialid =B.Serialid
LEFT JOIN C ON C.Serialid= A.Serialid
GROUP BY A.Sequenceid,C.ADDACC,A.date1,A.Serialid
我現在想把( date1 is not null and date2 is not )的數據也 count一下 但是不知道怎么寫?怎么達到這個效果呢?
A: Aid,Serialid,Sequenceid
1 1 1
2 2 2
B: Bid,Serialid, date1, date2
1 1 1988-8-2
2 2 1988-8-2 1999-8-8
3 2 1988-8-2
C: CID,Serialid,ADDACC
1 1 8
2 2 7
我要寫一個加总 現在是這樣寫的select A.Sequenceid,C.ADDACC,COUNT(A.date1) as num1,A.Serialid FROM B LEFT JOIN A ON A.Serialid =B.Serialid
LEFT JOIN C ON C.Serialid= A.Serialid
GROUP BY A.Sequenceid,C.ADDACC,A.date1,A.Serialid
我現在想把( date1 is not null and date2 is not )的數據也 count一下 但是不知道怎么寫?怎么達到這個效果呢?
select A.Sequenceid,C.ADDACC,COUNT(A.date1) as num1,SUM(CASE WHEN (date1 is not null and date2 is not NULL) THEN 1 ELSE 0 END) AS NUM2,A.Serialid FROM B LEFT JOIN A ON A.Serialid =B.Serialid
LEFT JOIN C ON C.Serialid= A.Serialid
GROUP BY A.Sequenceid,C.ADDACC,A.date1,A.Serialid