SQL查询一对多关系的两个表 [英] SQL query two tables with relation one-to-many

问题描述

我有两个表A和Band，A与B之间的关系是A --->一对多---> B

通常我的每条A记录都有一条B记录.

我正在尝试编写一个查询，该查询将为我提供A的 ONLY 条记录列表，这些记录在B中具有 ONE(MULTIPLE)条记录. /p>

我很困惑，因为我只做基本的sql查询，而这对我来说似乎很复杂.

有人可以指导我改正答案还是给我解决方案.

好吧，我尝试了以下类似操作，但它给了我一个错误

从A a，B b中选择SOME_COLUMN_NAME，其中a.ID = b.ID并计数(b.SOME_OTHER_COLUMN_NAME)> 1;

ORA-00934:此处不允许使用分组功能

我试图在互联网广告上进行搜索，但我不允许在where子句中使用分组，而应该通过使用hading进行搜索.我现在被困在这里.

解决方案

您尚未指定要使用的数据库系统(sql-server/mysql/sqlite/oracle等)，所以这是一个通用答案.

以这种形式，在SELECT和GROUP BY子句中显式列出A的所有列.通常，在大多数DBMS中，它都会生成一个好的简单计划.但是，如果该类型不可用于GROUP，例如SQL Server中的TEXT列，它也会惨遭失败.

SELECT A.Col1, A.Col2, A.Col3
FROM A
JOIN B ON A.LinkID = B.LinkID
GROUP BY A.Col1, A.Col2, A.Col3
HAVING COUNT(*) > 1

另一种使用子查询的形式适用于A中的任何列类型，并且通常会产生完全相同的计划.

SELECT A.Col1, A.Col2, A.Col3
FROM A
WHERE 1 < (
    SELECT COUNT(*)
    FROM B
    WHERE A.LinkID = B.LinkID)

I have two tables A and Band the relation between A to B is A--->one-to-Many--->B

Normally i have one record of B for every record of A.

I am trying to write a query which would give me list of ONLY records of A which have more than ONE(MULTIPLE) record(s) in B.

I am very confused as I have only done basic sql queries and this one seems complex to me.

Could some one please guide me to correct answer or give me the solution.

edited:

ok i tried something like below and it gave me an error

SELECT SOME_COLUMN_NAME FROM A a, B b WHERE a.ID=b.ID and count(b.SOME_OTHER_COLUMN_NAME)>1;

ORA-00934: group function is not allowed here

I tried to search on the internet ad i am not allowed to use grouping in where clause and should go by using having. I am stuck here now.

解决方案

You haven't specified which database system you are using (sql-server/mysql/sqlite/oracle etc) so this is a generic answer.

In this form, list out all the columns of A explicitly in the SELECT and GROUP BY clauses. It normally generates a good straightforward plan in most DBMS. But it can also fail miserably if the type is not GROUP-able, such as TEXT columns in SQL Server.

SELECT A.Col1, A.Col2, A.Col3
FROM A
JOIN B ON A.LinkID = B.LinkID
GROUP BY A.Col1, A.Col2, A.Col3
HAVING COUNT(*) > 1

This other form using a subquery works for any column types in A and normally produces exactly the same plan.

SELECT A.Col1, A.Col2, A.Col3
FROM A
WHERE 1 < (
    SELECT COUNT(*)
    FROM B
    WHERE A.LinkID = B.LinkID)

这篇关于SQL查询一对多关系的两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！