向大家请教一个很复杂的视图???
两张表
UsePro(Id,UserId,ProName,ProDesc,CreateTime) 商品表
UserInfo(UserId,UserName,HeadImage) 用户表
现在是建立一张 Distinct ProName 的视图
因为同一个ProName 有可能有不同的UserId 发上来。
表如下：
UsePro UserInfo
Id ProName UserId createtime Id USerName
1 百里 1 2007-1-1 1 ddddd
2 百里 2 2007-1-30 2 ffff
3 的 3 3 dddd
视图如下：
ProName UserId UserName Count
物品名 最新的一个发布用户 对应的用户名 该物品对应多少个UserId
百里 2 ffff 2
的 3 dddd 1
[解决办法]
--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
[解决办法]
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
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 百里
