求一条查重的sql语句。解决办法-MSSQL教程-爱易网页

求一条查重的sql语句。解决办法

日期：2014-05-18　浏览次数：20761 次

求一条查重的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 codeWITH CTE AS (
...
)
SELECT * FROM CTE A
WHERE EXISTS (
SELECT 1 FROM CTE WHERE NAME = A.NAME AND TEL <> A.TEL
)

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

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

求一条查重的sql语句。解决办法

相关资料更多>

推荐阅读更多>