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

关于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 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
where 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