救一条SQL查询语句

[mailto520]

原表table

列1 列2 列3
aaa zzzz a12145
aaa zzzz abd11b
aaa bbb abc124
bbb zzzz abde112
bbb zzzz abde124
bbb bbb 158482
bbb bbb 124582
bbb ccd 124545

查询如果如下：

列1 列2 列3也可为空
aaa zzzz a12145
aaa bbb abc124
bbb zzzz abde112
bbb bbb 158482
bbb ccd 124545

[fcuandy]

http://topic.csdn.net/u/20080123/18/9731d130-0d4b-4c11-8d89-f2c3ca331f0c.html

见此贴，同组取一条。

你的情况是以 列1和列2来区别是否为一组，自己照着抄一下就是了。

[liangCK]

--> liangCK小梁 于2008-10-22
 
--> 生成测试数据: @T
DECLARE @T TABLE (列1 VARCHAR(3),列2 VARCHAR(4),列3 VARCHAR(7))
INSERT INTO @T
SELECT 'aaa','zzzz','a12145' UNION ALL
SELECT 'aaa','zzzz','abd11b' UNION ALL
SELECT 'aaa','bbb','abc124' UNION ALL
SELECT 'bbb','zzzz','abde112' UNION ALL
SELECT 'bbb','zzzz','abde124' UNION ALL
SELECT 'bbb','bbb','158482' UNION ALL
SELECT 'bbb','bbb','124582' UNION ALL
SELECT 'bbb','ccd','124545'

--SQL查询如下:

SELECT *
FROM @T AS a
WHERE NOT EXISTS
      (
        SELECT *
        FROM @T
        WHERE a.列1=列1
           AND a.列2=列2
           AND 列3>a.列3
      )

/*
列1   列2   列3
---- ---- -------
aaa  zzzz abd11b
aaa  bbb  abc124
bbb  zzzz abde124
bbb  bbb  158482
bbb  ccd  124545

(5 行受影响)
*/

[octwind]

select * from table t where not exists (select 1 from table where col1=t.col1 and col2=t.col2 id<t.id)

[mustudent]

Select 列1 , 列2,sum( 列3) as '列3' from t group by 列1 , 列2

[rockyvan]

当列1、列2相同时列3取最小的还是取第一条？

[hyde100]

create table table1
(
	a nvarchar(50),
	b nvarchar(50),
	c nvarchar(50),
)

insert into table1
select 'aaa',    'zzzz',    'a12145'   union all 
select 'aaa',    'zzzz',    'abd11b'      union all
select 'aaa',    'bbb',    'abc124'    

create table #table1
(
	id int identity(1,1),
	a nvarchar(50),
	b nvarchar(50),
	c nvarchar(50),
)

insert into #table1
select * from table1

select *
from #table1 a
where not exists
(
	select 1 from #table1 b where a.a = b.a and a.b = b.b and a.id > b.id
)


drop table table1
drop table #table1
结果：
1	aaa	zzzz	a12145
3	aaa	bbb	abc124