将多行SQL放入单行，但并不总是这样

Question

我有一个实时数据库，可以显示呼叫中心的代理登录时的情况。代理可以作为语音代理或聊天代理登录，或者两者都登录，每个登录都显示在单独的行上；

如果她都登录了；

 AgentID | State    |ReasonCode  |LoginDur  |StateDur       |Domain
 --------------------------------------------------------------------
   1301    | NotReady   |Lunch       |01:23:12  |00:23:21       |1
   1301    | Ready      |Undefined   |00:57:12  |00:00:12       |5

域1用于语音登录，5用于聊天登录和AgentID是公分母。现在，我想要做的是将两者结合在一起；

 AgtID | V-State |V-ReCode |V-LogDur |V-StDur |C-State|C-RCode  |C-LogDur  |C-StDur |
-------------------------------------------------------------------------------------------
1301    |NotReady|Lunch    |01:23:12 |00:23:21|Ready  |Undefined|00:57:12  |00:00:12

如果代理以语音和聊天代理的身份登录，我可以使用下面的查询来实现这一点；

    SELECT AgentID, V-State, V-StDur, V-LogDur, V-RCode, C-State, C-StDur, C-LogDur, C-RCode 
FROM(SELECT
ART1.AgentID AS AgentID,
ART1.AgtState AS V-State,
ART2.AgtState AS C-State,
ART1.StateDur AS V-StDur,
ART2.StateDur AS C-StDur,
ART1.LoginDur AS V-LogDur,
ART2.LoginDur AS C-LogDur,
ART1.ReasonCode AS V-RCode,
ART2.ReasonCode AS C-RCode
FROM AgentRealTime ART1, AgentRealTime ART2
WHERE ART1.AgentID = ART2.AgentID AND
ART1.DomainID = 1 AND ART2.DomainID=5
)
AS AGRT

但是，只有在代理同时登录到这两种情况下，这才有效。我还有另外两种情况要处理；如果代理只以语音代理或聊天代理登录。对于语音登录，我使用这个查询；

    SELECT
AgentID=ART.AgentID,
V-State=ART.AgtState,
C-State=NULL,
V-StDur=ART.StateDur,
C-StDur=NULL,
V-LogDur=ART.LoginDur,
C-LogDur=NULL,
V-RCode=ART.ReasonCode,
C-RCode=NULL
FROM AgentRealTime ART
WHERE ART.DomainID = 1

对于仅用于聊天的登录，我使用以下内容

    SELECT
AgentID=ART.AgentID,
V-State=NULL,
C-State=ART.AgtState,
V-StDur=NULL,
C-StDur=ART.StateDur,
V-LogDur=NULL,
C-LogDur=ART.LoginDur,
V-RCode=NULL,
C-RCode=ART.ReasonCode
FROM AgentRealTime ART
WHERE ART.DomainID = 5

我把这三个问题都联合起来了。如果代理只登录到语音或聊天，则整个查询显示，如果代理同时登录到两个row.But，则I将以三行的形式结束；

AgtID | V-State |V-ReCode |V-LogDur |V-StDur |C-State|C-RCode  |C-LogDur  |C-StDur |
-------------------------------------------------------------------------------------------
1301    |NotReady|Lunch    |01:23:12 |00:23:21|NULL   |NULL |NULL  |NULL

AgtID | V-State |V-ReCode |V-LogDur |V-StDur |C-State|C-RCode  |C-LogDur  |C-StDur |
-------------------------------------------------------------------------------------------
1301    |NotReady|Lunch    |01:23:12 |00:23:21|Ready  |Undefined|00:57:12  |00:00:12

AgtID | V-State |V-ReCode |V-LogDur |V-StDur |C-State|C-RCode  |C-LogDur  |C-StDur |
-------------------------------------------------------------------------------------------
1301    |NULL|NULL     |NULL |NULL  |Ready  |Undefined|00:57:12  |00:00:12

如果代理同时登录到语音和聊天中，我如何将这三行合并为一行？或者您可以为这个场景推荐的任何其他方法？

Answer 1

你只需要完全的外部连接：

SELECT AgentID, V-State, V-StDur, V-LogDur, V-RCode, C-State, C-StDur, C-LogDur, C-RCode 
FROM(SELECT
COALEASE(ART1.AgentID,ART2.AgentID) AS AgentID,
ART1.AgtState AS V-State,
ART2.AgtState AS C-State,
ART1.StateDur AS V-StDur,
ART2.StateDur AS C-StDur,
ART1.LoginDur AS V-LogDur,
ART2.LoginDur AS C-LogDur,
ART1.ReasonCode AS V-RCode,
ART2.ReasonCode AS C-RCode
FROM AgentRealTime ART1 
FULL OUTER JOIN AgentRealTime ART2
ON ART1.AgentID = ART2.AgentID AND
WHERE ART1.DomainID = 1 AND ART2.DomainID=5 
)
AS AGRT

AgentId需要使用煤块。