SQL查询
有以下数据：
id type level price
1 m_p1 1 50
2 m_p2 1 50
3 m_p3 2 30
4 m_p3 2 30
查询level的值（1，2）各自对应一条数据
要得到以下结果
id type level price
1 m_p1 1 50
3 m_p3 2 30
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-02-21 17:55:32
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
--Jun 17 2011 00:54:03
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[type] varchar(4),[level] int,[price] int)
insert [huang]
select 1,'m_p1',1,50 union all
select 2,'m_p2',1,50 union all
select 3,'m_p3',2,30 union all
select 4,'m_p3',2,30
--------------开始查询--------------------------
SELECT *
FROM ( SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY [level] ORDER BY id ) pid
FROM [huang] a
WHERE [level] IN ( 1, 2 )
) a
WHERE pid = 1
----------------结果----------------------------
/*
id type level price pid
----------- ---- ----------- ----------- --------------------
1 m_p1 1 50 1
3 m_p3 2 30 1
(2 行受影响)
*/
