日期:2014-05-18  浏览次数:20648 次

求一条查重的sql语句。
比如我要得到所有Name列重复的数据,查询结果要按Name列排序,下面是我通过sql语句得到的一个“表d”,请大家就“表d”中添加上追加上相关sql语句,谢谢!!
C# code

select * 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




------解决方案--------------------
SQL code
with tb as (
select * 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 a.* from (select name,count(name) as num from tb  group by name ) a join
tb b on a.name=b.name where b.num>1

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

select d.NAME 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
              GROUP BY d.NAME
              HAVING COUNT(d.name)>1
              ORDER BY d.name

------解决方案--------------------
SQL code
WITH CTE AS (
...
)
SELECT * FROM CTE A
WHERE EXISTS (
SELECT 1 FROM CTE WHERE NAME = A.NAME AND TEL <> A.TEL
)

------解决方案--------------------
try
SQL code
select * from d t
where (select count(1) from d where name=t.name)>1
order by name