不好意思,group列搞错了 SELECT MAX(DISTINCT BMMC) AS BMMC, BMBH, MAX(DISTINCT LXR) AS LXR, MAX(DISTINCT TEL) AS TEL, MAX(Val) AS [VALUE] FROM dbo.[@T] GROUP BY BMBH
select * from 表 t where not exists(select 1 from 表 where BMBH=t.BMBH and VALUE>t.VALUE)
select a.* from test a where a.t4+a.t5=(select max(t4+t5) from test where t1+t2+t3=a.t1+a.t2+a.t3)
其实如果是按照你这个显示结果也归根为“显示BMMC不重复的而VALUE为最大的记录” select BMMC BMBH LXR TEL valu from table t where BMMC in (select max(VALUE) from table c where c.BMMC=t.BMCC)
上面那条写错了,应该是这样才可以 select BMMC,BMBH,LXR,TEL,value from [table] t where value in (select max(VALUE) from [table] c where c.BMMC=t.BMMC)
SELECT MAX(DISTINCT BMMC) AS BMMC, BMBH, MAX(DISTINCT LXR) AS LXR, MAX(DISTINCT TEL) AS TEL, MAX(Val) AS [VALUE] FROM dbo.[@T] GROUP BY BMBH 这种方法根本就不对啊。你怎么知道每组中各列最大值都是在同一条记录?vivianfdlpw的方法就很好啊。select BMMC,BMBH,LXR,TEL,value from [table] t where value=(select max(VALUE) from [table] c where c.BMMC=t.BMMC)这样也可以。或者求出每组中的BMBH和max(value),然后与原进行表连接。总的来说vivianfdlpw的方法最好(至少目前看来,我是这样认为的)
顺便说一下 SELECT MAX(DISTINCT BMMC) AS BMMC, BMBH, MAX(DISTINCT LXR) AS LXR, MAX(DISTINCT TEL) AS TEL, MAX(Val) AS [VALUE] FROM dbo.[@T] GROUP BY BMBH和 SELECT MAX(BMMC) AS BMMC, BMBH, MAX(LXR) AS LXR, MAX(TEL) AS TEL, MAX(Val) AS [VALUE] FROM dbo.[@T] GROUP BY BMBH两者是等价的, 除了前者更慢一些。
SELECT MAX(DISTINCT BMMC) AS BMMC, BMBH, MAX(DISTINCT LXR) AS LXR,
MAX(DISTINCT TEL) AS TEL, MAX(Val) AS [VALUE]
FROM dbo.[@T]
GROUP BY BMBH
where not exists(select 1 from 表 where BMBH=t.BMBH and VALUE>t.VALUE)
select a.* from test a where a.t4+a.t5=(select max(t4+t5) from test where t1+t2+t3=a.t1+a.t2+a.t3)
select
BMMC BMBH LXR TEL valu
from table t where BMMC in (select max(VALUE) from table c where c.BMMC=t.BMCC)
select BMMC,BMBH,LXR,TEL,value from [table] t where value in
(select max(VALUE) from [table] c where c.BMMC=t.BMMC)
Max和DISTINCT就解决的问题,
竟然吧子查询、where子句都用上了,
楼上的这样做不会是为了提高数据库性能吧?
不知道那位高手能指教一下
MAX(DISTINCT TEL) AS TEL, MAX(Val) AS [VALUE]
FROM dbo.[@T]
GROUP BY BMBH
这种方法根本就不对啊。你怎么知道每组中各列最大值都是在同一条记录?vivianfdlpw的方法就很好啊。select BMMC,BMBH,LXR,TEL,value from [table] t where value=(select max(VALUE) from [table] c where c.BMMC=t.BMMC)这样也可以。或者求出每组中的BMBH和max(value),然后与原进行表连接。总的来说vivianfdlpw的方法最好(至少目前看来,我是这样认为的)
SELECT MAX(DISTINCT BMMC) AS BMMC, BMBH, MAX(DISTINCT LXR) AS LXR,
MAX(DISTINCT TEL) AS TEL, MAX(Val) AS [VALUE]
FROM dbo.[@T]
GROUP BY BMBH和
SELECT MAX(BMMC) AS BMMC, BMBH, MAX(LXR) AS LXR,
MAX(TEL) AS TEL, MAX(Val) AS [VALUE]
FROM dbo.[@T]
GROUP BY BMBH两者是等价的, 除了前者更慢一些。