sql语句:
set @sql='select distinct p.Name as 姓名 ,''中国'' as country ,''上海'' as house,p.CardNumber as 身份证,t4.allcount as 次数 from Peoples p inner join
#table4 t4 on t4.PeopleID = p.PeopleID inner join Deals d on d.BuyID = p.PeopleID INNER JOIN
dbo.Houses AS h ON d.HouseID = h.HouseID left join HouseType ht on ht.TypeID = h.HouseTypeID where '
set @sql = @sql + ' d.CheckTime>='''+cast(@startdate as nvarchar(10))+''' and'
set @sql = @sql + ' d.CheckTime<='''+cast(@enddate as nvarchar(10))+''' and'if @Name <> ''
set @sql=@sql+' p.Name like '''+@Name+'%'' and '
if @id <> ''
set @sql=@sql+' p.CardNumber like ''%'+@id+'%'' and '
if @cishu <> ''
if @cishu = '首次交易者'
set @sql=@sql+' t4.allcount = 1 and'
if @cishu = '2次交易者'
set @sql=@sql+' t4.allcount= 2 and'
if @cishu = '3-5次交易者'
set @sql=@sql+' t4.allcount in (3,4,5) and'
if @cishu = '5-10次交易者'
set @sql=@sql+' t4.allcount in (6,7,8,9,10) and'
if @cishu = '10次以上交易者'
set @sql=@sql+' t4.allcount > 10 and'
if @qu <> ''
set @sql=@sql+' h.qu='''+@qu+''' and'
if @leixing <> ''
begin
if @leixing = '公寓'
set @sql=@sql+' ht.HouseTypeName in (''新工房'',''新式公寓'') and '
if @leixing = '别墅'
set @sql=@sql+' ht.HouseTypeName = ''公园别墅'' or h.XiangMu like ''%别墅%'' or h.Shi like ''%全幢%'' or h.Shi like ''%层%'' or h.Hao like ''%全幢%'' and '
if @leixing = '联排'
set @sql=@sql+' ht.HouseTypeName = ''联体'' and '
if @leixing = '办公'
set @sql=@sql+' ht.HouseTypeName = ''办公楼'' and '
if @leixing = '商铺'
set @sql=@sql+' ht.HouseTypeName = ''商铺'' and '
if @leixing = '车库'
set @sql=@sql+' h.Shi like ''%车%'' or h.Hao like ''%车%'' and '
end
set @sql=left(@sql,len(@sql)-6)
--set @sql=@sql + ' order by CheckTime desc'exec(@sql)这样的话有问题,问题出现在 后面的 or 那里,请问该怎么改正下。谢谢。
set @sql='select distinct p.Name as 姓名 ,''中国'' as country ,''上海'' as house,p.CardNumber as 身份证,t4.allcount as 次数 from Peoples p inner join
#table4 t4 on t4.PeopleID = p.PeopleID inner join Deals d on d.BuyID = p.PeopleID INNER JOIN
dbo.Houses AS h ON d.HouseID = h.HouseID left join HouseType ht on ht.TypeID = h.HouseTypeID where '
set @sql = @sql + ' d.CheckTime>='''+cast(@startdate as nvarchar(10))+''' and'
set @sql = @sql + ' d.CheckTime<='''+cast(@enddate as nvarchar(10))+''' and'if @Name <> ''
set @sql=@sql+' p.Name like '''+@Name+'%'' and '
if @id <> ''
set @sql=@sql+' p.CardNumber like ''%'+@id+'%'' and '
if @cishu <> ''
if @cishu = '首次交易者'
set @sql=@sql+' t4.allcount = 1 and'
if @cishu = '2次交易者'
set @sql=@sql+' t4.allcount= 2 and'
if @cishu = '3-5次交易者'
set @sql=@sql+' t4.allcount in (3,4,5) and'
if @cishu = '5-10次交易者'
set @sql=@sql+' t4.allcount in (6,7,8,9,10) and'
if @cishu = '10次以上交易者'
set @sql=@sql+' t4.allcount > 10 and'
if @qu <> ''
set @sql=@sql+' h.qu='''+@qu+''' and'
if @leixing <> ''
begin
if @leixing = '公寓'
set @sql=@sql+' ht.HouseTypeName in (''新工房'',''新式公寓'') and '
if @leixing = '别墅'
set @sql=@sql+' ht.HouseTypeName = ''公园别墅'' or h.XiangMu like ''%别墅%'' or h.Shi like ''%全幢%'' or h.Shi like ''%层%'' or h.Hao like ''%全幢%'' and '
if @leixing = '联排'
set @sql=@sql+' ht.HouseTypeName = ''联体'' and '
if @leixing = '办公'
set @sql=@sql+' ht.HouseTypeName = ''办公楼'' and '
if @leixing = '商铺'
set @sql=@sql+' ht.HouseTypeName = ''商铺'' and '
if @leixing = '车库'
set @sql=@sql+' h.Shi like ''%车%'' or h.Hao like ''%车%'' and '
end
set @sql=left(@sql,len(@sql)-6)
--set @sql=@sql + ' order by CheckTime desc'exec(@sql)这样的话有问题,问题出现在 后面的 or 那里,请问该怎么改正下。谢谢。
#table4 t4 on t4.PeopleID = p.PeopleID inner join Deals d on d.BuyID = p.PeopleID INNER JOIN
dbo.Houses AS h ON d.HouseID = h.HouseID left join HouseType ht on ht.TypeID = h.HouseTypeID where '
set @sql = @sql + ' d.CheckTime>='''+cast(@startdate as nvarchar(10))+''' and'
set @sql = @sql + ' d.CheckTime<='''+cast(@enddate as nvarchar(10))+''''if @Name <> ''
set @sql=@sql+' and p.Name like '''+@Name+'%'''
if @id <> ''
set @sql=@sql+' and p.CardNumber like ''%'+@id+'%'''
if @cishu <> ''
if @cishu = '首次交易者'
set @sql=@sql+' and t4.allcount = 1'
if @cishu = '2次交易者'
set @sql=@sql+' and t4.allcount= 2'
if @cishu = '3-5次交易者'
set @sql=@sql+' and t4.allcount in (3,4,5)'
if @cishu = '5-10次交易者'
set @sql=@sql+' and t4.allcount in (6,7,8,9,10)'
if @cishu = '10次以上交易者'
set @sql=@sql+' and t4.allcount > 10'
if @qu <> ''
set @sql=@sql+' and h.qu='''+@qu+''''if @leixing <> ''
begin
if @leixing = '公寓'
set @sql=@sql+' and ht.HouseTypeName in (''新工房'',''新式公寓'')'
if @leixing = '别墅'
set @sql=@sql+' and (ht.HouseTypeName = ''公园别墅'' or h.XiangMu like ''%别墅%'' or h.Shi like ''%全幢%'' or h.Shi like ''%层%'' or h.Hao like ''%全幢%'')'
if @leixing = '联排'
set @sql=@sql+' and ht.HouseTypeName = ''联体'''
if @leixing = '办公'
set @sql=@sql+' and ht.HouseTypeName = ''办公楼'''
if @leixing = '商铺'
set @sql=@sql+' and ht.HouseTypeName = ''商铺'''
if @leixing = '车库'
set @sql=@sql+' and (h.Shi like ''%车%'' or h.Hao like ''%车%'')'
end
--set @sql=left(@sql,len(@sql)-6)
--set @sql=@sql + ' order by CheckTime desc'
/*
我晕,能不能判断之后再加“and”
另:OR条件要()起来
*/
exec(@sql)