다음과 같이 두 개의 SQL 쿼리가 있습니다.
첫 번째 쿼리
SELECT TC.TCName,
sum(BS.BLDOS) as BLDOS,
sum(BS.CollectedAmount) as CollectedAmount,
(0.35*sum(BS.BLDOS)) as TargetAmount
FROM [Customer] C
INNER JOIN [dbo].[BillingStatus] BS ON BS.CustomerID = C.CustomerID
INNER JOIN [dbo].[TCDetails] TC ON TC.CustomerID = BS.CustomerID
WHERE TC.TCName in (Select distinct(TCName)
FROM [dbo].[TCDetails] )
GROUP BY TCName
order by [TCName] ASC
결과가 나옵니다.
||TCName || BLDOS || CollectedAmount || TargetAmount||
| Aarti | 81234.2 | 1678.76 | 789065 |
| Dev | 181234.2| 678.76 | 2389065 |
두 번째 쿼리는 다음과 같습니다.
SELECT COUNT(*) as count
FROM [dbo].[TCDetails]
where TCName in (Select distinct(TCName) FROM [dbo].[TCDetails] )
AND CallDate is NOT NULL
GROUP BY TCName
그리고 나는 얻고있다 :
||count||
231
123
이렇게 인쇄하고 싶어요
||TCName || BLDOS || CollectedAmount || TargetAmount|| count ||
| Aarti | 81234.2 | 1678.76 | 789065 | 231 |
| Dev | 181234.2| 678.76 | 2389065 | 123 |
두 쿼리 모두 wheres 절이 다르기 때문에 혼란 스럽습니다.
이 시도
SELECT * FROM
(
SELECT TC.TCName, sum(BS.BLDOS) as BLDOS, sum(BS.CollectedAmount) as CollectedAmount, (0.35*sum(BS.BLDOS)) as TargetAmount
FROM [Customer] C INNER JOIN
[dbo].[BillingStatus] BS ON BS.CustomerID = C.CustomerID INNER JOIN
[dbo].[TCDetails] TC ON TC.CustomerID = BS.CustomerID
WHERE TC.TCName in (Select distinct(TCName) FROM [dbo].[TCDetails] )
GROUP BY TCName order by [TCName] ASC
) S JOIN
(
SELECT TCName,COUNT(*) as count FROM [dbo].[TCDetails]
where TCName in (Select distinct(TCName) FROM [dbo].[TCDetails] )
AND CallDate is NOT NULL GROUP BY TCName
) T ON S.TCName=T.TCName;
의 위에:
+--------------------------------------------------------------+
|TCName | BLDOS | CollectedAmount | TargetAmount| count |
+--------------------------------------------------------------+
| Aarti | 81234.2 | 1678.76 | 789065 | 231 |
| Dev | 181234.2| 678.76 | 2389065 | 123 |
+--------------------------------------------------------------+
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다