分组取最大最小值的行中其它列的值

[wynlc]

[习惯性蹭分]

if OBJECT_ID('test') is not null
drop table test
go
create table test(J0001 char(13),J0002 char(6),J0007 datetime)
insert into test
select 'QBC209:1710','QBC209','2012/09/22 00:00:00' UNION ALL
SELECT 'QBC209:1720','QBC209','2012/10/20 00:00:00' UNION ALL
SELECT 'QBC209:2070','QBC209','2012/10/20 00:00:00' UNION ALL
SELECT 'QBC209:6000','QBC209','2012/10/20 00:00:00' UNION ALL
SELECT 'QZC272:1100','QZC272','2012/08/30 00:00:00' UNION ALL
SELECT 'QZC272:1105','QZC272','2012/08/30 00:00:00' UNION ALL
SELECT 'QZC272:5999','QZC272','2012/09/24 00:00:00' UNION ALL
SELECT 'QZC272:6000','QZC272','2012/09/24 00:00:00'
 /*SELECT *  FROM test a
 WHERE EXISTS (SELECT 1 FROM (SELECT j0002,MIN(CONVERT(INT,SUBSTRING(J0001,8,4)))
  minj0007,MAX(CONVERT(INT,SUBSTRING(J0001,8,4)))maxj0007 FROM test GROUP BY j0002)b WHERE a.j0002=b.j0002) 
3楼的方法，我测试没通过
  */
;with sel as(
SELECT   J0002,case when J0001=(select MIN(J0001) from test group by J0002 having J0002=a.J0002) then J0007 
 end as MINJ,case when J0001=(select MAX(J0001) from test group by J0002 having J0002=a.J0002) then J0007
 end as MAXJ from test as a)
 select J0002,MAX(minj)as minj0007,MAX(maxj) as maxj0007 from sel group by J0002

[發糞塗牆]

别看3楼了，当时懒得搞数据。随便写的，要看看7楼

[开启时代]

select j0002,MIN(j0007) minj0007 ,MAX(j0007) maxj0007 from 表1 group by j0002

[wynlc]

引用 1 楼 lixzhong 的回复:

select j0002,MIN(j0007) minj0007 ,MAX(j0007) maxj0007 from 表1 group by j0002

不对 我是以JO002分组 取JO001最大值和最小值所在的行的JO007的值生成MAXJO007和MINJO007

[發糞塗牆]

SELECT * 
FROM 表1 a
WHERE EXISTS (SELECT 1 FROM (
SELECT j0002,MIN(CONVERT(INT,SUBSTRING(JO001,7,4))) minj0007,MAX(CONVERT(INT,SUBSTRING(JO001,7,4)))maxj0007
FROM 表1
GROUP BY j0002)b WHERE a.j0002=b.j0002)

[开启时代]

select j0002,(select j0007 from 表1 where j0001=MIN(a.j0001)) minj0007,(select j0007 from 表1 where j0001=max(a.j0001)) maxj0007 from 表1 as a group by j0002

[wynlc]

引用 3 楼 DBA_Huangzj 的回复:

SQL code123456SELECT * FROM 表1 aWHERE EXISTS (SELECT 1 FROM (SELECT j0002,MIN(CONVERT(INT,SUBSTRING(JO001,7,4))) minj0007,MAX(CONVERT(INT,SUBSTRING(JO001,7,4)))maxj0007FROM 表1GROUP BY j00……

我测试了 不行

[wynlc]

引用 4 楼 lixzhong 的回复:

select j0002,(select j0007 from 表1 where j0001=MIN(a.j0001)) minj0007,(select j0007 from 表1 where j0001=max(a.j0001)) maxj0007 from 表1 as a group by j0002

这个对

[發糞塗牆]

SELECT a.jo002,MAX(CASE WHEN jo001=minjo007 THEN jo007 END ) minjo007,MAX(CASE WHEN jo001=maxjo007 THEN jo007 END) maxjo007
FROM TB a LEFT JOIN 
(
SELECT jo002,MIN(jo001) minjo007,MAX(jo001)maxjo007
FROM tb
GROUP BY jo002)b ON a.jo002=b.jo002 
GROUP BY a.jo002