取最大值
CREATE TABLE TBA
(
JO002 nvarchar(50),
JO014 nvarchar(50),
JO027 nvarchar(50)
)
INSERT INTO TBA
select 'QBC116','2012/07/20 13:00:00','240' union
select 'QBC116','2012/08/11 08:00','240' union
select 'QBC116','2012/08/11 08:00','180' union
select 'QBC135','2012/07/31 08:00:00','240' union
select 'QBC136','2012/09/08 08:00','2610' union
select 'QBC136','2012/09/10 08:00','1320' union
select 'QBC179','2012/08/23 11:00','360' union
select 'QBC179','2012/08/22 11:00','360' union
select 'QBC197','2012/08/28 10:00','360' union
select 'QBC197','2012/08/28 08:00','360' union
select 'QBC198','2012/09/06 08:00','360' union
select 'QBC198','2012/08/28 08:00','360' union
select 'QZC041','2012/07/31 08:00:00','4758' union
select 'QZC041','2012/06/23 00:00:00','2440.2' union
select 'QZC041','2012/06/24 00:00:00','1219.8' union
select 'QZC042','2012/07/31 08:00:00','4758' union
select 'QZC042','2012/06/29 15:00:00','2440.2' union
select 'QZC042','2012/07/02 08:00:00','1219.8'
[最优解释]
WITH tb
AS ( SELECT JO002 ,
MAX(JO027) AS 'JO027'
FROM #TBA
GROUP BY JO002
)
SELECT a.* ,
( SELECT MAX(JO014)
FROM #TBA b
WHERE b.JO027 = a.JO027
AND b.JO002 = a.JO002
) AS 'JO014'
FROM tb a
[其他解释]
select J0002,(select MAX(J0004) from TBA where J0002=a.J0002 and J0027=MAX(a.J0027)) as J0014,MAX(J0027) as J0027
from TBA
group by J0002
[其他解释]
select
JO002,
MAX(JO014)
from
TBA as A
where
NOT EXISTS(select 1 from TBA where A.JO002=JO002 AND A.JO027<JO027 )
GROUP BY
JO002
[其他解释]
select max(A.JO014),A.JO002,B.JO027 from TBA A,
(select max(JO027) as JO027,JO002 from TBA group by JO002) B
where A.JO002=B.JO002
group by A.JO002,B.JO027
(8 行受影响)
-------
2012/08/11 08:00QBC116240
2012/07/31 08:00:00QBC135240
2012/09/10 08:00QBC1362610
2012/08/23 11:00QBC179360
2012/08/28 10:00QBC197360
2012/09/06 08:00QBC198360
2012/07/31 08:00:00QZC0414758
2012/07/31 08:00:00QZC0424758
[其他解释]
select max(A.JO014),A.JO002,B.JO027 from TBA A,
(select max(JO027) as JO027,JO002 from TBA group by JO002) B
where A.JO002=B.JO002 and A.JO027=B.JO027
group by A.JO002,B.JO027
(8 行受影响)
---------
2012/08/11 08:00QBC116240
2012/07/31 08:00:00QBC135240
2012/09/08 08:00QBC1362610
2012/08/23 11:00QBC179360
2012/08/28 10:00QBC197360
2012/09/06 08:00QBC198360
2012/07/31 08:00:00QZC0414758
2012/07/31 08:00:00QZC0424758
[其他解释]
日哦，你的问题是，“按照JO002分组，取JO027最大值所对应的JO014的最大值”，图片是“按照JO002分组，取JO027最大值所对应的JO014的第一条数据”，害老子整了这么久。
[其他解释]
呵呵不好意 第一条让我写错了
[其他解释]
取第一条要这么写
WITH CTE AS(
SELECT CTE.JO002,TBA.JO014,CTE.JO027,ROW_NUMBER() OVER (ORDER BY CTE.JO002) JB
FROM (SELECT JO002,MAX(JO027) JO027 FROM TBA GROUP BY JO002 )CTE,TBA
WHERE CTE.JO002=TBA.JO002 AND CTE.JO027=TBA.JO027
)
SELECT JO002,JO014,JO027 FROM CTE A
WHERE NOT EXISTS(
SELECT 1 FROM CTE B WHERE A.JO002=B.JO002 AND A.JB<B.JB
)
--结果
JO002 JO014 JO027
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
QBC116 2012/07/20 13:00:00 240
QBC135 2012/07/31 08:00:00 240
QBC136 2012/09/08 08:00 2610
QBC179 2012/08/23 11:00 360
QBC197 2012/08/28 10:00 360
QBC198 2012/09/06 08:00 360
QZC041 2012/07/31 08:00:00 4758
QZC042 2012/07/31 08:00:00 4758
(8 行受影响)
[其他解释]
这是什么用法 啊 第一次见
------其他解决方案--------------------
SQL Server CET 通用表表达式
[其他解释]
[其他解释]
with cte as
(select
*
from
tba a
where not exists(select
*
from
tba b where a.jo002=b.jo002 and a.jo027<b.jo027))
select
*
from
cte a
where not exists (select
*
from
cte b where a.jo002=b.jo002 and a.jo014<b.jo014)
JO002 JO014 JO027
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
QBC116 2012/08/11 08:00 240
QBC135 2012/07/31 08:00:00 240
QBC136 2012/09/08 08:00 2610
QBC179 2012/08/23 11:00 360
QBC197 2012/08/28 10:00 360
QBC198 2012/09/06 08:00 360
QZC041 2012/07/31 08:00:00 4758
QZC042 2012/07/31 08:00:00 4758
(8 行受影响)
[其他解释]
select
*
from
tba a
where not exists(select
*
from
tba b where a.jo002=b.jo002 and a.jo027<b.jo027)
and not exists(select
*
from
tba c where c.jo002=a.jo002 and c.jo027=a.jo027 and a.jo014<c.jo014
) --sql 2000可以这样子