--> liangCK小梁 于2008-10-09
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (id INT,vouch INT)
INSERT INTO #T
SELECT '51','4' UNION ALL
SELECT '45','3' UNION ALL
SELECT '47','2' UNION ALL
SELECT '49','1' UNION ALL
SELECT '50','0' UNION ALL
SELECT '44','0' UNION ALL
SELECT '48','0' UNION ALL
SELECT '46','0'--SQL查询如下:SELECT *
FROM #T
ORDER BY ISNULL(NULLIF(vouch,0),99999999),id DESC/*
id vouch
----------- -----------
49 1
47 2
45 3
51 4
50 0
48 0
46 0
44 0(8 行受影响)*/
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (id INT,vouch INT)
INSERT INTO #T
SELECT '51','4' UNION ALL
SELECT '45','3' UNION ALL
SELECT '47','2' UNION ALL
SELECT '49','1' UNION ALL
SELECT '50','0' UNION ALL
SELECT '44','0' UNION ALL
SELECT '48','0' UNION ALL
SELECT '46','0'--SQL查询如下:SELECT *
FROM #T
ORDER BY ISNULL(NULLIF(vouch,0),99999999),id DESC/*
id vouch
----------- -----------
49 1
47 2
45 3
51 4
50 0
48 0
46 0
44 0(8 行受影响)*/
FROM Product
ORDER BY case when Vouch=0 then 5 else Vouch end ,id desc
FROM Product
ORDER BY (CASE WHEN Vouch = 0 THEN 5 ELSE Vouch END),id desc如果某几条记录的vouch字段为0,没办法使其排到最后
union all
SELECT .... where Vouch =0 id DESC