id name date
1 jay 20010101
2 jay 20020203
3 tony 20020203
4 tony 20030101
得到name字段比如jay 中 date 最大直所在的行的id.
结果
2 jay 20020203
4 tony 20030101
1 jay 20010101
2 jay 20020203
3 tony 20020203
4 tony 20030101
得到name字段比如jay 中 date 最大直所在的行的id.
结果
2 jay 20020203
4 tony 20030101
from tb
group by name
select * from 表 a where id =(select max(id) from 表 where a.name=name)
insert #test select 1 ,'jay','20010101'
insert #test select 2 ,'jay','20020203'
insert #test select 3 ,'tony','20020203'
insert #test select 4 ,'tony','20030101'
select * from #test a
where not exists(select 1 from #test where a.name=name and cast(a.date as datetime)<cast(date as datetime)) id name date
----------- -------------------- --------
2 jay 20020203
4 tony 20030101(2 行受影响)
where id=(select top 1 id from [Table] where name=t.name order by date desc)
max(id),这个我不需要啊。我只要求max(date).
2个max,那ms sql,怎么判断?
select *,rn = row_number() over(partition by name order by date desc) from #test
) a
where rn=1
id name date
----------- -------------------- --------
2 jay 20020203
4 tony 20030101(2 行受影响)
not exists
我这里会超时.因为数据量比较打.
我要的是date 最大的直.按照name分组.还要得到这行的其他数据比如 id.
insert #test select 1 ,'jay','20010101'
insert #test select 2 ,'jay','20020203'
insert #test select 3 ,'tony','20020203'
insert #test select 4 ,'tony','20030101'select * from #test t where id=(select max(id) from #test where name=t.name )
/*
id name date
----------- -------------------- --------
2 jay 20020203
4 tony 20030101(2 行受影响)
*/用5楼的表数据.
1 jay 20010101
2 jay 20020203
3 tony 20020203
4 tony 20010101
得到name字段比如jay 中 date 最大直所在的行的id等字段.
结果
2 jay 20020203
3 tony 20020203
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-02 12:31:32
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] NVARCHAR(10),[name] NVARCHAR(10),[date] DATETIME)
INSERT [tb]
SELECT '1','jay','20010101' UNION ALL
SELECT '2','jay','20020203' UNION ALL
SELECT '3','tony','20020203' UNION ALL
SELECT '4','tony','20010101'
GO
--SELECT * FROM [tb]-->SQL查询如下:
select * from [tb] t
where id=(select top 1 id from [tb] where name=t.name order by date desc)
/*
id name date
---------- ---------- -----------------------
2 jay 2002-02-03 00:00:00.000
3 tony 2002-02-03 00:00:00.000(2 行受影响)
*/
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] NVARCHAR(10),[name] NVARCHAR(10),[date] DATETIME)
INSERT [tb]
SELECT '1','jay','20010101' UNION ALL
SELECT '2','jay','20020203' UNION ALL
SELECT '3','tony','20020203' UNION ALL
SELECT '4','tony','20030101'
GO
SELECT tb.* FROM tb INNER JOIN
(
SELECT NAME,MAX(date) AS date FROM tb GROUP BY [name]
) a
ON tb.[name]=a.[NAME] AND tb.date=a.date
ORDER BY id ASC
(4 行受影响)
id name date
---------- ---------- -----------------------
2 jay 2002-02-03 00:00:00.000
4 tony 2003-01-01 00:00:00.000(2 行受影响)
这个中Group By,没有太大的意义!
insert #test select 1 ,'jay','20010101'
insert #test select 2 ,'jay','20020203'
insert #test select 3 ,'tony','20020203'
insert #test select 4 ,'tony','20030101'select * from #test a where date=(
select max(b.date) from #test b where b.name=a.name)id name date
----------- -------------------- --------
4 tony 20030101
2 jay 20020203(所影响的行数为 2 行)
--基本速度快的方法都出来了
--据说这种最快
select
*
from
your_table a
where
date=(select max(date) from your_table where name=a.name)--不过该加的索引还是得加上
8 jay 20010101
2 jay 20020203
3 tony 20020203
4 tony 20010101
得到name字段比如jay 中 date 最大直所在的行的id等字段.
结果
8 jay 20020203
3 tony 20020203
1 abc
1 bcd
1 asdfgselect A,B from table group by A
你说这样查出来是什么结果,A B
abc
1 bcd
asdfg右边3条如何变成一条,所以需要用到聚合函数,比如select A,count(B) 数量 from table group by A
这样的结果就是
A 数量
1 3group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面