日期:2014-05-17 浏览次数:20447 次
----------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2013-09-24 15:30:18
-- Verstion:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
-- Feb 10 2012 19:39:15
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([mid] int,[mvalue] int)
insert [tb]
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 1,2 union all
select 2,2 union all
select 3,2 union all
select 1,3 union all
select 2,3 union all
select 3,3 union all
select 1,4 union all
select 2,4 union all
select 3,4
--------------开始查询--------------------------
SELECT
mvalue,
MAX(CASE WHEN px=1 THEN mid END) AS n1,
MAX(CASE WHEN px=2 THEN mid END) AS n2,
MAX(CASE WHEN px=3 THEN mid END) AS n3
FROM
(SELECT *, px=(SELECT COUNT(1)+1 FROM TB WHERE mvalue=t.mvalue AND mid<t.mid) FROM TB T)t
GROUP BY
mvalue
----------------结果----------------------------
/* mvalue n1 n2 n3
----------- ----------- ----------- -----------
1 1 2 3
2 1 2 3
3 1