27,579
社区成员
发帖
与我相关
我的任务
分享
-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-09-19 09:28:33
-------------------------------------
--> 生成测试数据: @build
DECLARE @build TABLE (bid VARCHAR(2),no VARCHAR(2))
INSERT INTO @build
SELECT '01','1#' UNION ALL
SELECT '02','2#'
--> 生成测试数据: @floor
DECLARE @floor TABLE (fid VARCHAR(4),fno VARCHAR(3),type1 INT)
INSERT INTO @floor
SELECT '0101','1层',1 UNION ALL
SELECT '0102','2层',1 UNION ALL
SELECT '0201','1层',1 UNION ALL
SELECT '0202','2层',2
--SQL查询如下:
SELECT
*,
宿舍类别 = CASE WHEN NOT EXISTS(SELECT * FROM @floor
WHERE LEFT(fid,2)=A.bid AND type1 = 1)
THEN '全女生'
WHEN NOT EXISTS(SELECT * FROM @floor
WHERE LEFT(fid,2)=A.bid AND type1 = 2)
THEN '全男生'
ELSE '混合'
END
FROM @build AS A
/*
bid no 宿舍类别
---- ---- ------
01 1# 全男生
02 2# 混合
(2 行受影响)
*/