时间戳

首页 > 解决方案 > SQL 查询返回结果的乘积而不是总和

sql - SQL 查询返回结果的乘积而不是总和

问题描述

如何确保通过此加入我只会收到结果的总和而不是产品？

我有一个项目实体，其中包含两个一对多关系。如果我查询处置和供应。

使用以下查询：

SELECT *
FROM projects
         JOIN disposals disposal on projects.project_id = disposal.disposal_project_refer
WHERE (projects.project_name = 'Höngg')

我得到以下结果：

project_id,project_name,disposal_id,depository_refer,material_refer,disposal_date,disposal_measurement,disposal_project_refer
1,Test,1,1,1,2020-08-12 15:24:49.913248,123,1
1,Test,2,1,2,2020-08-12 15:24:49.913248,123,1
1,Test,7,2,1,2020-08-12 15:24:49.913248,123,1
1,Test,10,3,4,2020-08-12 15:24:49.913248,123,1

相同的耗材查询返回相同数量的结果。

type Project struct {
    ProjectID   uint       `gorm:"primary_key" json:"ProjectID"`
    ProjectName string     `json:"ProjectName"`
    Disposals   []Disposal `gorm:"ForeignKey:disposal_project_refer"`
    Supplies    []Supply   `gorm:"ForeignKey:supply_project_refer"`
}

如果我查询两个表，我希望收到两个单个查询的总和。目前我收到 16 个结果（4 个供应结果乘以 4 个处置结果）。

组合查询：

SELECT *
FROM projects
         JOIN disposals disposal ON projects.project_id = disposal.disposal_project_refer
         JOIN supplies supply ON projects.project_id = supply.supply_project_refer
WHERE (projects.project_name = 'Höngg');

我尝试通过联合查询来实现我的目标，但我没有成功。我还应该尝试什么来实现我的目标？

标签： sqlpostgresql

解决方案

这是你的情况（简化）：

# with a(x,y) as (values(1,1)), b(x,z) as (values(1,11),(1,22)), c(x,t) as (values(1,111),(1,222))
select * from a join b on (a.x=b.x) join c on (b.x=c.x);
┌───┬───┬───┬────┬───┬─────┐
│ x │ y │ x │ z  │ x │  t  │
├───┼───┼───┼────┼───┼─────┤
│ 1 │ 1 │ 1 │ 11 │ 1 │ 111 │
│ 1 │ 1 │ 1 │ 11 │ 1 │ 222 │
│ 1 │ 1 │ 1 │ 22 │ 1 │ 111 │
│ 1 │ 1 │ 1 │ 22 │ 1 │ 222 │
└───┴───┴───┴────┴───┴─────┘

它产生笛卡尔连接，因为连接的值在所有表中都相同。您需要一些附加条件来加入数据。例如（各种情况的测试）：

# with a(x,y) as (values(1,1)), b(x,z) as (values(1,11),(1,22)), c(x,t) as (values(1,111),(1,222))
select *
from a
    cross join lateral (
        select *
        from (select row_number() over() as rn, * from b where b.x=a.x) as b
                full join (select row_number() over() as rn, * from c where c.x=a.x) as c on (b.rn=c.rn)
        ) as bc;

┌───┬───┬────┬───┬────┬────┬───┬─────┐
│ x │ y │ rn │ x │ z  │ rn │ x │  t  │
├───┼───┼────┼───┼────┼────┼───┼─────┤
│ 1 │ 1 │  1 │ 1 │ 11 │  1 │ 1 │ 111 │
│ 1 │ 1 │  2 │ 1 │ 22 │  2 │ 1 │ 222 │
└───┴───┴────┴───┴────┴────┴───┴─────┘

# with a(x,y) as (values(1,1)), b(x,z) as (values(1,11),(1,22),(1,33)), c(x,t) as (values(1,111),(1,222))
select *
from a
    cross join lateral (
        select *
        from (select row_number() over() as rn, * from b where b.x=a.x) as b
                full join (select row_number() over() as rn, * from c where c.x=a.x) as c on (b.rn=c.rn)
        ) as bc;

┌───┬───┬────┬───┬─────┬──────┬──────┬──────┐
│ x │ y │ rn │ x │  z  │  rn  │  x   │  t   │
├───┼───┼────┼───┼─────┼──────┼──────┼──────┤
│ 1 │ 1 │  1 │ 1 │  11 │    1 │    1 │  111 │
│ 1 │ 1 │  2 │ 1 │  22 │    2 │    1 │  222 │
│ 1 │ 1 │  3 │ 1 │  33 │ ░░░░ │ ░░░░ │ ░░░░ │
└───┴───┴────┴───┴─────┴──────┴──────┴──────┘

# with a(x,y) as (values(1,1)), b(x,z) as (values(1,11),(1,22)), c(x,t) as (values(1,111),(1,222),(1,333))
select *
from a
    cross join lateral (
        select *
        from (select row_number() over() as rn, * from b where b.x=a.x) as b
                full join (select row_number() over() as rn, * from c where c.x=a.x) as c on (b.rn=c.rn)
        ) as bc;

┌───┬───┬──────┬──────┬──────┬────┬───┬─────┐
│ x │ y │  rn  │  x   │  z   │ rn │ x │  t  │
├───┼───┼──────┼──────┼──────┼────┼───┼─────┤
│ 1 │ 1 │    1 │    1 │   11 │  1 │ 1 │ 111 │
│ 1 │ 1 │    2 │    1 │   22 │  2 │ 1 │ 222 │
│ 1 │ 1 │ ░░░░ │ ░░░░ │ ░░░░ │  3 │ 1 │ 333 │
└───┴───┴──────┴──────┴──────┴────┴───┴─────┘

db<>fiddle

disposals请注意，和supplies（在我的示例中）之间没有任何明显的关系，b因此c两者的顺序可能是随机的。对我而言，此任务的更好解决方案可能是使用 JSON 聚合这些表中的数据，例如：

with a(x,y) as (values(1,1)), b(x,z) as (values(1,11),(1,22),(1,33)), c(x,t) as (values(1,111),(1,222))
select
    *,
    (select json_agg(to_json(b.*)) from b where a.x=b.x) as b,
    (select json_agg(to_json(c.*)) from c where a.x=c.x) as c
from a;

┌───┬───┬──────────────────────────────────────────────────┬────────────────────────────────────┐
│ x │ y │                        b                         │                 c                  │
├───┼───┼──────────────────────────────────────────────────┼────────────────────────────────────┤
│ 1 │ 1 │ [{"x":1,"z":11}, {"x":1,"z":22}, {"x":1,"z":33}] │ [{"x":1,"t":111}, {"x":1,"t":222}] │
└───┴───┴──────────────────────────────────────────────────┴────────────────────────────────────┘

推荐阅读