求句SQL

[hzybc]

表中的数据：

RQ H Je1 Je2
2009-1-1 A 200 0
2009-1-18 A-1 200 56
2009-3-6 B 500 0
2009-3-14 B-1 500 60

想要得到
RQ H Je1 Je2
2009-1-1 A 200 56
2009-3-6 B 500 60

[SQL77]

SELECT MIN(RQ)RQ,LEFT(H,1)H,JE1,SUM(JE2)JE2 FROM TB GROUP BY LEFT(H,1)H,JE1

[水族杰纶]

--> Title  : Generating test data #T
--> Author : wufeng4552
--> Date   : 2009-10-27
if object_id('tempdb.dbo.#T') is not null drop table #T
go
create table #T (RQ datetime,H varchar(3),Je1 int,Je2 int)
insert into #T
select '2009-1-1','A',200,0 union all
select '2009-1-18','A-1',200,56 union all
select '2009-3-6','B',500,0 union all
select '2009-3-14','B-1',500,60
select min(RQ)RQ,
       min(H)H,
       min(Je1)Je1,
       max(Je2)je2
from #t 
group by convert(varchar(7),rq,120)
/*
RQ                      H    Je1         je2
----------------------- ---- ----------- -----------
2009-01-01 00:00:00.000 A    200         56
2009-03-06 00:00:00.000 B    500         60

*/

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-10-27 11:57:20
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([RQ] datetime,[H] varchar(3),[Je1] int,[Je2] int)
insert [tb]
select '2009-1-1','A',200,0 union all
select '2009-1-18','A-1',200,56 union all
select '2009-3-6','B',500,0 union all
select '2009-3-14','B-1',500,60
--------------开始查询--------------------------
select * from tb t where not exists(select 1 from tb where substring(h,1,1)=substring(t.h,1,1) and Je2>t.Je2)
----------------结果----------------------------
/* RQ                      H    Je1         Je2
----------------------- ---- ----------- -----------
2009-01-18 00:00:00.000 A-1  200         56
2009-03-14 00:00:00.000 B-1  500         60

(2 行受影响)

*/
 

[--小F--]

晕 搞错