关于top 和 distinct 的使用有关问题

关于top 和 distinct 的使用问题

select top 10 a.id_jg,a.Mingcheng ,(select count(*) from JU_Unit_People c where a.id_jg=c.id_jg) as peopleShu,(select count(*) from JU_DWSZ_School d where a.id_jg=d.id_jg) as yingjian,(select count(*) from JU_DWSZ_Sequel e where a.id_jg=e.id_jg) as chengguo 
from JU_Unit a left join JU_DWSZ_School d on a.Id_JG=d.Id_JG left join JU_DWSZ_Sequel e on a. Id_JG=e.Id_JG
select top 10 属于存储过程里面的 并且规定这个存储过程不可更变 由于JU_DWSZ_School表现在有三条记录 查询结果会出现3条一模一样的数据 JU_DWSZ_School和JU_DWSZ_Sequel表有几条数据就会出现几条重复 想知道如何去掉重复数据
distinct出现在 a.id_jg前面 会报错

[解决办法]

SQL code

;with JU_Unit_Tmp as (select top 10 a.id_jg,a.Mingcheng ,(select count(*) from JU_Unit_People c where a.id_jg=c.id_jg) as peopleShu,(select count(*) from JU_DWSZ_School d where a.id_jg=d.id_jg) as yingjian,(select count(*) from JU_DWSZ_Sequel e where a.id_jg=e.id_jg) as chengguo  from JU_Unit a )select distinct * from JU_Unit_Tmp aleft join JU_DWSZ_School d on a.Id_JG=d.Id_JG left join JU_DWSZ_Sequel e on a. Id_JG=e.Id_JGwhere 1=1 and d.CreateDate>='2011-07-10' and d.CreateDate<='2012-07-10' and e.getDate>='2011-07-01' and e.getDate<='2012-07-10'
[解决办法]
select top 10 a.id_jg,a.Mingcheng ,(select count(*) from JU_Unit_People c where a.id_jg=c.id_jg) as peopleShu,(select count(*) from JU_DWSZ_School d where a.id_jg=d.id_jg) as yingjian,(select count(*) from JU_DWSZ_Sequel e where a.id_jg=e.id_jg) as chengguo 
from JU_Unit a left join JU_DWSZ_School d on a.Id_JG=d.Id_JG left join JU_DWSZ_Sequel e on a. Id_JG=e.Id_JG
GROUP BY a.id_jg,a.Mingcheng