select * from
(select [name],b='买家',Country.CountryName as country ,Citys.CityName as house,Peoples.CardNumber as 身份证,
time1 =count(*) from Deals INNER JOIN
dbo.Houses AS h ON Deals.HouseID = h.HouseID left join HouseType ht on ht.TypeID = h.HouseTypeID ,Peoples INNER JOIN
Country ON Peoples.CountryID = Country.CountryID INNER JOIN
Citys ON Peoples.CityID = Citys.CityCode where BuyID=Peoples.PeopleID and Deals.CheckTime between @startdate and @enddate and (@Name is null or [name] like '%'+@Name+'') and
(@id is null or Peoples.CardNumber like '%'+@id+'%') and (@countryname is null or Country.CountryName = @countryname) and (@cityname is null or Citys.CityName = @cityname) and (@qu is null or h.Qu = @qu)
and (@leixing is null or case when @leixing = '公寓' then ht.HouseTypeName in ('新工房','公寓') when @leixing = '别墅' then ht.HouseTypeName = '别墅'
when @leixing = '联排' then ht.HouseTypeName = '联体' when @leixing = '办公' then ht.HouseTypeName = '办公楼' when @leixing = '商铺' then ht.HouseTypeName = '商铺' when @leixing = '车库' then (h.Shi like '%车%' or h.Hao like '%车%'))group by Peoples.PeopleID,[name],Country.CountryName,Citys.CityName,Peoples.CardNumber)a
where (@cishu is null or case when @cishu = '首次交易者' then time1 = 1 when @cishu = '2次交易者' then time1 = 2 when @cishu = '3-5次交易者' then time1 in (3,4,5) when @cishu = '5-10次交易者' then time1 in (6,7,8,9,10)
when @cishu = '10次以上交易者' then time1 > 10)
union all
select * from
(select [name],b='卖家',Country.CountryName as country ,Citys.CityName as house,Peoples.CardNumber as 身份证,
time1 =count(*) from Deals INNER JOIN
dbo.Houses AS h ON Deals.HouseID = h.HouseID left join HouseType ht on ht.TypeID = h.HouseTypeID ,Peoples INNER JOIN
Country ON Peoples.CountryID = Country.CountryID INNER JOIN
Citys ON Peoples.CityID = Citys.CityCode where SellID=Peoples.PeopleID and Deals.CheckTime between @startdate and @enddate and (@Name is null or [name] like '%'+@Name+'') and
(@id is null or Peoples.CardNumber like '%'+@id+'%') and (@countryname is null or Country.CountryName = @countryname) and (@cityname is null or Citys.CityName = @cityname) and (@qu is null or h.Qu = @qu)
and (@leixing is null or case when @leixing = '公寓' then ht.HouseTypeName in ('新工房','公寓') when @leixing = '别墅' then ht.HouseTypeName = '别墅'
when @leixing = '联排' then ht.HouseTypeName = '联体' when @leixing = '办公' then ht.HouseTypeName = '办公楼' when @leixing = '商铺' then ht.HouseTypeName = '商铺' when @leixing = '车库' then (h.Shi like '%车%' or h.Hao like '%车%'))
group by Peoples.PeopleID,[name],Country.CountryName,Citys.CityName,Peoples.CardNumber)a
where (@cishu is null or case when @cishu = '首次交易者' then time1 = 1 when @cishu = '2次交易者' then time1 = 2 when @cishu = '3-5次交易者' then time1 in (3,4,5) when @cishu = '5-10次交易者' then time1 in (6,7,8,9,10)
when @cishu = '10次以上交易者' then time1 > 10)
?????