sql 查询
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))+ ' ' ' '
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)
