22,209
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('table1') IS NOT NULL
DROP TABLE table1
IF OBJECT_ID('table2') IS NOT NULL
DROP TABLE table2
create table table1 (lid int,name nvarchar(500));
insert into table1 (lid,name)
values (1,'本子'),(2,'牙膏');
create table table2 (lid int,addTime datetime);
insert into table2 (lid,addTime)
values (1,'2015-09-14 23:59:59'),
(1,'2015-09-15 23:59:59'),
(2,'2015-09-15 23:59:59'),
(2,'2015-09-14 23:59:59');
SELECT t.name,t.addTime FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY a.lid ORDER BY b.addTime DESC) AS rid
,a.NAME
,b.addtime
FROM table1 AS a INNER JOIN table2 AS b ON a.lid=b.lid
) AS t
WHERE rid=1
/*
name addTime
本子 2015-09-15 23:59:59.000
牙膏 2015-09-15 23:59:59.000
*/
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T(gid INT,[name] nvarchar(22),[proFormat] text,[addTime] DATETIME)
Insert #T
select 1,N'测试','12','2017-01-01 00:00:00' UNION
select 1,N'测试','12','2017-02-01 00:00:00' UNION
select 1,N'测试','12','2017-03-01 00:00:00' UNION
select 2,N'测试','12','2017-04-01 00:00:00' UNION
select 2,N'测试','12','2017-05-01 00:00:00'
GO
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([id] int,[商品名] nvarchar(23))
Insert #T1
select 1,N'商品1' union all
select 2,N'商品2'
Go
--测试数据结束
SELECT name ,
CONVERT(VARCHAR(8000), proFormat) AS proFormat ,
b.商品名 ,
MAX(addTime)
FROM #T a
JOIN #T1 b ON a.gid = b.id
GROUP BY CONVERT(VARCHAR(8000), proFormat) ,
name ,
商品名
[/quote]
好了,解决了,原来之前是group by 多加了 addTime,谢谢大神的耐心解答![/quote]
记得结贴哦--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T(gid INT,[name] nvarchar(22),[proFormat] text,[addTime] DATETIME)
Insert #T
select 1,N'测试','12','2017-01-01 00:00:00' UNION
select 1,N'测试','12','2017-02-01 00:00:00' UNION
select 1,N'测试','12','2017-03-01 00:00:00' UNION
select 2,N'测试','12','2017-04-01 00:00:00' UNION
select 2,N'测试','12','2017-05-01 00:00:00'
GO
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([id] int,[商品名] nvarchar(23))
Insert #T1
select 1,N'商品1' union all
select 2,N'商品2'
Go
--测试数据结束
SELECT name ,
CONVERT(VARCHAR(8000), proFormat) AS proFormat ,
b.商品名 ,
MAX(addTime)
FROM #T a
JOIN #T1 b ON a.gid = b.id
GROUP BY CONVERT(VARCHAR(8000), proFormat) ,
name ,
商品名
[/quote]
好了,解决了,原来之前是group by 多加了 addTime,谢谢大神的耐心解答!SELECT name ,
MAX(addTime) AS addTime
FROM dbo.table1 a
JOIN dbo.table2 b ON a.lid = b.proID
GROUP BY name
create table table1 (lid int,name nvarchar(500));
insert into table1 (lid,name)
values (1,'本子'),(2,'牙膏');
create table table2 (lid int,addTime datetime);
insert into table2 (proID,addTime)
values (1,'2015-09-14 23:59:59'),
(1,'2015-09-15 23:59:59'),
(2,'2015-09-15 23:59:59'),
(2,'2015-09-14 23:59:59');
表名:table1
lid name --列名
1 本子
2 牙膏
表名:table2
lid addTime --列名
1 2015-09-14 23:59:59
1 2015-09-15 23:59:59
2 2015-09-14 23:59:59
2 2015-09-15 23:59:59
--要实现的效果:
name addTime
------- ----------- ----------- ----------- ----------- -----------
本子 2015-09-15 23:59:59
牙膏 2015-09-15 23:59:59--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T(gid INT,[name] nvarchar(22),[proFormat] text,[addTime] DATETIME)
Insert #T
select 1,N'测试','12','2017-01-01 00:00:00' UNION
select 1,N'测试','12','2017-02-01 00:00:00' UNION
select 1,N'测试','12','2017-03-01 00:00:00' UNION
select 2,N'测试','12','2017-04-01 00:00:00' UNION
select 2,N'测试','12','2017-05-01 00:00:00'
GO
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([id] int,[商品名] nvarchar(23))
Insert #T1
select 1,N'商品1' union all
select 2,N'商品2'
Go
--测试数据结束
SELECT name ,
CONVERT(VARCHAR(8000), proFormat) AS proFormat ,
b.商品名 ,
MAX(addTime)
FROM #T a
JOIN #T1 b ON a.gid = b.id
GROUP BY CONVERT(VARCHAR(8000), proFormat) ,
name ,
商品名
SELECT name ,
CONVERT(VARCHAR(8000), proFormat) AS proFormat ,其他字段
MAX(addTime)
FROM #T
GROUP BY name ,
CONVERT(VARCHAR(8000), proFormat),其他字段