WITH CTE AS (
select RowID = ROW_NUMBER() OVER (ORDER BY (case when Time is null then TimeC else Time end) ASC,NEWID()),* 
from(
            select * from
               (     
                 select * from (select * from Orders) a 
                               full join (select * from PatientCome) b 
                               on a.GUID = b.ComeGUID
               ) c
               where (c.ProjectID=16 or RealProjectID=16)
                  and 
                 (
                   (convert(varchar(10),Time,120) >= '2010-01-01' and convert(varchar(10),Time,120) <='2015-02-02') 
                   or (convert(varchar(10),TimeC,120) >= '2010-01-01' and convert(varchar(10),TimeC,120) <='2015-02-02') 
                 )
              ) d
)
SELECT * FROM CTE A
WHERE EXISTS (
SELECT 1 FROM CTE WHERE Tel = A.Tel AND RowID <> A.RowID) order by Tel

SELECT * FROM CTE A
WHERE EXISTS (
SELECT 1 FROM CTE WHERE Tel = A.Tel AND RowID <> A.RowID and Tel <> N'') order by Tel

------解决方案--------------------
SQL code

SELECT * FROM CTE A
WHERE A.Tel <>'' and EXISTS (
SELECT 1 FROM CTE WHERE Tel = A.Tel AND RowID <> A.RowID) order by Tel

------解决方案--------------------
看到2个大问题：

乱用子SQL语句
乱用CTE




------解决方案--------------------

探讨


感谢楼上，第一个问题已经解决了（也就是tel字段为空的字段不算差重范围）。
现在要解决第二个问题，也是非常头痛的问题。

为了便于大家更容易的分析我的问题，我先用一个demo来模拟一下我想要的结果 

比如表d的数据如下
HTML code

 tel      other
  111     11
  222      22
  111     33
  333     44
 ……