เท่าที่ลองหาๆดูเหมือนว่าต้องใช้ union หรือเปล่า แต่เห็นบางคอมเม้นบอกว่า ใช้ union ได้ต้องมี column เหมือนกัน (ไม่รู้เกี่ยวไหม) พึ่งเคยลอง union ก็ยัง งงๆ code
มีอะไรสงสัยสอบถามเพิ่มเติมได้นะครับ ขอบคุณล่วงหน้าครับ :))
ที่ลองทำ ค่าคอลั่ม IN_AMO และ C_AMO เพี้ยน คิดว่าน่าจะผิดที่ where หรือเปล่า ไม่แน่ใจ ไม่ได้ใช้ union (SQL)
SELECT items.CODE
, items.NAME
, sum(invoya.AMOUNT) as IN_AMO
, invoya.NET_COST as invoya_NET
, items.R_SIZE
, format(invoya.NET_COST/items.R_SIZE,2) as invoya_NET_R_SIZE
, format(sum(invoya.AMOUNT*invoya.NET_COST),2) as invoya_total
, sum(credsell.C_AMO) as C_AMO
, format(credsell.NET,2) as credsell_NET
FROM invoya
LEFT JOIN items on (invoya.CODE=items.CODE)
LEFT JOIN credsell on (items.code=credsell.code)
where date(invoya.IV_DATE) BETWEEN '2015-01-01' AND '2015-06-30'
AND date(credsell.CRD_DATE) BETWEEN '2015-01-01' AND '2015-06-30'
AND invoya.CODE=credsell.code
group by items.CODE
order by sum(invoya.AMOUNT*invoya.NET_COST) desc
LIMIT 3000;
code นี้ใช้ union ไม่ทราบว่าผิดตรงไหน ขึ้น Error 1222 (อย่าด่าผมนะ ฮ่าๆ พึ่งเคยลอง) ใช้ union (SQL)
SELECT items.CODE
, items.NAME
, sum(invoya.AMOUNT)
, invoya.NET_COST
, items.R_SIZE
, format(invoya.NET_COST/items.R_SIZE,2)
, format(sum(invoya.AMOUNT*invoya.NET_COST),2)
, sum(credsell.C_AMO)
, format(credsell.NET,2)
FROM
(
SELECT items.CODE
, items.NAME
, sum(invoya.AMOUNT) as IN_AMO
, invoya.NET_COST as invoya_NET
, items.R_SIZE
, format(invoya.NET_COST/items.R_SIZE,2) as invoya_NET_R_SIZE
, format(sum(invoya.AMOUNT*invoya.NET_COST),2) as invoya_total
FROM invoya
LEFT JOIN items on (invoya.CODE=items.CODE)
UNION
SELECT items.CODE
, items.NAME
, sum(credsell.C_AMO) as C_AMO
, format(credsell.NET,2) as credsell_NET
, format(sum(credsell.C_AMO*credsell.NET),2) as credsell_total
FROM credsell
LEFT JOIN items on (items.code=credsell.code)
)
as a
where date(invoya.IV_DATE) BETWEEN '2015-01-01' AND '2015-06-30'
AND date(credsell.CRD_DATE) BETWEEN '2015-01-01' AND '2015-06-30'
AND invoya.CODE=credsell.code
group by items.CODE
order by sum(invoya.AMOUNT*invoya.NET_COST) desc
LIMIT 3000;
invoya (SQL)
SELECT items.CODE
, items.NAME
, sum(invoya.AMOUNT) as IN_AMO
, invoya.NET_COST as invoya_NET
, items.R_SIZE
, format(invoya.NET_COST/items.R_SIZE,2) as invoya_NET_R_SIZE
, format(sum(invoya.AMOUNT*invoya.NET_COST),2) as invoya_total
FROM invoya
LEFT JOIN items on (invoya.CODE=items.CODE)
where date(invoya.IV_DATE) BETWEEN '2015-01-01' AND '2015-06-30'
group by items.CODE
order by sum(invoya.AMOUNT*invoya.NET_COST) desc
LIMIT 3000;
invoya credsell (SQL)
SELECT items.CODE
, items.NAME
, sum(credsell.C_AMO) as C_AMO
, format(credsell.NET,2) as credsell_NET
, format(sum(credsell.C_AMO*credsell.NET),2) as credsell_total
FROM credsell
LEFT JOIN items on (items.code=credsell.code)
where date(credsell.CRD_DATE) BETWEEN '2015-01-01' AND '2015-06-30'
group by items.CODE
select
items.CODE,
items.NAME,
IN_AMO,
invoya_NET,
items.R_SIZE,
invoya_NET_R_SIZE,
invoya_total
C_AMO
credsell_NET
from (
select
invoya.CODE,
sum(invoya.AMOUNT) as IN_AMO,
invoya.NET_COST as invoya_NET,
format(invoya.NET_COST/items.R_SIZE,2) as invoya_NET_R_SIZE,
format(sum(invoya.AMOUNT*invoya.NET_COST),2) as invoya_total
from invoya where date(invoya.IV_DATE) BETWEEN '2015-01-01' AND '2015-06-30' group by invoya.CODE
) as sum_invoya
left join items on items.CODE=sum_invoya.CODE
left join (
select
credsell.CODE,
sum(credsell.C_AMO) as C_AMO,
format(credsell.NET,2) as credsell_NET
from credsell where date(credsell.CRD_DATE) BETWEEN '2015-01-01' AND '2015-06-30' group by credsell.CODE
) as sum_credsell on sum_credsell.CODE=sum_invoya.code
order by invoya_total desc
LIMIT 3000
Load balance : Server 05
Quote:
