select * from ( select '04' as id union all select '05' union all select '02' union all select '06' union all select '07' ) aa id ---- 04 05 02 06 07(所影响的行数为 5 行)------------- 我这里没有用排序
很简单,一条代码搞定 select id from tablename group by id
晕, select distinct id from #tselect id from #t group by id怎么都是自动排序阿!思考ing...
DECLARE @tb TABLE([id] varchar(10)) INSERT INTO @tb SELECT '04' UNION ALL SELECT '04' UNION ALL SELECT '04' UNION ALL SELECT '05' UNION ALL SELECT '05' UNION ALL SELECT '05' UNION ALL SELECT '02' UNION ALL SELECT '06' UNION ALL SELECT '06' UNION ALL SELECT '06' UNION ALL SELECT '07' SELECT ID, IDENTITY(INT, 1, 1) CT INTO #T FROM @tbSELECT A.ID FROM (SELECT ID, MIN(CT) AS CT FROM #T GROUP BY ID)A ORDER BY A.CTDROP TABLE #T
在用distinct时,sql会自动排序后在赛选,所以就出问题了。要变换方法才能实现。
select ID from ( select distinct ID from ( select '04' as id union all select '04' union all select '04' union all select '05' union all select '05' union all select '05' union all select '02' union all select '06' union all select '06' union all select '06' union all select '07' union all select '07' union all select '07' ) aa ) b order by case when id in ('04','05') then 1 else 0 end desc ,ID ID ---- 04 05 02 06 07(所影响的行数为 5 行)
order by case when id in ('04','05') then 1 else 0 end desc ,ID 我怎么看不明白?
是排序方式 id 为'04','05'时值为1,其它为0 desc排序从大到小,先排1,再排0
select ID from ( select distinct ID from ( select '04' as id union all select '04' union all select '04' union all select '05' union all select '05' union all select '05' union all select '02' union all select '06' union all select '06' union all select '06' union all select '07' union all select '07' union all select '07' ) aa ) b order by case when id in ('04','05') then 1 else 0 end desc ,ID 还是看不明白。 谁能改写成容易看懂的写法?
select * from
(
select '04' as id union all
select '05' union all
select '02' union all
select '06' union all
select '07' ) aa id
----
04
05
02
06
07(所影响的行数为 5 行)-------------
我这里没有用排序
select id from tablename group by id
select distinct id from #tselect id from #t group by id怎么都是自动排序阿!思考ing...
INSERT INTO @tb
SELECT '04'
UNION ALL SELECT '04'
UNION ALL SELECT '04'
UNION ALL SELECT '05'
UNION ALL SELECT '05'
UNION ALL SELECT '05'
UNION ALL SELECT '02'
UNION ALL SELECT '06'
UNION ALL SELECT '06'
UNION ALL SELECT '06'
UNION ALL SELECT '07'
SELECT ID, IDENTITY(INT, 1, 1) CT INTO #T FROM @tbSELECT A.ID FROM (SELECT ID, MIN(CT) AS CT FROM #T GROUP BY ID)A ORDER BY A.CTDROP TABLE #T
(
select distinct ID from
(
select '04' as id union all
select '04' union all
select '04' union all
select '05' union all
select '05' union all
select '05' union all
select '02' union all
select '06' union all
select '06' union all
select '06' union all
select '07' union all
select '07' union all
select '07' ) aa
) b
order by case when id in ('04','05') then 1 else 0 end desc ,ID ID
----
04
05
02
06
07(所影响的行数为 5 行)
我怎么看不明白?
id 为'04','05'时值为1,其它为0
desc排序从大到小,先排1,再排0
(
select distinct ID from
(
select '04' as id union all
select '04' union all
select '04' union all
select '05' union all
select '05' union all
select '05' union all
select '02' union all
select '06' union all
select '06' union all
select '06' union all
select '07' union all
select '07' union all
select '07' ) aa
) b
order by case when id in ('04','05') then 1 else 0 end desc ,ID
还是看不明白。
谁能改写成容易看懂的写法?