27,580
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-03-11 22:17:53
-- Verstion:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[txt] varchar(4),[NO] int)
insert [tb]
select 1,'在岗',1 union all
select 2,'病假',2 union all
select 3,'事假',2 union all
select 4,'年休',3 union all
select 5,'婚假',3 union all
select 6,'出差',4 union all
select 7,'产假',3
--------------开始查询--------------------------
select
no,
max(case no when 1 then '在岗' when 2 then '请假' when 3 then '休假' when 4 then '出差' else '' end) as 'txt'
from
tb
group by
no
----------------结果----------------------------
/*
no txt
----------- ----
1 在岗
2 请假
3 休假
4 出差
(所影响的行数为 4 行)
*/
select distinct no,
(case txt when '事假 ' then '请假 '
when '病假 ' then '请假 '
when '年休 ' then '休假 '
when '婚假 ' then '休假 '
else txt end) txt
from consttable