求条sql语句 急等

[我2我骄傲]

A B
1 x1
1 x2
2 x3
2 x4
2 xxx5
3 xxx
3 xxxx

表中有个字段 A B 有多条记录
我只想要期中的第一条

得到的结果是

A B
1 x1
2 x3
3 xxxx
这个SQL怎么写

[wanshichen]

得到的结果是

A B
1 x1
2 x3
3 xxxx

如果是要其中第一条的话
3应该是
3 xxx 吧？

[我2我骄傲]

是的，这怎么写？

[老白猫]

对阿 这啥规律呢

[我2我骄傲]

得到的结果
A B
1 x1
2 x3
3 xxx

我多打了个X

这怎么写？

[我腫了]

SELECT a.A,b.B FROM t1 AS a
    CROSS APPLY(SELECT TOP 1 B FROM t1 AS x WHERE x.A=a.A) AS b
GROUP BY a.A,b.B
ORDER BY a.A

[我2我骄傲]

能不用Cross Apply吗？？？ Cross Apply 好像是inner join 吧。

有其他方法吗

[gw6328]

;with cte as (
select rn=row_number() over(partition by a order by getdate()),* from t
)
select * from t where rn=1;

[wanshichen]

select * from tb a where b in(select top 1 b from tb where id=a.id)

[问苍天人生几何]

declare @test table(A int, B varchar(4))
insert into @test
select 1, 'x1' union
select 1, 'x2' union
select 2, 'x3' union
select 2, 'x4' union
select 2, 'xxx5' union
select 3, 'xxx' union
select 3, 'xxxx'
select A,B from
(
	select row_number() over(partition by A order by A) rn,* from @test
) t
where t.rn=1

/*
A           B
----------- ----
1           x1
2           x3
3           xxx
*/

[ORAClE SE]

我顶。。。。declare @test table(A int, B varchar(4))
insert into @test
select 1, 'x1' union
select 1, 'x2' union
select 2, 'x3' union
select 2, 'x4' union
select 2, 'xxx5' union
select 3, 'xxx' union
select 3, 'xxxx'
select A,B from
(
select row_number() over(partition by A order by A) rn,* from @test
) t
where t.rn=1

/*
A B
----------- ----
1 x1
2 x3
3 xxx
*/

[我2我骄傲]

可以了 谢谢 马上结贴。

[我2我骄傲]

outer apply 也行。

[以学习为目的]

-->测试数据
IF OBJECT_ID('tab') IS NOT NULL
DROP TABLE tab
GO
CREATE TABLE tab(A INT,B VARCHAR(10))
INSERT INTO tab

SELECT '1','x1' UNION ALL
SELECT '1','x2' UNION ALL
SELECT '2','x3' UNION ALL
SELECT '2','x4' UNION ALL
SELECT '2','xxx5' UNION ALL
SELECT '3','xxx' UNION ALL
SELECT '3','xxxx' 
GO
--select  * from tab
-->查询
;
with 
cte as (
select rn=row_number() over(partition by A order by B),* from tab
)
select * from cte where rn=1

/*
   A    B
   1    x1
   2    x3
   3    xxx
*/

[以学习为目的]

-->测试数据
IF OBJECT_ID('tab') IS NOT NULL
DROP TABLE tab
GO
CREATE TABLE tab(A INT,B VARCHAR(10))
INSERT INTO tab

SELECT '1','x1' UNION ALL
SELECT '1','x2' UNION ALL
SELECT '2','x3' UNION ALL
SELECT '2','x4' UNION ALL
SELECT '2','xxx5' UNION ALL
SELECT '3','xxx' UNION ALL
SELECT '3','xxxx'
GO
--select * from tab
-->查询
;
with
cte as (
select rn=row_number() over(partition by A order by B),* from tab
)
select * from cte where rn=1

/*
A B
1 x1
2 x3
3 xxx
*/

[w87875251l]

select A,min(B) from @test
group by A

[發糞塗牆]

SELECT * FROM test a 
WHERE EXISTS (SELECT 1 FROM 
(SELECT A,NTILE(1) OVER(ORDER BY A) groups
FROM test) b WHERE a.A=b.A AND b.groups=1)

[xiaozhanger]

[Quote=引用 9 楼 的回复:]

with
cte as (
select rn=row_number() over(partition by A order by B),* from tab
)
select * from cte where rn=1
s……
[/Quote]

我看不懂。