--try:
select A.proname,A.userid,C.username, (select count(*) from usepro where proName=A.proname) as [count]
from usepro A
inner join
(select proname, max(createtime) as createtime from UsePro group by proname) B
on A.proname=B.proname and A.createtime=B.createtime
inner join userinfo C
on A.userid=C.id
select A.proname,A.userid,C.username, (select count(*) from usepro where proName=A.proname) as [count]
from usepro A
inner join
(select proname, max(createtime) as createtime from UsePro group by proname) B
on A.proname=B.proname and A.createtime=B.createtime
inner join userinfo C
on A.userid=C.id
(
Id INT,
UserId INT,
ProName VARCHAR(50),
CreateTime DATETIME
)
CREATE TABLE UserInfo
(
UserId INT,
UserName VARCHAR(50)
)
INSERT INTO UsePro
SELECT 1,1,'百里','2007-1-1' UNION ALL
SELECT 2,2,'百里','2007-1-30' UNION ALL
SELECT 3,3,'的','2007-1-30'
INSERT INTO UserInfo
SELECT 1,'ddddd' UNION ALL
SELECT 2,'ffff' UNION ALL
SELECT 3,'dddd'
GO
CREATE VIEW VIEW_TEST
AS
SELECT A.UserId,B.UserName,A.ProName,A.CreateTime
FROM UsePro A INNER JOIN UserInfo
B ON A.UserId=B.UserId INNER JOIN
(SELECT ProName,max(CreateTime) CreateTime,COUNT(1) NUM FROM UsePro GROUP BY ProName) C
ON A.CreateTime=C.CreateTime AND A.ProName=C.ProName
GO
SELECT * FROM VIEW_TEST
DROP VIEW VIEW_TEST
DROP TABLE UsePro
DROP TABLE UserInfo
--结果
UserId UserName ProName CreateTime
----------- -------------------------------------------------- -------------------------------------------------- -----------------------
3 dddd 的 2007-01-30 00:00:00.000
2 ffff 百里
CREATE TABLE UsePro
(
Id INT,
UserId INT,
ProName VARCHAR(50),
CreateTime DATETIME
)
CREATE TABLE UserInfo
(
UserId INT,
UserName VARCHAR(50)
)
INSERT INTO UsePro
SELECT 1,1,'百里','2007-1-1' UNION ALL
SELECT 2,2,'百里','2007-1-30' UNION ALL
SELECT 3,3,'的','2007-1-30'
INSERT INTO UserInfo
SELECT 1,'ddddd' UNION ALL
SELECT 2,'ffff' UNION ALL
SELECT 3,'dddd'
GO
CREATE VIEW VIEW_TEST
AS
SELECT A.UserId,B.UserName,A.ProName,A.CreateTime,C.NUM
FROM UsePro A INNER JOIN UserInfo
B ON A.UserId=B.UserId INNER JOIN
(SELECT ProName,max(CreateTime) CreateTime,COUNT(1) NUM FROM UsePro GROUP BY ProName) C
ON A.CreateTime=C.CreateTime AND A.ProName=C.ProName
GO
SELECT * FROM VIEW_TEST
DROP VIEW VIEW_TEST
DROP TABLE UsePro
DROP TABLE UserInfo
--结果
UserId UserName ProName CreateTime NUM
----------- -------------------------------------------------- -------------------------------------------------- ----------------------- -----------
2 ffff 百里 2007-01-30 00:00:00.000 2
3 dddd 的 2007-01-30 00:00:00.000 1