代码之家 › 专栏 › 技术社区 › Elie

基于多个计算值的MySQL视图

mysql

Elie · 技术社区 · 15 年前

在前一个问题的后续工作中,假设我有3个表,A、B和C。表A有一个ID,用作表B和C上的外键,每个表都有一些值作为属性。对于表A中的每个ID,我想得到表B和表C之间的值差,具体如下:

CREATE VIEW T1 AS
    SELECT B.A_ID AS ID, SUM(B.VALUE) AS VAL
    FROM B
    GROUP BY B.A_ID;
CREATE VIEW T2 AS
    SELECT C.A_ID AS ID, SUM(C.VALUE) AS VAL
    FROM C
    GROUP BY C.A_ID;
SELECT T1.ID, T1.VAL, T2.VAL FROM T1, T2 WHERE T1.ID = T2.ID;

问题是,如果表B有特定ID的一些值,但是表C没有,或者相反。在这种情况下,select语句将不会返回该行。有没有一种方法可以让我创建一个单一的视图,它本质上看起来如下:

CREATE VIEW T3 AS
    SELECT B.A_ID AS ID, SUM(B.VALUE) AS VAL1, SUB(C.VAL) AS VAL2
    FROM B, C
    WHERE B.A_ID = C.A_ID
    GROUP BY B.A_ID;

这类视图的创建脚本示例将不胜感激。

2 回复 | 直到 15 年前

Justin Giboney 15 年前

你可以用这个

CREATE VIEW myView AS
SELECT test_a.id, name, IFNULL( (
  SELECT SUM( value ) 
  FROM test_b
  WHERE test_b.a_id = test_a.id
  GROUP BY test_b.a_id ) , 0
) - IFNULL( (
  SELECT SUM( value ) 
  FROM test_c
  WHERE test_c.a_id = test_a.id
  GROUP BY test_c.a_id ) , 0
)
FROM test_a

但如果C的和大于B,这将导致负数。如果您希望绝对差大于使用它:

CREATE VIEW myView AS
SELECT test_a.id, name, ABS( IFNULL( (
  SELECT SUM( value ) 
  FROM test_b
  WHERE test_b.a_id = test_a.id
  GROUP BY test_b.a_id ) , 0 ) - IFNULL( (
SELECT SUM( value ) 
  FROM test_c
  WHERE test_c.a_id = test_a.id
  GROUP BY test_c.a_id ) , 0
  )
)
FROM test_a

Jonathan 15 年前

SELECT ID, MAX(VAL1), MAX(VAL2) FROM 
((SELECT B.A_ID AS ID, SUM(B.VALUE) AS VAL1, 0 as VAL2
FROM B GROUP BY B.A_ID) as T1
UNION
(SELECT C.A_ID AS ID, 0 VAL1, SUM(C.VALUE) AS VAL2
FROM C GROUP BY C.A_ID) as T2) as Foo
GROUP BY FOO.ID

有点粗糙。另外,不能创建具有联合的视图。

推荐文章

Badal Solanki · 如何在MySQL中对字符串进行算术运算?

1 年前

Saleh Mehdiyev · 我想重构我的Laravel代码以遵守SOLID原则

1 年前

hello_programmers · Mysql从其他表输出一列

1 年前

Community wiki · 这个MySQL语句出了什么问题?

1 年前

Community wiki · 优化从同一表中提取的多列的查询

1 年前