ORDER BY +变量

[bieyinan]

CREATE   FUNCTION   F_ID_paixun(@id   INT, @PAIXU VARCHAR(50))   
RETURNS   TABLE   
AS   



RETURN   (select VIP_NO,Sum(Test_Num) as Test_Num,Max(Test_Time) as test_time from soft_Vip_sum where Soft_name=@id GROUP BY VIP_NO order by @PAIXU desc 
GO   

[bieyinan]

ORDER BY 子句所标识的第 1 个 SELECT 项包含了一个变量，该变量位于标识列位置的表达式中。只有排序依据表达式引用的是列名时，才允许在该表达式中使用变量。

[csdyyr]

用存储过程动态SQL。

[bieyinan]

[Quote=引用 2 楼 csdyyr 的回复:]
用存储过程动态SQL。
[/Quote]
请赐教

[csdyyr]

CREATE   PROC   P_ID_paixun
@id   INT, 
@PAIXU VARCHAR(50) 
AS   
  EXEC('select VIP_NO,Sum(Test_Num) as Test_Num,Max(Test_Time) as test_time from soft_Vip_sum where Soft_name='+@id+' GROUP BY VIP_NO order by '+ @PAIXU+' desc ')
GO 

[SQL77]

[Quote=引用楼主 bieyinan 的帖子:]
SQL code

CREATE FUNCTION F_ID_paixun(@id INT, @PAIXU VARCHAR(50))
RETURNS TABLE
AS



RETURN (select VIP_NO,Sum(Test_Num) as Test_Num,Max(Test_Time) as test_time from soft_Vip_sum where Soft_name=@id GROUP BY VIP_NO order by @PAIXU desc
GO
[/Quote]

将一个列名赋给那个排序表达示就好了!!

[sdhdy]

CREATE   proc   F_ID_paixun(@id   INT, @PAIXU VARCHAR(50))     
AS   



exec ('select VIP_NO,Sum(Test_Num) as Test_Num,Max(Test_Time) as test_time from soft_Vip_sproum where Soft_name='''+@id+''' GROUP BY VIP_NO order by '+@PAIXU+' desc ')
GO   

[csdyyr]

[Quote=引用 4 楼 csdyyr 的回复:]
SQL codeCREATEPROCP_ID_paixun@idINT,@PAIXUVARCHAR(50)ASEXEC('select VIP_NO,Sum(Test_Num) as Test_Num,Max(Test_Time) as test_time from soft_Vip_sum where Soft_name='+@id+'GROUP BY VIP_NO order by'+@PAIXU+'desc')GO
[/Quote]

CREATE   PROC   P_ID_paixun
@id   INT, 
@PAIXU VARCHAR(50) 
AS   
  DECLARE @STR VARCHAR(8000)
  SET @STR='select VIP_NO,Sum(Test_Num) as Test_Num,Max(Test_Time) as test_time from soft_Vip_sum where Soft_name='+RTRIM(@id)+' GROUP BY VIP_NO order by '+ @PAIXU+' desc '
  EXEC(@STR)
GO 

[csdyyr]

--如果Soft_name是字符型
CREATE   PROC   P_ID_paixun
@id   INT, 
@PAIXU VARCHAR(50) 
AS   
  DECLARE @STR VARCHAR(8000)
  SET @STR='select VIP_NO,Sum(Test_Num) as Test_Num,Max(Test_Time) as test_time from soft_Vip_sum where Soft_name='''+RTRIM(@id)+''' GROUP BY VIP_NO order by '+ @PAIXU+' desc '
  EXEC(@STR)
GO

[bieyinan]

谢谢各位，我的是自定义函数不是存储过程
CREATE FUNCTION F_ID_paixun

[SQL77]

[Quote=引用楼主 bieyinan 的帖子:]
SQL code

CREATE FUNCTION F_ID_paixun(@id INT, @PAIXU VARCHAR(50))
RETURNS TABLE
AS



RETURN (select VIP_NO,Sum(Test_Num) as Test_Num,Max(Test_Time) as test_time from soft_Vip_sum where Soft_name=@id GROUP BY VIP_NO order by @PAIXU desc
GO

[/Quote]

CREATE   FUNCTION   F_ID_paixun(@id   INT, @PAIXU VARCHAR(50))   
RETURNS   TABLE   
AS   



RETURN   (select TOP 100 VIP_NO,Sum(Test_Num) as Test_Num,Max(Test_Time) as test_time from soft_Vip_sum where Soft_name=@id GROUP BY VIP_NO order by '@PAIXU' desc 
GO

这样试试??呵呵

[SQL77]

CREATE   FUNCTION   F_ID_paixun(@id   INT, @PAIXU VARCHAR(50))   
RETURNS   @TABLE   TABLE(VIP_NO VARCHAR(20),TEST_NUM INT,TEST_TIME DATETIME)
AS   
select VIP_NO,
Sum(Test_Num) as Test_Num,
Max(Test_Time) as test_time 
from soft_Vip_sum where Soft_name=@id 
GROUP BY VIP_NO 
order by '@PAIXU' desc 

RETURN
GO

这样吧,应该可以了,呵呵

[sdhdy]

[Quote=引用楼主 bieyinan 的帖子:]
SQL code

CREATE FUNCTION F_ID_paixun(@id INT, @PAIXU VARCHAR(50))
RETURNS TABLE
AS



RETURN (select VIP_NO,Sum(Test_Num) as Test_Num,Max(Test_Time) as test_time from soft_Vip_sum where Soft_name=@id GROUP BY VIP_NO order by @PAIXU desc
GO
[/Quote]
order by @PAIXU ，这种情况要用动态SQL.
关于动态SQL基本语法，楼主可以参考这个。
http://blog.csdn.net/sdhdy/archive/2009/05/15/4190010.aspx
用函数处理这种情况，不太容易，建议楼主变通一下用存储过程来实现。

[SQL77]

[Quote=引用 11 楼 SQL77 的回复:]
SQL code
CREATE FUNCTION F_ID_paixun(@id INT, @PAIXU VARCHAR(50))
RETURNS @TABLE TABLE(VIP_NO VARCHAR(20),TEST_NUM INT,TEST_TIME DATETIME)
AS
select VIP_NO,
Sum(Test_Num) as Test_Num,
Max(Test_Time) as test_time
from soft_Vip_sum where Soft_name=@id
GROUP BY VIP_NO
order by '@PAIXU' desc

RETURN
GO








这样吧,应该可以了,呵呵
[/Quote]

少了几个东西,呵呵!!补上!!

CREATE   FUNCTION   F_ID_paixun(@id   INT, @PAIXU VARCHAR(50))   
RETURNS   @TABLE   TABLE(VIP_NO VARCHAR(20),TEST_NUM INT,TEST_TIME DATETIME)
AS 
begin  
insert @table
select VIP_NO,
Sum(Test_Num) as Test_Num,
Max(Test_Time) as test_time  
from soft_Vip_sum 
where Soft_name=@id 
GROUP BY VIP_NO 
order by '@PAIXU' desc 
return
end
GO

[--小F--]

学习

[zhaoweiting0609]

学习中，感觉应该用动态SQL

[ssxw]

--这样吧

--方法1：
ALTER function [dbo].[TestVar](@paixu varchar(20))
returns @Table TABLE(Object VARCHAR(500),Event varchar(500),PostTime datetime)
as
begin
insert into @Table
select Object,max([Event]) Event,max(PostTime) PostTime from databaseLog
group by object
order by (select name from syscolumns where id=object_id('DatabaseLog') and name=@paixu) Desc
/*case @paixu when 'Event' then 'Event' when 'PostTime' then 'PostTime' else 'Object' end */

return
end


--方法2
ALTER function [dbo].[TestVar](@paixu varchar(20))
returns @Table TABLE(Object VARCHAR(500),Event varchar(500),PostTime datetime)
as
begin
insert into @Table
select Object,max([Event]) Event,max(PostTime) PostTime from AdventureWorks.dbo.databaseLog
group by object order by case @paixu when 'Event' then 'Event' when 'PostTime' then 'PostTime' else 'Object' end
return
end

[kye_jufei]

[Quote=引用 13 楼 SQL77 的回复:]
引用 11 楼 SQL77 的回复:
SQL code
CREATE FUNCTION F_ID_paixun(@id INT, @PAIXU VARCHAR(50))
RETURNS @TABLE TABLE(VIP_NO VARCHAR(20),TEST_NUM INT,TEST_TIME DATETIME)
AS
select VIP_NO,
Sum(Test_Num) as Test_Num,
Max(Test_Time) as test_time
from soft_Vip_sum where Soft_name=@id
GROUP BY VIP_NO
order by '@PAIXU' desc

RETURN
GO

[/Quote]
up

[幸运的意外]

表值函数中对于简单的查询还可以，如果用到变量确定的查询，最好还是用动态SQL，函数用动态存储过程感觉不怎么好。楼主再合计合计吧。

[zxkid]

学习 好像只能用动态SQL吧

[wjfmail]

没错,用动态sql;不过你可以把它放在存取过程里.