iT邦幫忙

0

Laravel join編寫問題 SQL ORM

YJ 2020-10-02 10:39:391277 瀏覽
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             

想尋問這合併查詢的資料表的寫法

rogeryao iT邦大師 3 級 ‧ 2020-10-02 12:27:33 檢舉
需求9月的 num 與 outnum 是如何計算 ?
area = 1.3 其 num = 1 還是 num =2 ???
YJ iT邦新手 5 級 ‧ 2020-10-02 13:38:51 檢舉
num和outnum是加總
area那我改了一下area在1~4
感謝!!

1 個回答

0
rogeryao
iT邦大師 3 級 ‧ 2020-10-02 14:26:44
最佳解答
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

Demo

看更多先前的回應...收起先前的回應...
YJ iT邦新手 5 級 ‧ 2020-10-02 15:08:40 檢舉

不好意思在請教您一下我上面改為1~4 可以怎麼改呢,上方改了一下

還有warehouse表是total_id對應total表id的/images/emoticon/emoticon25.gif

rogeryao iT邦大師 3 級 ‧ 2020-10-02 15:46:35 檢舉

程式已更新,請驗算 area = 3 的 num 是多少才對 .

YJ iT邦新手 5 級 ‧ 2020-10-02 15:56:32 檢舉

感謝您,驗算過了打錯了,不好意思可以請教您一下substring(purchase_at,1,7)因目前欄位設定date,還有where 1=1的意思嗎,目前在laravel再把它改成eloquent orm寫法

rogeryao iT邦大師 3 級 ‧ 2020-10-02 16:13:50 檢舉

程式已更新
total表的資料太少,area 可能會有重複的值,所以猜不出來是要如何計算才對

YJ iT邦新手 5 級 ‧ 2020-10-02 16:36:21 檢舉

不好意思可以請問一下,我的id和total_id有對應的/images/emoticon/emoticon02.gif

rogeryao iT邦大師 3 級 ‧ 2020-10-02 18:26:25 檢舉

warehouse 的 total_id 這個欄位已經更新了,
他跟 totol 的 id 對應關係就是下面的兩行
as B on B.total_id=A.id
as C on C.total_id=A.id

YJ iT邦新手 5 級 ‧ 2020-10-02 21:27:47 檢舉

不好意思想再詢問一下,目前卡在轉eloquent orm

rogeryao iT邦大師 3 級 ‧ 2020-10-02 23:57:02 檢舉
YJ iT邦新手 5 級 ‧ 2020-10-04 14:07:58 檢舉

錯誤回應#1054 - Unknown column 'B.num' in 'field list'
我帶上面的帶進去mysql會錯/images/emoticon/emoticon02.gif

rogeryao iT邦大師 3 級 ‧ 2020-10-04 14:38:46 檢舉

程式碼已經改成 MySql 的語法了,
請確認你的兩個 Table 是 total 及 warehouse

YJ iT邦新手 5 級 ‧ 2020-10-04 15:18:47 檢舉

非常感謝您,我試過資料庫查詢是對的,但我代入了你提供的文件改為原型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'));
rogeryao iT邦大師 3 級 ‧ 2020-10-04 18:38:10 檢舉

google 一下 : Laravel return view compact
看看 view 及 compact 的用法吧

YJ iT邦新手 5 級 ‧ 2020-10-04 19:23:36 檢舉

/images/emoticon/emoticon12.gif謝謝您,我去查一下,成功了,之後有問題再請多多指較,學到很多東西

我要發表回答

立即登入回答