sql server中多表的select查询

Question

嘿伙计们， 我的 sql server 数据库中有 3 个表。 1. crossarticle_article 有字段：标题 2. blogs_entries 有字段：标题 3. 论坛帖子。有字段：主题

现在我想做的是，我在我的网站上进行搜索，当用户在搜索框中输入任何关键字并点击按钮时，它应该在所有这些表中搜索标题并返回单个结果集。

我怎样才能做到这一点，我正在使用 sql server 2008

Answer 1

像这样的东西：

SELECT "article"                  AS type
     , crossarticle_article.id    AS id
     , crossarticle_article.Title AS title_subject
FROM crossarticle_article 
WHERE Title LIKE "%userinput%"

UNION ALL

SELECT "blog entry"               AS type
     , blogs_entries.id           AS id     
     , blogs_entries.title        AS title_subject
FROM blogs_entries
WHERE title LIKE "%userinput%"

UNION ALL 

SELECT "forum post"               AS type
     , forums_posts.id            AS id
     , forums_posts.subject       AS title_subject
FROM forums_posts
WHERE subject LIKE "%userinput%"

Answer 2

UNION 将返回唯一的结果，而UNION ALL 将返回所有内容，包括重复。

SELECT Title
FROM crossarticle_article
WHERE Title = '%term%'
UNION
SELECT Title
FROM blogs_entries
WHERE Title = '%term%'
UNION
SELECT subject AS Title
FROM forums_posts
WHERE Title = '%term%'

Answer 3

SELECT Title
FROM crossarticle_article
WHERE Title = @Title

UNION

SELECT title
FROM blogs_entries
WHERE title = @Title

UNION

SELECT subject
FROM forums_posts
WHERE subject = @Title