我的如下的表结构: 表A
ClassID intime StockUniqueid
0001 2007-01-05 B1EFE8A6-CD2E-424F-BFCF-FBA279771D64
0002 2006-09-06 9DD02640-5A6A-4C55-9808-235F6CD78910
0001 2008-01-15 005B9623-6F90-49AF-93A7-DC78F10A0EDD
0006 2007-12-04 2D1A261F-3C47-4DF9-B744-8F5A08873089
0002 2007-11-26 8EFF3D4A-E990-49C2-A97B-13F6BCCED6DA
0008 2005-01-11 9BFC5943-8F7F-47FF-A83B-A1EC3121E75B
我的目的是想取ClassID 相同的 intime 最大的 StockUniqueid 这个的值。。如何实现??
就是想取 StockUniqueid 这个唯一值。。以上表的结果应该是如下。
ClassID StockUniqueid
0001 005B9623-6F90-49AF-93A7-DC78F10A0EDD
0002 8EFF3D4A-E990-49C2-A97B-13F6BCCED6DA
0006 2D1A261F-3C47-4DF9-B744-8F5A08873089
0008 9BFC5943-8F7F-47FF-A83B-A1EC3121E75B
ClassID intime StockUniqueid
0001 2007-01-05 B1EFE8A6-CD2E-424F-BFCF-FBA279771D64
0002 2006-09-06 9DD02640-5A6A-4C55-9808-235F6CD78910
0001 2008-01-15 005B9623-6F90-49AF-93A7-DC78F10A0EDD
0006 2007-12-04 2D1A261F-3C47-4DF9-B744-8F5A08873089
0002 2007-11-26 8EFF3D4A-E990-49C2-A97B-13F6BCCED6DA
0008 2005-01-11 9BFC5943-8F7F-47FF-A83B-A1EC3121E75B
我的目的是想取ClassID 相同的 intime 最大的 StockUniqueid 这个的值。。如何实现??
就是想取 StockUniqueid 这个唯一值。。以上表的结果应该是如下。
ClassID StockUniqueid
0001 005B9623-6F90-49AF-93A7-DC78F10A0EDD
0002 8EFF3D4A-E990-49C2-A97B-13F6BCCED6DA
0006 2D1A261F-3C47-4DF9-B744-8F5A08873089
0008 9BFC5943-8F7F-47FF-A83B-A1EC3121E75B
from tablea a
where not exists(select 1
from tablea
where ClassID = a.ClassID and StockUniqueid > a.StockUniqueid )
from A where not exists(select 1 from A t where ClassID=A.ClassID and intime>A.intime)
order by ClassID
但不得出现这样的错
" 列 '表A.StockUniqueid'在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。"
insert @t select
'0001','2007-01-05','B1EFE8A6-CD2E-424F-BFCF-FBA279771D64' union select
'0002','2006-09-06','9DD02640-5A6A-4C55-9808-235F6CD78910' union select
'0001','2008-01-15','005B9623-6F90-49AF-93A7-DC78F10A0EDD' union select
'0006','2007-12-04','2D1A261F-3C47-4DF9-B744-8F5A08873089' union select
'0002','2007-11-26','8EFF3D4A-E990-49C2-A97B-13F6BCCED6DA' union select
'0008','2005-01-11','9BFC5943-8F7F-47FF-A83B-A1EC3121E75B'select *
from @t a
where not exists(select 1
from @t
where ClassID = a.ClassID and StockUniqueid < a.StockUniqueid )
/*
ClassID intime StockUniqueid
-------- ------------------------------------------------------ ----------------------------------------------------------------------------------------------------
0001 2008-01-15 00:00:00.000 005B9623-6F90-49AF-93A7-DC78F10A0EDD
0002 2007-11-26 00:00:00.000 8EFF3D4A-E990-49C2-A97B-13F6BCCED6DA
0006 2007-12-04 00:00:00.000 2D1A261F-3C47-4DF9-B744-8F5A08873089
0008 2005-01-11 00:00:00.000 9BFC5943-8F7F-47FF-A83B-A1EC3121E75B(所影响的行数为 4 行)
*/
(
ClassID varchar(10),
intime varchar(10),
StockUniqueid varchar(100)
)insert into t1
select '0001','2007-01-05','B1EFE8A6-CD2E-424F-BFCF-FBA279771D64' union all
select '0002','2006-09-06','9DD02640-5A6A-4C55-9808-235F6CD78910' union all
select '0001','2008-01-15','005B9623-6F90-49AF-93A7-DC78F10A0EDD' union all
select '0006','2007-12-04','2D1A261F-3C47-4DF9-B744-8F5A08873089' union all
select '0002','2007-11-26','8EFF3D4A-E990-49C2-A97B-13F6BCCED6DA' union all
select '0008','2005-01-11','9BFC5943-8F7F-47FF-A83B-A1EC3121E75B'
select classid,stockuniqueid from t1 a where not exists(select 1 from t1 where t1.classid=a.classid and t1.intime>a.intime) order by classid
select * from 表A a where not exists(select 1 from 表A where ClassID=a.ClassID and intime>a.intime)--或者
select * from 表A a where intime in (select max(intime) from 表A where ClassID=a.ClassID) --或者
select * from 表A a where
(select count(distinct intime) from 表A where ClassID=a.ClassID and intime>=a.intime)=1 ----等等``
--1
select classId,Stockuniqueid from tb a
where not exists(select 1 from tb b where a.classId=b.classId and a.Intime<b.Intime)
--2
select classId,Stockuniqueid from tb a
where Intime in (select Max(Intime) as Intime from tb b where a.classId=b.classId)
--3
select a.classId,a.Stockuniqueid from tb a,(select ClassId,max(Intime) as Intime from tb group by ClassId) b
where a.classId=b.classId and a.INtime=b.Intime/code]
INSERT INTO @TABLE1
SELECT '0001', '2007-01-05', 'B1EFE8A6-CD2E-424F-BFCF-FBA279771D64'
UNION
SELECT '0002', '2006-09-06', '9DD02640-5A6A-4C55-9808-235F6CD78910'
UNION
SELECT '0001', '2008-01-15', '005B9623-6F90-49AF-93A7-DC78F10A0EDD'
UNION
SELECT '0006', '2007-12-04', '2D1A261F-3C47-4DF9-B744-8F5A08873089'
UNION
SELECT '0002', '2007-11-26', '8EFF3D4A-E990-49C2-A97B-13F6BCCED6DA'
UNION
SELECT '0008', '2005-01-11', '9BFC5943-8F7F-47FF-A83B-A1EC3121E75B'SELECT T1.ClassID, T1.StockUniqueid
FROM @TABLE1 T1 WHERE NOT EXISTS (SELECT 1 FROM @TABLE1 WHERE ClassID=T1.ClassID AND intime>T1.intime)
--或者
SELECT T1.ClassID, T1.StockUniqueid
FROM @TABLE1 T1 WHERE T1.intime = (SELECT MAX(intime) FROM @TABLE1 WHERE ClassID=T1.ClassID)
insert @t select
'0001','2007-01-05','B1EFE8A6-CD2E-424F-BFCF-FBA279771D64' union select
'0002','2006-09-06','9DD02640-5A6A-4C55-9808-235F6CD78910' union select
'0001','2008-01-15','005B9623-6F90-49AF-93A7-DC78F10A0EDD' union select
'0006','2007-12-04','2D1A261F-3C47-4DF9-B744-8F5A08873089' union select
'0002','2007-11-26','8EFF3D4A-E990-49C2-A97B-13F6BCCED6DA' union select
'0008','2005-01-11','9BFC5943-8F7F-47FF-A83B-A1EC3121E75B'select * from @t
where intime in (select max(intime) from @t group by Classid)
可以这样使用 group by
from A group by classid
from A
inner join (select classid,max(intime ) intime from a group by classid ) as b
on a.classid = b.classid and a.intime =b.intime 刚才领会错了 :)
from A t where not exists(select 1 from A where ClassID=t.ClassID and intime>t.intime)
insert @t select
'0001','2007-01-05','B1EFE8A6-CD2E-424F-BFCF-FBA279771D64' union select
'0002','2006-09-06','9DD02640-5A6A-4C55-9808-235F6CD78910' union select
'0001','2008-01-15','005B9623-6F90-49AF-93A7-DC78F10A0EDD' union select
'0006','2007-12-04','2D1A261F-3C47-4DF9-B744-8F5A08873089' union select
'0002','2007-11-26','8EFF3D4A-E990-49C2-A97B-13F6BCCED6DA' union select
'0008','2005-01-11','9BFC5943-8F7F-47FF-A83B-A1EC3121E75B'
select ClassID, StockUniqueid
from @t t where not exists(select 1 from @t where ClassID=t.ClassID and intime>t.intime)
/*
ClassID StockUniqueid
-------- ----------------------------------------------------------------------------------------------------
0001 005B9623-6F90-49AF-93A7-DC78F10A0EDD
0002 8EFF3D4A-E990-49C2-A97B-13F6BCCED6DA
0006 2D1A261F-3C47-4DF9-B744-8F5A08873089
0008 9BFC5943-8F7F-47FF-A83B-A1EC3121E75B
*/
drop table tb
go
create table tb(ClassID varchar(8),intime datetime,StockUniqueid varchar(100))
insert tb select
'0001','2007-01-05','B1EFE8A6-CD2E-424F-BFCF-FBA279771D64' union select
'0002','2006-09-06','9DD02640-5A6A-4C55-9808-235F6CD78910' union select
'0001','2008-01-15','005B9623-6F90-49AF-93A7-DC78F10A0EDD' union select
'0006','2007-12-04','2D1A261F-3C47-4DF9-B744-8F5A08873089' union select
'0002','2007-11-26','8EFF3D4A-E990-49C2-A97B-13F6BCCED6DA' union select
'0008','2005-01-11','9BFC5943-8F7F-47FF-A83B-A1EC3121E75B'select a.intime,StockUniqueid from tb a inner join
(select ClassID,max(intime) as intime from tb group by ClassID) b
on a.ClassID=b.ClassID and a.intime=b.intime
from A
where intime= (select max(intime) from A as t where t.ClassID = A.ClassID)
order by A.ClassID