J'ai une requête comme celle-ci:
SELECT recipientid AS ID, COUNT(*) AS Recieved FROM Inbox GROUP BY recipientid UNION SELECT SenderId, COUNT(*) AS [Sent] FROM Inbox GROUP BY SenderId
Le résultat:
RecipientID Recieved 001 3 001 4 002 4 002 2 003 18 003 55
Comment puis-je réécrire de telle sorte qu'il affiche comme ceci:
RecipientID Recieved Sent 001 3 4 002 4 2 003 18 55
Merci.
Il suffit de join les sous-requêtes:
select a.ID,Received,Sent from( SELECT recipientid AS ID, COUNT(*) AS Recieved FROM Inbox GROUP BY recipientid )a full outer join( SELECT SenderId as ID, COUNT(*) AS [Sent] FROM Inbox GROUP BY SenderId )b on (a.ID = b.ID) order by a.ID;
Notez que cela saisit toutes les valeurs sent
et received
pour tous les destinataires ou expéditeurs. Si vous souhaitez uniquement get des résultats pour les ID
appartenant aux destinataires et aux expéditeurs, effectuez une inner join
.
J'appendais une colonne source
à votre requête et ferais un simple pivot
select ID, max (case when source=1 then Cnt else 0 end) as Received, max (case when source=2 then Cnt else 0 end) as Sent from ( SELECT 1 as Source, recipientid AS ID, COUNT(*) AS Cnt FROM Inbox GROUP BY recipientid UNION SELECT 2 as Source, SenderId, COUNT(*) FROM Inbox GROUP BY SenderId ) x GROUP BY ID
Si c'est Postgres, MS SQL ou d'autres qui prennent en charge les CTE –
With Both as ( SELECT recipientid AS ID, Count(*) AS Recieved, 0 as [Sent] FROM Inbox GROUP BY recipientid UNION SELECT SenderId as ID, 0 as Recieved, Count(*) AS [Sent] FROM Inbox GROUP BY SenderId ) SELECT ID, Sum(Received) as [Received], Sum(Sent) as [Sent] FROM BOTH GROUP BY ID ORDER BY 1
En supposant que vous avez une table d' users
avec les ID, vous pouvez faire quelque chose comme:
SELECT users.id, COUNT(sent.senderid) AS sent, COUNT(received.recipientid) AS received FROM users LEFT JOIN inbox AS sent ON sent.senderid = users.id LEFT JOIN inbox AS received ON received.recipientid = users.id GROUP BY sent.senderid, received.recipientid ORDER BY users.id;