sql - SQL Server 2005 有趣且具有挑战性的自连接问题

Question

我有这个名为 OrdersToCall 的表

数据类型:所有 bigints，除了 date 是一个日期时间

|-Order Num-|----Date--- |- Primary Ph -| Secondary Ph | Alternate Ph|----101----| 02-07-2010 | 925-515-1234 | 916-515-1234 |  707-568-5778  |----102----| 02-07-2010 | 925-888-4141 | 925-888-4141 |  000-000-0000|----103----| 02-07-2010 | 000-000-0000 | 000-000-0000 |  510-555-4575  |----104----| 02-07-2010 | 415-789-5454 | 415-707-5588 |  735-874-9566|----105----| 02-07-2010 | 925-887-7979 | 925-887-7979 |  925-887-7979

and I have another table named PhoneNumCalled

|-AgentID-|----Date----|-Dialed Number||-145564--| 02-07-2010 | 925-515-1234 ||-145564--| 02-07-2010 | 707-568-5778 ||-145566--| 02-07-2010 | 925-888-4141 ||-145567--| 02-07-2010 | 510-555-4575 ||-145568--| 02-07-2010 | 415-789-5454 ||-145568--| 02-07-2010 | 415-707-5588 ||-145568--| 02-07-2010 | 735-874-9566 ||-145570--| 02-07-2010 | 925-887-7979 ||-145570--| 02-07-2010 | 925-887-7979 |

Now my challenge is: I want to count how many Order Num were called and create a table based off the results.

So for example if agent 1234 called all 3 numbers on 1 order that would still only count as 1 order for that agent. The ratio is 1:1. Once a phone number is called then it is counted as 1 order. No matter if all 3 were called, an agent only has to call 1 of phone numbers to get credit for the order.

In less than 3 months time I already have almost 1/2 a million records so try to be as space conscious as possible.

My solution (Which I wish to revise with your help):
I ended up creating a stored procedure which:

--Delete and recreate the CombinedData table created yesterday
Insert into the CombinedData table
Select Order Num, Date, Primary Ph as Phone
 from OrdersToCall
Union
Select Order Num, Date, Secondary Ph as Phone
 from OrdersToCall
Union
Select Order Num, Date, Alternate Ph as Phone
 from OrdersToCall
Delete from the CombinedData table
 where phone in ('000-000-0000', '999-999-9999')

现在这不仅会创建一个新表，而且由于每个订单中的每个电话号码现在都是它自己的行，因此该表变得非常庞大，最多需要 2 分钟才能创建。

然后我从这个表中导出计数并将它们存储在另一个表中。

Answer 1

我想这就是您要找的:

SELECT c.AgentId, COUNT(DISTINCT o.[Order Num]) AS [Orders per Agent]
FROM OrdersToCall o
JOIN PhoneNumCalled c ON c.[Dialed Number] = o.[Primary Ph]
                      OR c.[Dialed Number] = o.[Secondary Ph]
                      OR c.[Dialed Number] = o.[Alternate Ph]
GROUP BY c.AgentId

如果您想知道每个日期打了多少电话，您还必须加入该日期:

SELECT c.AgentId, c.Date, COUNT(DISTINCT o.[Order Num]) AS [Orders per Agent]
FROM OrdersToCall o
JOIN PhoneNumCalled c ON (c.[Dialed Number] = o.[Primary Ph]
                      OR c.[Dialed Number] = o.[Secondary Ph]
                      OR c.[Dialed Number] = o.[Alternate Ph])
                      AND o.Date = c.Date
GROUP BY c.AgentId, c.Date