日期:2014-05-16 浏览次数:20606 次
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-07 20:22:47
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([W] nvarchar(2),[U] nvarchar(2),[V] nvarchar(2),[X] int,[Y] nvarchar(2),[Z] int)
insert [A]
select 'A','B','C',37,N'张',11 union all
select 'A','B','C',37,N'王',12 union all
select 'A','B','C',37,N'李',13 union all
select 'A','B','C',37,N'赵',1 union all
select 'e','f','g',36,N'黄',3
--------------生成数据--------------------------
SELECT CASE WHEN id=1 THEN w ELSE '' END W,CASE WHEN id=1 THEN u ELSE '' END u,
CASE WHEN id=1 THEN v ELSE '' END v,
ISNULL(CASE WHEN id=1 THEN CAST(x AS VARCHAR(3)) ELSE NULL END,'') x,y,z
FROM (
SELECT * ,(SELECT COUNT(1) FROM a AS b WHERE a.w=b.w AND a.u=b.u AND a.v=b.v AND a.x=b.x AND a.y>=b.y)id
FROM a)a
----------------结果----------------------------
/*
W u v x y z
---- ---- ---- ---- ---- -----------
A B C 37 张 11
王 12
李 13
赵 1
e f g 36 黄 3
*/