ms sqlserver sql example

SQL查询技巧详解

最新推荐文章于 2025-07-10 09:11:18 发布

原创最新推荐文章于 2025-07-10 09:11:18 发布 · 125 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL #C #C++ #C# #XML

MS sqlserver 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一种复杂的SQL查询方法，该方法通过多个表的连接操作实现对演示评论及相关信息的综合检索。具体包括从不同表中选取所需字段，并通过LEFT JOIN进行关联，最终实现了根据用户ID筛选特定评论的功能。

select p.long_title as title,
ps.[name] as STATUS,
i.[name] as industry,
c.[name] as category,
pc.comment as 'comment',
users.first_name + ' ' + users.last_name as commentuser,
pc.last_updated_date as commentdate ,
topicTags.tags
from presentation_comment pc
left join
presentation p
on pc.presentation_id=p.presentation_id
left join
presentation_status ps
on ps.presentation_status_id =p.presentation_status_id
left join industry i on i.industry_id = p.industry_id
left join category c on c.category_id = p.category_id
left join
users on users.user_id=pc.last_updated_user_id
left join
(
SELECT presentation_id,
tags =
(
SELECT distinct ',' + topic.[name] + ','
FROM presentation_topic tmp,
topic
WHERE topic.topic_id = tmp.topic_id AND
presentation_id = presentation_topic.presentation_id FOR
XML PATH ('')
)
FROM presentation_topic
WHERE presentation_id IS NOT NULL
GROUP BY presentation_id
) topicTags
on topicTags.presentation_id = p.presentation_id
left join
(
SELECT presentation_id,
speaker =
(
SELECT ',' + ps. + ','
FROM presentation tmp,
presentation_speaker ps
WHERE topic.topic_id = tmp.topic_id AND
presentation_id = presentation_topic.presentation_id FOR
XML PATH ('')
)
FROM presentation_topic
WHERE presentation_id IS NOT NULL
GROUP BY presentation_id
) topicSpeakers
on topicSpeakers.presentation_id=p.presentation_id
where users.user_id=1