三个表如何full join（sql语句有点长...）

[ximomomoxinei22]

前两个表已经合并成功了，条件是->第一个表和第二表中 的 ProjectID字段和StatisticsDate字段值同时相同，则进行合并!!
sql语句如下

SELECT a.StatisticsDate as 对话量日期,a.ProjectID as 对话量项目ID,a.LocalAllCount as 本地对话量,
a.NoLocalAllCount as 外地对话量,a.LocalValidCount 本地有效对话量,a.NoLocalValidCount as 外地有效对话量,
b.预约总数,b.到诊总数,b.到诊率,b.主表日期,b.ProjectID as 主表项目ID
 FROM DialogueQuantity a FULL join
 (select ProjectID,主表日期=convert(varchar(10),OrderTime,120),
 COUNT(convert(varchar(10),OrderTime,120)) as 预约总数, 
 sum(case when PatientStatus='复诊' or PatientStatus='初诊' then 1 else 0 end) as 到诊总数, 
 ltrim(sum(case when PatientStatus='复诊' or PatientStatus='初诊' then 1 else 0 end)*100/count(1))+'%' as 到诊
率 from Orders where IsArrive=0 group by convert(varchar(10),OrderTime,120) ,ProjectID)b
  on a.ProjectID=b.ProjectID
  and a.StatisticsDate= b.主表日期

现在我想合并第三个表，也用full join。同时条件是 与上面两表中任何一个表的ProjectID字段和StatisticsDate字段值同时相同
就 进行合并（应该是这个逻辑没错吧），应该怎么写呢？

第三个表单独查询的sql语句->
select StatisticsDate,ProjectID,CostAmount,ShowAmount,ClickAmount from BidStatistics

[ximomomoxinei22]

说白了其实就是这三个表（两个实表一个虚表）的 ProjectID字段和StatisticsDate字段值完全都相同的时候 才进行合并为一行，
任意两个表的ProjectID字段和StatisticsDate字段值完全 也进行合并
用full jion

[叶子]

SELECT  a.StatisticsDate AS 对话量日期 ,
        a.ProjectID AS 对话量项目ID ,
        a.LocalAllCount AS 本地对话量 ,
        a.NoLocalAllCount AS 外地对话量 ,
        a.LocalValidCount 本地有效对话量 ,
        a.NoLocalValidCount AS 外地有效对话量 ,
        b.预约总数 ,
        b.到诊总数 ,
        b.到诊率 ,
        b.主表日期 ,
        b.ProjectID AS 主表项目ID
FROM    DialogueQuantity a
        FULL JOIN ( SELECT  ProjectID ,
                            主表日期 = CONVERT(VARCHAR(10), OrderTime, 120) ,
                            COUNT(CONVERT(VARCHAR(10), OrderTime, 120)) AS 预约总数 ,
                            SUM(CASE WHEN PatientStatus = '复诊'
                                          OR PatientStatus = '初诊' THEN 1
                                     ELSE 0
                                END) AS 到诊总数 ,
                            LTRIM(SUM(CASE WHEN PatientStatus = '复诊'
                                                OR PatientStatus = '初诊' THEN 1
                                           ELSE 0
                                      END) * 100 / COUNT(1)) + '%' AS 到诊率
                    FROM    Orders
                    WHERE   IsArrive = 0
                    GROUP BY CONVERT(VARCHAR(10), OrderTime, 120) ,
                            ProjectID
                  ) b ON a.ProjectID = b.ProjectID
                         AND a.StatisticsDate = b.主表日期
        FULL JOIN BidStatistics c ON ( a.ProjectID = c.ProjectID
                                       AND a.StatisticsDate = c.StatisticsDate
                                     )
                                     OR ( b.ProjectID = c.ProjectID
                                          AND b.主表日期 = c.StatisticsDate
                                        )

[ximomomoxinei22]

太感谢了 就是这个意思
结贴了！