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

联接到视图表转换

mysqli laravel-5 sql mysql php

proghasan · 技术社区 · 6 年前

我想把左连接改成 view table .

Following is my SQL

 SELECT d.account_no, COALESCE(d.deposits, 0) - COALESCE(w.withdrawals, 0) 
   AS balance
  FROM (SELECT account_no, SUM(deposit_amount) AS deposits
   FROM deposits
  GROUP BY account_no) d
  LEFT JOIN (SELECT account_no, SUM(withdraw_amount) AS withdrawals
       FROM withdraws
       GROUP BY account_no) w ON w.account_no = d.account_no

我想创造 view 表。

我该怎么做呢?有人能帮忙吗?

1 回复 | 直到 6 年前

ScaisEdge 6 年前

创建视图(一次)并在左连接中使用

CREATE  VIEW my_view AS 
SELECT account_no, SUM(withdraw_amount) AS withdrawals
       FROM withdraws
       GROUP BY account_no
;


SELECT d.account_no, COALESCE(d.deposits, 0) - COALESCE(w.withdrawals, 0) 
 AS balance
FROM (SELECT account_no, SUM(deposit_amount) AS deposits
 FROM deposits
GROUP BY account_no) d
LEFT JOIN my_view  w ON w.account_no = d.account_no

推荐文章

Onyx · 尝试使用Laravel迁移添加外键约束时出错

6 年前

Prateek Saini · Laravel网站“上传到共享主机”显示的是目录结构,而不是我指定的网络路径

6 年前

davidvera · 在部分页面而不是视图Laravel 5.5上显示SQL请求

6 年前

Tyteck · Laravel-用表2列更新表1列

6 年前

SaidbakR · Laravel针对多个同名字段的自定义验证消息

6 年前

Niladri Banerjee - Uttarpara · Laravel 5.5创建菜单的递归函数

6 年前

AVA · 如何将cordova项目连接到数据库?

6 年前

apin pipin · 在输入表Laravel 5.5中添加值

6 年前

user9796679 · Laravel-参数1传递给Illuminate\Auth\EloquentUserProvider::validateCredentials()

6 年前

Jesse Orange · 《拉雷维尔》中NPM、作曲家和鲍尔之间的区别?

6 年前