 |
เอาข้อมูลที่ต้องการมา union กัน แล้วก้มา left join แล้วข้อมูลตารางที่ 2 ออกไม่ครบ ตารางแรกออกครบถ้วน |
| |
 |
|
|
 |
 |
|
เอาข้อมูลที่ต้องการมา union กัน แล้วก้มา left join แล้วข้อมูลตารางที่ 2 ออกไม่ครบ
ตารางแรกออกครบถ้วน ใครทราบรบกวนตอบด้วยนะคะ
ผลที่ออก
กระจกเทมเปอร์ xxxxx1 57633.68 112779.17
กระจกเทมเปอร์ โสดา 21532
กระจกลามิเนท รัชนี 101891 27872
กระจกใส จันทร์ 52766
ถ้าใช้ full join ข้อมูลที่หายไปของตารางที่สองจะออกแบบนี้
กระจกเทมเปอร์ xxxxx1 57633.68 112779.17
กระจกเทมเปอร์ โสดา 21532
กระจกลามิเนท รัชนี 101891 27872
กระจกใส จันทร์ 52766
15900
37890
Code
select t1.name1, t1.salesman, t1.total_price, t2.total_price1 from (
(select res_users.id, 'กระจกเทมเปอร์' as name1,
res_users.name as salesman,
sum(ROUND((coalesce(sale_order_line.price_unit,0)* coalesce(product_uom_qty,0)),2)) as total_price
from sale_order
left join res_partner on res_partner.id = sale_order.partner_id
left join sale_order_line on sale_order_line.order_id = sale_order.id
left join product_product on sale_order_line.product_id = product_product.id
left join res_users on res_users.id = sale_order.user_id
where sale_order.requested_date between '2012-05-01' and '2012-05-07'
and sale_order.state = 'done'
and product_product.default_code like 'st%'
and not (product_product.default_code like 'stc%' or default_code like 'stb%')
and not res_partner.id = '874'
group by res_users.id,salesman)
union
(select res_users.id, 'กระจกลามิเนท' as name1,
res_users.name as salesman,
sum(ROUND((coalesce(sale_order_line.price_unit,0)* coalesce(product_uom_qty,0)),2)) as total_price
from sale_order
left join res_partner on res_partner.id = sale_order.partner_id
left join sale_order_line on sale_order_line.order_id = sale_order.id
left join product_product on sale_order_line.product_id = product_product.id
left join res_users on res_users.id = sale_order.user_id
where sale_order.requested_date between '2012-05-01' and '2012-05-07'
and sale_order.state = 'done'
and product_product.default_code like 'sl%'
group by res_users.id,salesman)
union
(select res_users.id, 'กระจกธรรมดา' as name1,
res_users.name as salesman,
sum(ROUND((coalesce(sale_order_line.price_unit,0)* coalesce(product_uom_qty,0)),2)) as total_price
from sale_order
left join res_partner on res_partner.id = sale_order.partner_id
left join sale_order_line on sale_order_line.order_id = sale_order.id
left join product_product on sale_order_line.product_id = product_product.id
left join res_users on res_users.id = sale_order.user_id
where sale_order.requested_date between '2012-05-01' and '2012-05-07'
and sale_order.state = 'done'
and product_product.default_code like 'sa%'
and not (product_product.default_code like 'sac%' or default_code like 'sab%')
group by res_users.id,salesman)
union
(select res_users.id, 'กระจกพ่นสี-พ่นทราย' as name1,
res_users.name as salesman,
sum(ROUND((coalesce(sale_order_line.price_unit,0)* coalesce(product_uom_qty,0)),2)) as total_price
from sale_order
left join res_partner on res_partner.id = sale_order.partner_id
left join sale_order_line on sale_order_line.order_id = sale_order.id
left join product_product on sale_order_line.product_id = product_product.id
left join res_users on res_users.id = sale_order.user_id
where sale_order.requested_date between '2012-05-01' and '2012-05-07'
and sale_order.state = 'done'
and (product_product.default_code like 'sac%' or default_code like 'stc%' or default_code like 'sab%' or default_code like 'stb%')
group by res_users.id,salesman)) t1
left join
((select res_users.id, 'กระจกเทมเปอร์' as name1,
res_users.name as salesman1,
sum(ROUND((coalesce(sale_order_line.price_unit,0)* coalesce(product_uom_qty,0)),2)) as total_price1
from sale_order
left join res_partner on res_partner.id = sale_order.partner_id
left join sale_order_line on sale_order_line.order_id = sale_order.id
left join product_product on sale_order_line.product_id = product_product.id
left join res_users on res_users.id = sale_order.user_id
where sale_order.requested_date = '2012-05-07'
and sale_order.state = 'done'
and product_product.default_code like 'st%'
and not (product_product.default_code like 'stc%' or default_code like 'stb%')
and not res_partner.id = '874'
group by res_users.id,salesman1)
union
(select res_users.id, 'กระจกลามิเนท' as name1,
res_users.name as salesman1,
sum(ROUND((coalesce(sale_order_line.price_unit,0)* coalesce(product_uom_qty,0)),2)) as total_price1
from sale_order
left join res_partner on res_partner.id = sale_order.partner_id
left join sale_order_line on sale_order_line.order_id = sale_order.id
left join product_product on sale_order_line.product_id = product_product.id
left join res_users on res_users.id = sale_order.user_id
where sale_order.requested_date = '2012-05-07'
and sale_order.state = 'done'
and product_product.default_code like 'sl%'
group by res_users.id,salesman1)
union
(select res_users.id, 'กระจกธรรมดา' as name1,
res_users.name as salesman1,
sum(ROUND((coalesce(sale_order_line.price_unit,0)* coalesce(product_uom_qty,0)),2)) as total_price1
from sale_order
left join res_partner on res_partner.id = sale_order.partner_id
left join sale_order_line on sale_order_line.order_id = sale_order.id
left join product_product on sale_order_line.product_id = product_product.id
left join res_users on res_users.id = sale_order.user_id
where sale_order.requested_date = '2012-05-07'
and sale_order.state = 'done'
and product_product.default_code like 'sa%'
and not (product_product.default_code like 'sac%' or default_code like 'sab%')
group by res_users.id,salesman1)
union
(select res_users.id, 'กระจกพ่นสี-พ่นทราย' as name1,
res_users.name as salesman1,
sum(ROUND((coalesce(sale_order_line.price_unit,0)* coalesce(product_uom_qty,0)),2)) as total_price1
from sale_order
left join res_partner on res_partner.id = sale_order.partner_id
left join sale_order_line on sale_order_line.order_id = sale_order.id
left join product_product on sale_order_line.product_id = product_product.id
left join res_users on res_users.id = sale_order.user_id
where sale_order.requested_date = '2012-05-07'
and sale_order.state = 'done'
and (product_product.default_code like 'sac%' or default_code like 'stc%' or default_code like 'sab%' or default_code like 'stb%')
group by res_users.id,salesman1)) t2 on t1 = t2
order by t1.name1, t1.salesman, t2.salesman1, t1.total_price, t2.total_price1
Tag : PostgreSQL
|
|
|
 |
 |
 |
 |
| Date :
2012-05-08 13:03:34 |
By :
mumi |
View :
1576 |
Reply :
3 |
|
 |
 |
 |
 |
|
|
|
|
 |
 |
|
 |
 |
| |
|
|
|
| |
|
เพิ่งเคยเจอคำสั่ง query ยาวขนาดนี้ 
คงต้องขอดู table ต่างๆ ที่เกี่ยวข้องแหละครับ ถึงจะรู้ว่าขาดหายอะไรไป
อีกอย่างถ้าเป็นผมจะทำการ query ข้อมูลหลักๆ
แล้วเอาแต่ละค่ามาเขียนโค้ดคำนวณจะสะดวกกว่า
แสดงผลได้ตรงตามที่ต้องการมากกว่า
อีกอย่าง คือ สามารถตวจสอบได้ง่ายว่า ตรงไหนที่ผิด
แต่ก็แล้วแต่ความถนัดครับ 
|
| |
|
|
| |
| Date :
2012-05-08 13:29:54 |
By :
slipknot1256 |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ถ้ายังไม่ได้มา left join กัน ข้อมูลออกถูกต้อง
ที่จริงทั้งสองตารางต่างกันแค่
ต้องการยอดขายปัจจุบัน กับยอดขายสะสมค่ะ
จากcode เก่า ถ้าไม่เอา name1 เพิ่มเข้ามา code เก่าเป็นแบบนี้ค่ะ
ข้อมูลออกมาถูกต้อง
Code
select t1.salesman, t1.total_price, t2.total_price1 from (
(select res_users.id,
res_users.name as salesman,
sum(ROUND((coalesce(sale_order_line.price_unit,0)* coalesce(product_uom_qty,0)),2)) as total_price
from sale_order
left join res_company on res_company.id = sale_order.company_id
left join res_partner on res_partner.id = sale_order.partner_id
left join res_partner_address on res_partner_address.id = sale_order.partner_invoice_id
left join res_partner_address as cusship on cusship.id = sale_order.partner_shipping_id
left join sale_order_line on sale_order_line.order_id = sale_order.id
left join product_uom on product_uom.id = sale_order_line.product_uom
left join product_product on sale_order_line.product_id = product_product.id
left join res_users on res_users.id = sale_order.user_id
left join product_template on product_product.product_tmpl_id = product_template.id
left join product_category on product_template.categ_id = product_category.id
left join product_thick on product_product.thick = product_thick.id
where sale_order.requested_date between '2012-05-01' and '2012-05-07'
and sale_order.state = 'done'
and (product_product.default_code like 'st%'
or default_code like 'sl%'
or default_code like 'sa%'
or default_code like 'sac%'
or default_code like 'stc%'
or default_code like 'sab%'
or default_code like 'stb%')
group by res_users.id, res_users.name)) t1
left join
((select res_users.id,
res_users.name as salesman1,
sum(ROUND((coalesce(sale_order_line.price_unit,0)* coalesce(product_uom_qty,0)),2)) as total_price1
from sale_order
left join res_company on res_company.id = sale_order.company_id
left join res_partner on res_partner.id = sale_order.partner_id
left join res_partner_address on res_partner_address.id = sale_order.partner_invoice_id
left join res_partner_address as cusship on cusship.id = sale_order.partner_shipping_id
left join sale_order_line on sale_order_line.order_id = sale_order.id
left join product_uom on product_uom.id = sale_order_line.product_uom
left join product_product on sale_order_line.product_id = product_product.id
left join res_users on res_users.id = sale_order.user_id
left join product_template on product_product.product_tmpl_id = product_template.id
left join product_category on product_template.categ_id = product_category.id
left join product_thick on product_product.thick = product_thick.id
where sale_order.requested_date = '2012-05-07'
and sale_order.state = 'done'
and (product_product.default_code like 'st%'
or default_code like 'sl%'
or default_code like 'sa%'
or default_code like 'sac%'
or default_code like 'stc%'
or default_code like 'sab%'
or default_code like 'stb%')
group by res_users.id, salesman1)) t2 on t1.id = t2.id
|
| |
|
|
| |
| Date :
2012-05-08 13:43:18 |
By :
mumi |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ไม่มีใครมาตอบต่อเลย
|
| |
|
|
| |
| Date :
2012-05-12 10:11:23 |
By :
mumi |
|
|
| |
|
|
|
|
|
| |
|
|
|
|
|
