求合并SQL语句
declare @tbl_Tmp table(iCount int, Class int, SubClass int, iParam int)
insert into @tbl_Tmp(iCount, Class, SubClass, iParam)
select 15,1,0,1 union all
select 10,1,1,1 union all //对于类1的每一子类0,1,2,iParam=1的统计值都> 0,符合条件
select 50,1,2,1 union all
select 1,1,0,2 union all
select 0,1,1,2 union all //对于类1的每一子类0,1,2,iParam=2的统计值并非都> 0,不符合条件
select 1,1,2,2 union all
select 1,2,0,3 union all //对于类2的每一子类0,1,iParam=3的统计值并非都> 0,不符合条件
select 0,2,1,3
表中数据如上,输入参数iParam和类Class、子类SubClass匹配,得出统计值iCount
现在要按Class分组,求每一SubClass的统计值都大于0的那个输入参数,即
select Class,iParam from @tbl_Tmp where(...) 得到Class=1,iParam=1这一行数据
[解决办法]
这样行不行呀
declare @tbl_Tmp table(iCount int, Class int, SubClass int, iParam int)
insert into @tbl_Tmp(iCount, Class, SubClass, iParam)
select 15,1,0,1 union all
select 10,1,1,1 union all --//对于类1的每一子类0,1,2,iParam=1的统计值都> 0,符合条件
select 50,1,2,1 union all
select 1,1,0,2 union all
select 0,1,1,2 union all --//对于类1的每一子类0,1,2,iParam=2的统计值并非都> 0,不符合条件
select 1,1,2,2 union all
select 1,2,0,3 union all --//对于类2的每一子类0,1,iParam=3的统计值并非都> 0,不符合条件
select 0,2,1,3
select * from @tbl_Tmp a where not exists(select 1 from @tbl_Tmp b where a.class=b.class and a.iparam <=0)
[解决办法]
select * from @tbl_Tmp a
where iparam not in (select iParam from @tbl_Tmp where a.Class =Class and icount <= 0 )
iCount Class SubClass iParam
----------- ----------- ----------- -----------
15 1 0 1
10 1 1 1
50 1 2 1
(所影响的行数为 3 行)
[解决办法]
按条件进行统计..
用if else ...或动态语句case when ...then
[解决办法]
--maybe you can try :
select class,iparam from @tbl_Tmp
group by class,iparam
having min(icount)> 0
/*result:*/
class iparam
----------- -----------
1 1