select a,b,c,sum(e)
from tableA
group by a,b,c
order by case when (select count(tb.tableBPK) from tableB as tb where tb.fieldA = d)> 2 then 0 else 1 end
tableA表有n个字段(a,b,c,d....),tableB.fieldA和tableA.d是外建关系
现在要tableB中出现过tableA.d 大于2次的排前面
现在有这个order by 和没有orderby都没有什么区别
因为以前设计有点问题group by a,b,c之前的部分不怎么好改,所以只能动order by 后面部分
并且和select a,b,c from tableA group by a,b,c必须在sqlserver2k下可以运行
效率暂时不考虑,反正2个表数据也不会超过1w条,1个月也就跑几次
------解决方案--------------------
select a,b,c,e from
(select a,b,c,sum(e) e,case when (select count(tb.tableBPK) from tableB as tb where tb.fieldA = d)> 2 then 0 else 1 end 次数
from tableA
group by a,b,c) as t
order by t.次数 desc