--查询下面语句报错：从数据类型 nvarchar 转换为 float 时出错。
declare @temp table(company nvarchar(50),account nvarchar(50),keyword nvarchar(50),cost float)
insert into @temp(company,account,keyword,cost) values( 'a ', 'r ', 'u ',1.0)
insert into @temp(company,account,keyword,cost) values( 'b ', 't ', 'i ',2.0)
insert into @temp(company,account,keyword,cost) values( 'c ', 'y ', 'o ',1.3)
declare @dir nvarchar(10),@para nvarchar(50)
select @dir= 'desc ',@para= 'company '
select company,account,keyword,cost from @temp
order by case @dir
when 'desc ' THEN CASE @para
when 'company ' then company
when 'account ' then account
when 'keyword ' then keyword
when 'cost ' then cost
end
end desc,
case @dir
when 'asc ' THEN CASE @para
when 'company ' then company
when 'account ' then account
when 'keyword ' then keyword
when 'cost ' then cost
end
end ,
case @dir
when '0 ' then 1
end/**/
------解决方案--------------------
晕，这么处理
declare @temp table(company nvarchar(50),account nvarchar(50),keyword nvarchar(50),cost float)
insert into @temp(company,account,keyword,cost) values( 'a ', 'r ', 'u ',1.0)
insert into @temp(company,account,keyword,cost) values( 'b ', 't ', 'i ',2.0)
insert into @temp(company,account,keyword,cost) values( 'c ', 'y ', 'o ',1.3)
declare @dir nvarchar(10),@para nvarchar(50)
select @dir= 'desc ',@para= 'company '
select company,account,keyword,cost from @temp
order by case @dir
when 'desc ' THEN CASE @para
when 'company ' then company
when 'account ' then account
when 'keyword ' then keyword
else ' '
end
end desc,
case when @dir= 'desc ' and @para = 'cost ' then cost
else 0
end desc,
case @dir
when 'asc ' THEN CASE @para
when 'company ' then company
when 'account ' then account
when 'keyword ' then keyword
else ' '
end
end ,
case when @dir= 'asc ' and @para = 'cost ' then cost
else 0
end