表TB1 里有字段SOID 和ACCOUNTID
数据有如下
SOID ACCOUNTID
S0001 A01
S0002 A01
S0003 A01
S1001 A02
S1003 A02
S2001 A03
S2004 A03我想要select到以下结果
SOID ACCOUNTID
S0001 A01
S1003 A02
S2001 A03也就是每个ACCOUNTID取一条 SOID记录
求高手
数据有如下
SOID ACCOUNTID
S0001 A01
S0002 A01
S0003 A01
S1001 A02
S1003 A02
S2001 A03
S2004 A03我想要select到以下结果
SOID ACCOUNTID
S0001 A01
S1003 A02
S2001 A03也就是每个ACCOUNTID取一条 SOID记录
求高手
select min(SOID) SOID,ACCOUNTID
from tb
group by ACCOUNTID
-- Author : htl258(Tony)
-- Date : 2010-04-28 23:01:21
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([SOID] NVARCHAR(10),[ACCOUNTID] NVARCHAR(10))
INSERT [tb]
SELECT 'S0001','A01' UNION ALL
SELECT 'S0002','A01' UNION ALL
SELECT 'S0003','A01' UNION ALL
SELECT 'S1001','A02' UNION ALL
SELECT 'S1003','A02' UNION ALL
SELECT 'S2001','A03' UNION ALL
SELECT 'S2004','A03'
GO
--SELECT * FROM [tb]-->SQL查询如下:
SELECT MIN(SOID) SOID,ACCOUNTID
FROM TB
GROUP BY ACCOUNTIDSELECT * FROM TB T
WHERE SOID=(
SELECT MIN(SOID) FROM TB
WHERE ACCOUNTID=T.ACCOUNTID)
/*
SOID ACCOUNTID
---------- ----------
S0001 A01
S1001 A02
S2001 A03(3 行受影响)
*/
*
from
tb t
where
not exists(select 1 from tb where ACCOUNTID=t.ACCOUNTID and SOID<t.SOID)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-28 23:04:47
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([SOID] varchar(5),[ACCOUNTID] varchar(3))
insert [tb]
select 'S0001','A01' union all
select 'S0002','A01' union all
select 'S0003','A01' union all
select 'S1001','A02' union all
select 'S1003','A02' union all
select 'S2001','A03' union all
select 'S2004','A03'
--------------开始查询--------------------------
select
*
from
tb t
where
not exists(select 1 from tb where ACCOUNTID=t.ACCOUNTID and SOID<t.SOID)
----------------结果----------------------------
/* SOID ACCOUNTID
----- ---------
S0001 A01
S1001 A02
S2001 A03(3 行受影响)
*/
select max(SOID) SOID, ACCOUNTID from tb1 group by ACCOUNTID--如果不止这两字段
select t.* from tb1 t where SOID = (select max(SOID) from tb1 where ACCOUNTID = t.ACCOUNTID)
select t.* from tb1 t where not exists (select 1 from tb1 where ACCOUNTID = t.ACCOUNTID and SOID > t.SOID)
数据有如下
SOID ACCOUNTID
S0001 A01
S0002 A01
S0003 A01
S1001 A02
S1003 A02
S2001 A03
S2004 A03我想要select到以下结果
SOID ACCOUNTID
S0001 A01
S1003 A02
S2001 A03也就是每个ACCOUNTID取一条 SOID记录
求高手
create table TB1
(
SOID varchar(50),
ACCOUNTID varchar(50)
)
insert into TB1(SOID,ACCOUNTID)
(
select 'S0001','A01' union
select 'S0002','A01' union
select 'S0003','A01' union
select 'S1001','A02' union
select 'S1003','A02' union
select 'S2001','A03' union
select 'S2004','A03'
)
select * from TB1
go
select * from TB1 A where A.SOID in(select min(SOID) from TB1 B where B.ACCOUNTID=A.ACCOUNTID)
go
Select Min(SoID) SoID,AccountID From tb Group By AccountID
多列:
Select * From tb t Where SoID = (Select Min(SoID) From tb Where AccountID = t.AccountID)
(
SOID varchar(100),
ACCOUNTID varchar(100)
)insert into @tb select 'S0001', 'A01' union all
select 'S0002','A01' union all
select 'S0003','A01' union all
select 'S1001','A02' union all
select 'S1003','A02' union all
select 'S2001','A03' union all
select 'S2004','A03' select min(soid),accountid from @tb group by accountid
--静态的
select * from @tb t where soid =(select min(soid) from @tb where ACCOUNTID=t.ACCOUNTID )---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
S0001 A01
S1001 A02
S2001 A03(3 行受影响)SOID ACCOUNTID
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
S0001 A01
S1001 A02
S2001 A03
from tb
group by ACCOUNTID
from tb
where SOID in
(select min(SOID)
from tb
group by ACCOUNTID)