不显示某字段有重复的记录 SQL语句

详细解释下需求：

    根据某字段（此例中为StateProvinceID）做判断，如果发现表中该字段有重复值，则结果不显示该条记录。

一，最直接的方法，效率低

 1 SELECT * FROM 
 2  (
 3         SELECT StateProvinceID FROM
 4         (
 5             SELECT COUNT(*) AS num ,ad.StateProvinceID 
 6                 FROM Person.Address ad 
 7                   GROUP BY StateProvinceID
 8          )AS tt
 9          WHERE tt.num<2  --排除重复
10      ) AS tab
11    INNER JOIN Person.Address addr ON tab.StateProvinceID=addr.StateProvinceID

二、使用了CTE

 1 with t1
 2  AS(
 3     SELECT 
 4     * FROM Person.Address --WHERE AddressID<200
 5   ),
 6      t2 
 7   AS(
 8     SELECT 
 9     COUNT(*) AS num ,
10     t1.StateProvinceID 
11     FROM t1 
12     GROUP BY StateProvinceID
13     )
14 SELECT * FROM T2 
15    INNER JOIN T1 ON t2.StateProvinceID = t1.StateProvinceID
16   WHERE t2.num<2

三、最终版

 1  with  t2 
 2   AS(
 3     SELECT 
 4     COUNT(*) AS num ,
 5     t1.StateProvinceID 
 6     FROM Person.Address t1
 7     GROUP BY StateProvinceID
 8     )  
 9 SELECT * FROM 
10 (
11   SELECT T2.StateProvinceID  FROM t2 WHERE num<2
12   ) AS T3
13 INNER JOIN Person.Address t1 ON T3.StateProvinceID=t1.StateProvinceID

还听到有人说可以写个func来判断存临时表，本人没有测试，在此没有列出。

 

 

posted @ 2013-01-05 14:52  net515  阅读(3919)  评论(0)  编辑  收藏  举报