当文本字段是 MS SQL Server 2000 中返回字段的一部分时，如何选择不同的行？

发布于 2024-07-16 10:17:26 字数 822 浏览 7 评论 0原文

我有一个数据库驱动的常见问题解答，它被组织成几个部分，我试图仅获取那些有与之相关的问题/答案的部分的部分数据。

这是架构：

|---------------------|      |----------------------|
|       Section       |      |       Quest-Ans      |
|---------------------|      |----------------------|
| PK | id(int)        |<--|  | PK     | id(int)     |
|    | title(varchar) |   |--| FK     | Sec_id(int) |
|    | desc(text)     |      |        | body(text)  |
|---------------------|      |----------------------|

当我尝试此查询时：

SELECT DISTINCT s.id, s.title, s.desc
FROM Section as s INNER JOIN Quest-Ans as q ON s.id = q.Sec_id

我收到一条错误，指出 DISCRETE 无法应用于文本字段。我怎样才能得到我想要的数据？

如果重要的话，这是一个 SQL2000 数据库。

编辑：

好的，看来有两种方法可以解决这个问题。可以使用 EXISTS 和 where 子句中的子查询，或者使用内连接中的子查询。哪个更快？

原文

I have a database-driven FAQ that is organised into sections and I am trying to get the section data for only those sections who have a question/answer associated with them.

Here is the schema:

|---------------------|      |----------------------|
|       Section       |      |       Quest-Ans      |
|---------------------|      |----------------------|
| PK | id(int)        |<--|  | PK     | id(int)     |
|    | title(varchar) |   |--| FK     | Sec_id(int) |
|    | desc(text)     |      |        | body(text)  |
|---------------------|      |----------------------|

When I try this query:

SELECT DISTINCT s.id, s.title, s.desc
FROM Section as s INNER JOIN Quest-Ans as q ON s.id = q.Sec_id

I get an error saying that DISCRETE cannot be applied to a text field. How can I get the data I want?

If it matters, this is an SQL2000 database.

EDIT:

Ok, so it seems like there are two ways to go about this. Either with EXISTS and a subquery in the where clause, or with the subquery in the inner join. Which is faster?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

等数载，海棠开 2024-07-23 10:17:26

这应该可以做到：

SELECT s.id, s.title, s.desc
FROM Section as s 
WHERE EXISTS (SELECT * FROM Quest-Ans as q where q.Sec_id = s.id)

This should do it:

SELECT s.id, s.title, s.desc
FROM Section as s 
WHERE EXISTS (SELECT * FROM Quest-Ans as q where q.Sec_id = s.id)

回复收藏 0 原文

一笔一画续写前缘 2024-07-23 10:17:26

select s.id, s.title, s.desc
from Section s 
inner join (select distinct sec_id from Quest-Ans) dqa on s.id = dqa.sec_id

select s.id, s.title, s.desc
from Section s 
inner join (select distinct sec_id from Quest-Ans) dqa on s.id = dqa.sec_id

回复收藏 0 原文

猛虎独行 2024-07-23 10:17:26

尝试一下：

SELECT s.Title, s.Desc
FROM Section as s
INNER JOIN (
  SELECT DISTINCT s.id
  FROM Section as s 
  INNER JOIN Quest-Ans as q ON s.id = q.Sec_id
) q ON s.Id = q.Id

Try it:

SELECT s.Title, s.Desc
FROM Section as s
INNER JOIN (
  SELECT DISTINCT s.id
  FROM Section as s 
  INNER JOIN Quest-Ans as q ON s.id = q.Sec_id
) q ON s.Id = q.Id

回复收藏 0 原文