你上面的结果没有错.create table a( id int identity, unitid varchar(10), productid varchar(10), brand varchar(10), zid int )insert into a values('10001','20202020','aa',null) insert into a values('10002','10101010','bb',null) insert into a values('10002','20202020','aa',null) insert into a values('10003','10101010','bb',null) declare @Orderby int set @Orderby = 1 select * from a order by ( CASE @OrderBy WHEN 1 THEN unitid WHEN 2 THEN productid ELSE zid END ) DESC --- id unitid productid brand zid ----------- ---------- ---------- ---------- ----------- id unitid productid brand zid ----------- ---------- ---------- ---------- ----------- 4 10003 10101010 bb NULL 3 10002 20202020 aa NULL 2 10002 10101010 bb NULL 1 10001 20202020 aa NULL(所影响的行数为 4 行)
SELECT PL.PostId FROM ProductList PL WHERE PL.StoreId = @StoreId AND Title LIKE '%'+@Title+'%' ORDER BY ( CASE WHEN @OrderBy=1 THEN Title WHEN @OrderBy=2 THEN PriceNew WHEN @OrderBy=3 THEN RefreshDate WHEN @OrderBy=4 THEN ValueDate ELSE RefreshDate END ) DESC
order by没有错,问题是@orderby没有在聚合函数或者group by后边:select count(1) from ( SELECT PL.PostId FROM ProductList PL WHERE PL.StoreId = @StoreId AND Title LIKE '%'+@Title+'%' ORDER BY ( CASE @OrderBy WHEN 1 THEN Title WHEN 2 THEN PriceNew WHEN 3 THEN RefreshDate WHEN 4 THEN ValueDate ELSE RefreshDate END ) DESC )t
楼主不是聚合,原来的语句SELECT PL.PostId 。可以行的通。
: ) 不好意思,是可以通过. 多了一个 count()
CASE @OrderBy WHEN 1 THEN Title WHEN 2 THEN PriceNew WHEN 3 THEN RefreshDate WHEN 4 THEN ValueDate ELSE RefreshDate END 此表达式返回的类型是Title,PriceNew,RefreshDate,ValueDate中类型最高的。 估计应该是datetime类型(猜测RefreshDate或ValueDate中有一个是datetime类型), 那么如果title是varchar或text等, 当@OrderBy=1时按Title排序时,需要转换Title的值为Datetime类型,显然不能转换。 故产生错误
这样修改:CASE @OrderBy WHEN 1 THEN Title else null end, CASE @OrderBy WHEN 2 THEN PriceNew else null end, CASE @OrderBy WHEN 3 THEN RefreshDate else null end, CASE @OrderBy WHEN 4 THEN ValueDate else null end, CASE WHEN @OrderBy NOT IN (1,2,3,4) THEN RefreshDate else null end
好象then后的值类型也不统一
SELECT PL.PostId
id int identity,
unitid varchar(10),
productid varchar(10),
brand varchar(10),
zid int
)insert into a values('10001','20202020','aa',null)
insert into a values('10002','10101010','bb',null)
insert into a values('10002','20202020','aa',null)
insert into a values('10003','10101010','bb',null)
declare @Orderby int
set @Orderby = 1
select * from a
order by
(
CASE @OrderBy
WHEN 1 THEN unitid
WHEN 2 THEN productid
ELSE zid
END
) DESC
---
id unitid productid brand zid
----------- ---------- ---------- ---------- -----------
id unitid productid brand zid
----------- ---------- ---------- ---------- -----------
4 10003 10101010 bb NULL
3 10002 20202020 aa NULL
2 10002 10101010 bb NULL
1 10001 20202020 aa NULL(所影响的行数为 4 行)
FROM ProductList PL
WHERE
PL.StoreId = @StoreId
AND Title LIKE '%'+@Title+'%'
ORDER BY
(
CASE WHEN @OrderBy=1 THEN Title
WHEN @OrderBy=2 THEN PriceNew
WHEN @OrderBy=3 THEN RefreshDate
WHEN @OrderBy=4 THEN ValueDate
ELSE RefreshDate
END
) DESC
from
(
SELECT PL.PostId
FROM ProductList PL
WHERE
PL.StoreId = @StoreId
AND Title LIKE '%'+@Title+'%'
ORDER BY
(
CASE @OrderBy
WHEN 1 THEN Title
WHEN 2 THEN PriceNew
WHEN 3 THEN RefreshDate
WHEN 4 THEN ValueDate
ELSE RefreshDate
END
) DESC
)t
WHEN 1 THEN Title
WHEN 2 THEN PriceNew
WHEN 3 THEN RefreshDate
WHEN 4 THEN ValueDate
ELSE RefreshDate
END
此表达式返回的类型是Title,PriceNew,RefreshDate,ValueDate中类型最高的。
估计应该是datetime类型(猜测RefreshDate或ValueDate中有一个是datetime类型),
那么如果title是varchar或text等,
当@OrderBy=1时按Title排序时,需要转换Title的值为Datetime类型,显然不能转换。
故产生错误
CASE @OrderBy WHEN 2 THEN PriceNew else null end,
CASE @OrderBy WHEN 3 THEN RefreshDate else null end,
CASE @OrderBy WHEN 4 THEN ValueDate else null end,
CASE WHEN @OrderBy NOT IN (1,2,3,4) THEN RefreshDate else null end