新闻表List由list_id list_title list_date user_id user_name组成
评论表ping 由 ping_id user_id user_name list_id ping_date
现建立一个视图查询实现如下显示
list_id list_title list_date user_id user_name ping_user_id ping_user_name ping_date ping_count
新闻ID 新闻标题 发布日期 发布人ID 发布人用户名 最后评论人ID 最后评论人用户名 最后评论的日期 总评论数通过表list的list_id和评论表ping的list_id相等建立LEFT OUTER JOIN 建立一个视图 语句如下SELECT dbo.list.list_id, dbo.list.list_title, dbo.list.list_date, dbo.list.user_name, dbo.list.user_id, MAX(dbo.ping.user_name) AS ping_user_name,
MAX(dbo.ping.user_id) AS ping_user_id, MAX(dbo.ping.ping_date) AS ping_date_last,
COUNT(dbo.ping.ping_id) AS ping_countFROM dbo.list LEFT OUTER JOIN
dbo.ping ON dbo.list.list_id = dbo.ping.list_idGROUP BY dbo.list.list_id, dbo.list.list_title, dbo.list.list_date, dbo.list.user_name, dbo.list.user_idORDER BY dbo.list.list_id DESC,
MAX(dbo.ping.ping_date) DESC
为什么 "最后评论人ID" "最后评论人用户名" 显示总是错误 ,它不是显示的是对应最后日期的评论人和评论ID 而是最大的值
请问如何修改使得 ping_user_id ping_user_name 和ping_date 对应 显示为最后评论的人 ID 日期
评论表ping 由 ping_id user_id user_name list_id ping_date
现建立一个视图查询实现如下显示
list_id list_title list_date user_id user_name ping_user_id ping_user_name ping_date ping_count
新闻ID 新闻标题 发布日期 发布人ID 发布人用户名 最后评论人ID 最后评论人用户名 最后评论的日期 总评论数通过表list的list_id和评论表ping的list_id相等建立LEFT OUTER JOIN 建立一个视图 语句如下SELECT dbo.list.list_id, dbo.list.list_title, dbo.list.list_date, dbo.list.user_name, dbo.list.user_id, MAX(dbo.ping.user_name) AS ping_user_name,
MAX(dbo.ping.user_id) AS ping_user_id, MAX(dbo.ping.ping_date) AS ping_date_last,
COUNT(dbo.ping.ping_id) AS ping_countFROM dbo.list LEFT OUTER JOIN
dbo.ping ON dbo.list.list_id = dbo.ping.list_idGROUP BY dbo.list.list_id, dbo.list.list_title, dbo.list.list_date, dbo.list.user_name, dbo.list.user_idORDER BY dbo.list.list_id DESC,
MAX(dbo.ping.ping_date) DESC
为什么 "最后评论人ID" "最后评论人用户名" 显示总是错误 ,它不是显示的是对应最后日期的评论人和评论ID 而是最大的值
请问如何修改使得 ping_user_id ping_user_name 和ping_date 对应 显示为最后评论的人 ID 日期
MAX(dbo.ping.user_name) AS ping_user_name,
MAX(dbo.ping.user_id) AS ping_user_id,
改成
(select top 1dbo.ping.user_name from ping dbo.ping where list.list_id = ping.list_id ordrby dbo.ping.ping_date desc) AS ping_user_name,
(select top 1dbo.ping.user_id from ping dbo.ping where list.list_id = ping.list_id ordrby dbo.ping.ping_date desc) AS ping_user_id, 但这么着效率会比较差
也可以考虑group by之后再 join一次来找到最后一条
MAX(dbo.ping.user_id) AS ping_user_id, 不行
那个select top 1dbo.ping.效率又不高,那到底怎么写啊?
最后在用上面的信息链接主帖子表
SELECT dbo.list.list_id, dbo.list.list_title, dbo.list.list_date, dbo.list.user_name, dbo.list.user_id, b.user_name AS ping_user_name,
b.user_id AS ping_user_id,
b.ping_date AS ping_date_last,
b.ping_count AS ping_count FROM dbo.list a LEFT OUTER JOIN
(
select a.list_id,b.user_name,b.user_id,b.ping_date,b.ping_count
from
(
select
dbo.ping.list_id,
MAX(dbo.ping.ping_date),
count(*) as ping_count
from dbo.ping
group by dbo.ping.list_id
) as a left join dbo.ping b
on a.list_id=b.list_id and a.ping_date=b.ping_date
) as b
ON a.list_id = b.list_id
在 access “查询”中 用last 函数就能搞定, last(dbo.ping.user_name) AS ping_user_name,
last(dbo.ping.user_id) AS ping_user_id, 由于sql 没有此函数 就要用 个select 吗?这样的效率也太低了在一个列表页 即显示 出新闻的信息 又 统计出对应 它 的评论数 后最后评论的人 最后评论时间 用 “视图查询”就怎么....
你感觉慢,估计是索引的设置也需要相应调整吧
, LIST.[USER_ID], LIST.[USER_NAME]
, TEMP.PING_DATE LAST_PING_DATE
, TEMP.[USER_ID] LAST_PING_USER_ID
, TEMP.[USER_NAME] LAST_PING_USER_NAME
FROM LIST
LEFT JOIN (
SELECT ROW_NUMBER() OVER (PARTITION BY LIST_ID ORDER BY PING_DATE DESC) ROW_NO
, PING.LIST_ID
, PING.PING_DATE
, PING.[USER_ID]
, PING.[USER_NAME]
FROM PING
) TEMP
ON TEMP.LIST_ID = LIST.LIST_ID
WHERE ISNULL(TEMP.ROW_NO,1) = 1
drop table List
select 1 as list_id,'abc' as list_title,getdate() as list_date,1 as user_id,'tom' as user_name
into list
union all
select 2,'',cast('20090101' as datetime) ,2,'mac'
union all
select 3,'',cast('20090102' as datetime) ,3,'ccc'
union all
select 4,'',cast('20090103' as datetime) ,4,'bbb'
union all
select 5,'',cast('20090104' as datetime) ,5,'aaa'
union all
select 6,'',cast('20090105' as datetime) ,2,'mac'drop table ping
select 1 as ping_id,1 as user_id,'tom' as user_name,1 as list_id,cast('20090101' as datetime) as ping_date
into ping
union all
select 1,2,'mac',1,cast('20090102' as datetime)
union all
select 1,4,'bbb',1,cast('20090103' as datetime)
union all
select 1,3,'ccc',1,cast('20090104' as datetime)
union all
select 1,2,'mac',1,cast('20090105' as datetime)
SELECT a.list_id, a.list_title, a.list_date, a.user_name, a.user_id, b.user_name AS ping_user_name,
b.user_id AS ping_user_id,
b.ping_date AS ping_date_last,
b.ping_count AS ping_count FROM list as a LEFT OUTER JOIN
(
select a.list_id,b.user_name,b.user_id,b.ping_date,a.ping_count
from
(
select
dbo.ping.list_id,
MAX(dbo.ping.ping_date) as ping_date,
count(*) as ping_count
from dbo.ping
group by dbo.ping.list_id
) as a left join dbo.ping as b
on a.list_id=b.list_id and a.ping_date=b.ping_date
) as b
ON a.list_id = b.list_id