total表
id name vendor stock area
1 test1 vendor1 5 1
2 test2 vendor2 2 2
3 test3 vendor3 3 3
4 test4 vendor4 1 4
5 test5 vendor5 4 5
warehouse表
total.id name vendor num outnum purchase_at ship_at
1 test1 vendor1 2 2020-09-02
1 test1 vendor1 3 2020-09-02
3 test3 vendor3 1 2020-09-02
3 test3 vendor3 2 2020-08-05
3 test3 vendor3 1 2020-09-02
3 test3 vendor3 3 2020-08-05
3 test3 vendor3 4 2020-09-04
需求9月
area id name vendor stock num outnum
1 1 test1 vendor1 5 5
2 2 test2 vendor2 2
3 3 test3 vendor3 3 2 4
4 4 test4 vendor4 1
想尋問這合併查詢的資料表的寫法
已邀請的邦友 {{ invite_list.length }}/5
CREATE TABLE total
(
id int,
name nvarchar(10),
vendor nvarchar(10),
stock nvarchar(10),
area nvarchar(10)
);
insert into total
values
(1,'test1','vendor1','5','1'),
(2,'test2','vendor2','2','2'),
(3,'test3','vendor3','3','3'),
(4,'test4','vendor4','1','4'),
(5,'test5','vendor5','4','5');
CREATE TABLE warehouse
(
total_id int,
name nvarchar(10),
vendor nvarchar(10),
num int,
outnum int,
purchase_at date,
ship_at date
);
insert into warehouse
values
(1,'test1','vendor1',2,0,'2020-09-02',null),
(1,'test1','vendor1',3,0,'2020-09-02',null),
(3,'test3','vendor3',1,0,'2020-09-02',null),
(3,'test3','vendor3',2,0,'2020-08-05',null),
(3,'test3','vendor3',1,0,'2020-09-02',null),
(3,'test3','vendor3',0,3,null,'2020-08-05'),
(3,'test3','vendor3',0,4,null,'2020-09-04');
select A.area,A.id,A.name,A.vendor,A.stock,
B.num,C.outnum
from total as A
left join (
select total_id,sum(num) as num
from warehouse
where purchase_at like '2020-09%'
group by total_id,date_format(purchase_at,120)
) as B on B.total_id=A.id
left join (
select total_id,sum(outnum) as outnum
from warehouse
where ship_at like '2020-09%'
group by total_id,date_format(ship_at,120)
) as C on C.total_id=A.id
where A.area>='1'
and A.area<='4'
order by A.area,A.id
不好意思在請教您一下我上面改為1~4 可以怎麼改呢，上方改了一下
還有warehouse表是total_id對應total表id的
程式已更新,請驗算 area = 3 的 num 是多少才對 .
感謝您，驗算過了打錯了，不好意思可以請教您一下substring(purchase_at,1,7)因目前欄位設定date，還有where 1=1的意思嗎，目前在laravel再把它改成eloquent orm寫法
程式已更新
total表的資料太少,area 可能會有重複的值,所以猜不出來是要如何計算才對
不好意思可以請問一下，我的id和total_id有對應的
warehouse 的 total_id 這個欄位已經更新了,
他跟 totol 的 id 對應關係就是下面的兩行
as B on B.total_id=A.id
as C on C.total_id=A.id
不好意思想再詢問一下，目前卡在轉eloquent orm
請參閱 :
SQL三部曲:你不需要ORM
Laravel框架執行原生SQL語句及使用paginate分頁的方法
(參考 : 1、執行原生sql)
錯誤回應#1054 - Unknown column 'B.num' in 'field list'
我帶上面的帶進去mysql會錯
程式碼已經改成 MySql 的語法了,
請確認你的兩個 Table 是 total 及 warehouse
非常感謝您，我試過資料庫查詢是對的，但我代入了你提供的文件改為原型sql，輸出前端資料卻代不出來，不好意思在請教您一下
$sqlTmp=sprintf('select A.area,A.id,A.name,A.vendor,A.stock,
B.d,C.e
from total as A
left join (
select total_id,sum(num) as d
from warehouse2
where month(purchase_at) = 8
group by total_id
) as B on B.total_id=A.id
left join (
select total_id,sum(outnum) as e
from warehouse2
where month(ship_at) = 8
group by total_id
) as C on C.total_id=A.id
where A.area>=19
and A.area<=55
order by A.area,A.id');
$c=totalModel::select($sqlTmp);
return view('Report/month2',compact('c'));
google 一下 : Laravel return view compact
看看 view 及 compact 的用法吧
謝謝您，我去查一下，成功了，之後有問題再請多多指較，學到很多東西
iThome鐵人賽
看影片追技術