How can I make this query easier to read_问答_开发者

I have a massive query below, the things I don't like are:

I can't reference item_tax_percent_regular and item_tax_percent_cumulative as alias inside the query. (I have to repeat the massive sum statement whenever I want to use them)
IT IS JUST BIG

The query does exactly what I want, but it is so big.

CREATE TEMPORARY TABLE phppos_sales_items_temp (SELECT phppos_sales.deleted as deleted, date(sale_time) as sale_date, phppos_sales_items.sale_id, comment,payment_type, customer_id, employee_id, phppos_items.item_id, NULL as item_kit_id, supplier_id, quantity_purchased, item_cost_price, item_unit_price, category, 

SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END) as item_tax_percent_regular, 
SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END) as item_tax_percent_cumulative, discount_percent, 
(item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100) as subtotal, phppos_sales_items.line as line, serialnumber, phppos_sales_items.description as description, 
ROUND((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)+ROUND((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100),2) +((ROUND((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100),2) + (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)) *(SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END))/100),2) as total, 

ROUND((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100),2) +((ROUND((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100),2) + (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)) *(SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END))/100) as tax, 

(item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100) - (item_cost_price*quantity_purchased) as profit FROM phppos_sales_items INNER JOIN phppos_sales ON phppos_sales_items.sale_id=phppos_sales.sale_id INNER JOIN phppos_items ON phppos_sales_items.item_id=phppos_items.item_id 

LEFT OUTER JOIN phppos_suppliers ON phppos_items.supplier_id=phppos_suppliers.person_id 
LEFT OUTER JOIN phppos_sales_items_taxes ON phppos_sales_items.sale_id=phppos_sales_items_taxes.sale_id and phppos_sales_items.item_id=phppos_sales_items_taxes.item_id and phppos_sales_items.line=phppos_sales_items_taxes.line
WHERE date(sale_time) BETWEEN "2011-07-28" and "2011-07-28" 
GROUP BY sale_id, item_id, line) 

UNION ALL

(SELECT phppos_sales.deleted as deleted, date(sale_time) as sale_date, phppos_sales_item_kits.sale_id, comment,payment_type, customer_id, employee_id, NULL as item_id, phppos_item_kits.item_kit_id, '' as supplier_id, quantity_purchased, item_kit_cost_price, item_kit_unit_price, category, 
SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END) as item_kit_tax_percent_regular, SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END) as item_kit_tax_percent_cumulative, discount_percent, (item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100) as subtotal,
phppos_sales_item_kits.line as line, '' as serialnumber, phppos_sales_item_kits.description as description, ROUND((item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)+ROUND((item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 开发者_运维问答THEN percent ELSE 0 END)/100),2) +((ROUND((item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100),2) + (item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)) *(SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END))/100),2) as total,

 ROUND((item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100),2) +((ROUND((item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100),2) + (item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)) *(SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END))/100) as tax, 

(item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100) - (item_kit_cost_price*quantity_purchased) as profit FROM phppos_sales_item_kits 
INNER JOIN phppos_sales ON phppos_sales_item_kits.sale_id=phppos_sales.sale_id INNER JOIN phppos_item_kits ON phppos_sales_item_kits.item_kit_id=phppos_item_kits.item_kit_id 
LEFT OUTER JOIN phppos_sales_item_kits_taxes ON phppos_sales_item_kits.sale_id=phppos_sales_item_kits_taxes.sale_id and phppos_sales_item_kits.item_kit_id=phppos_sales_item_kits_taxes.item_kit_id and phppos_sales_item_kits.line=phppos_sales_item_kits_taxes.line WHERE date(sale_time) 
BETWEEN "2011-07-28" and "2011-07-28" 
GROUP BY sale_id, item_kit_id, line) ORDER BY sale_id, line

Use "as" to create aliases:

...
LEFT OUTER JOIN phppos_sales_item_kits_taxes AS sikTaxes ON sikTaxes.sale_id=phppos_sales.sale_id
...

You can use the aliases you create throughout the query.

Yanking out some of the arithmetic into user defined functions might also help.

How about this? I've incorporated some line break formatting to assist. I put the operator on the next line (e.g. comma separation or subtraction) for easy commenting:

CREATE TEMPORARY TABLE phppos_sales_items_temp
(
SELECT phppos_sales.deleted as deleted
, date(sale_time) as sale_date
, phppos_sales_items.sale_id
, comment
, payment_type
, customer_id
, employee_id
, phppos_items.item_id
, NULL as item_kit_id
, supplier_id
, quantity_purchased
, item_cost_price
, item_unit_price
, category
, SUM(CASE
     WHEN cumulative != 1
     THEN percent
     ELSE 0
     END) as item_tax_percent_regular
, SUM(CASE
     WHEN cumulative = 1
     THEN percent
     ELSE 0
     END) as item_tax_percent_cumulative
, discount_percent
, (item_unit_price * quantity_purchased - item_unit_price * quantity_purchased * discount_percent / 100) as subtotal
, phppos_sales_items.line as line
, serialnumber
, phppos_sales_items.description as description
, ROUND((item_unit_price * quantity_purchased - item_unit_price * quantity_purchased * discount_percent / 100)
  + ROUND((item_unit_price * quantity_purchased - item_unit_price * quantity_purchased * discount_percent / 100)
  + (SUM(CASE
        WHEN cumulative != 1
        THEN percent
        ELSE 0
        END) / 100), 2)
  + ((ROUND((item_unit_price * quantity_purchased - item_unit_price * quantity_purchased * discount_percent / 100)
  * (SUM(CASE
        WHEN cumulative != 1
        THEN percent
        ELSE 0
        END) / 100), 2)
  + (item_unit_price * quantity_purchased - item_unit_price * quantity_purchased * discount_percent / 100))
  * (SUM(CASE
        WHEN cumulative = 1
        THEN percent
        ELSE 0
        END)) / 100), 2) as total
, ROUND((item_unit_price * quantity_purchased - item_unit_price * quantity_purchased * discount_percent / 100)
  * (SUM(CASE
        WHEN cumulative != 1
        THEN percent
        ELSE 0
        END) / 100), 2)
  + ((ROUND((item_unit_price * quantity_purchased - item_unit_price * quantity_purchased * discount_percent/100)
  * (SUM(CASE
        WHEN cumulative != 1
        THEN percent
        ELSE 0
        END) / 100), 2)
  + (item_unit_price * quantity_purchased - item_unit_price * quantity_purchased * discount_percent / 100))
  * (SUM(CASE
        WHEN cumulative = 1
        THEN percent
        ELSE 0
        END)) / 100) as tax
, (item_unit_price * quantity_purchased - item_unit_price * quantity_purchased * discount_percent / 100)
  - (item_cost_price * quantity_purchased) as profit
FROM phppos_sales_items
INNER JOIN phppos_sales ON phppos_sales_items.sale_id = phppos_sales.sale_id
INNER JOIN phppos_items ON phppos_sales_items.item_id = phppos_items.item_id 
LEFT OUTER JOIN phppos_suppliers
   ON phppos_items.supplier_id = phppos_suppliers.person_id 
LEFT OUTER JOIN phppos_sales_items_taxes
   ON phppos_sales_items.sale_id = phppos_sales_items_taxes.sale_id
   AND phppos_sales_items.item_id = phppos_sales_items_taxes.item_id
   AND phppos_sales_items.line = phppos_sales_items_taxes.line
WHERE date(sale_time) BETWEEN "2011-07-28" and "2011-07-28" 
GROUP BY sale_id
, item_id
, line
) 

UNION ALL

(
SELECT phppos_sales.deleted as deleted
, date(sale_time) as sale_date
, phppos_sales_item_kits.sale_id
, comment
, payment_type
, customer_id
, employee_id
, NULL as item_id
, phppos_item_kits.item_kit_id
, '' as supplier_id
, quantity_purchased
, item_kit_cost_price
, item_kit_unit_price
, category
, SUM(CASE
     WHEN cumulative != 1
     THEN percent
     ELSE 0
     END) as item_kit_tax_percent_regular
, SUM(CASE
     WHEN cumulative = 1
     THEN percent
     ELSE 0
     END) as item_kit_tax_percent_cumulative
, discount_percent
, (item_kit_unit_price * quantity_purchased-item_kit_unit_price * quantity_purchased * discount_percent / 100) as subtotal
, phppos_sales_item_kits.line as line
, '' as serialnumber
, phppos_sales_item_kits.description as description
, ROUND((item_kit_unit_price * quantity_purchased - item_kit_unit_price * quantity_purchased * discount_percent / 100)
  + ROUND((item_kit_unit_price * quantity_purchased - item_kit_unit_price * quantity_purchased * discount_percent / 100)
  * (SUM(CASE
        WHEN cumulative != 1
        THEN percent
        ELSE 0
        END) / 100), 2)
  + ((ROUND((item_kit_unit_price * quantity_purchased - item_kit_unit_price * quantity_purchased * discount_percent / 100)
  * (SUM(CASE
        WHEN cumulative != 1
        THEN percent
        ELSE 0
        END) / 100), 2)
  + (item_kit_unit_price * quantity_purchased - item_kit_unit_price * quantity_purchased * discount_percent / 100))
  * (SUM(CASE WHEN
        cumulative = 1
        THEN percent
        ELSE 0
        END)) / 100), 2) as total
, ROUND((item_kit_unit_price * quantity_purchased - item_kit_unit_price * quantity_purchased * discount_percent / 100)
  * (SUM(CASE
     WHEN cumulative != 1
     THEN percent
     ELSE 0
     END) / 100), 2)
  + ((ROUND((item_kit_unit_price * quantity_purchased - item_kit_unit_price * quantity_purchased * discount_percent / 100)
  * (SUM(CASE
        WHEN cumulative != 1
        THEN percent
        ELSE 0
        END) / 100), 2)
  + (item_kit_unit_price * quantity_purchased - item_kit_unit_price * quantity_purchased * discount_percent / 100))
  * (SUM(CASE
        WHEN cumulative = 1
        THEN percent
        ELSE 0
        END)) / 100) as tax
, (item_kit_unit_price * quantity_purchased - item_kit_unit_price * quantity_purchased * discount_percent / 100)
  - (item_kit_cost_price * quantity_purchased) as profit
FROM phppos_sales_item_kits 
INNER JOIN phppos_sales
   ON phppos_sales_item_kits.sale_id = phppos_sales.sale_id
INNER JOIN phppos_item_kits
   ON phppos_sales_item_kits.item_kit_id = phppos_item_kits.item_kit_id 
LEFT OUTER JOIN phppos_sales_item_kits_taxes
   ON phppos_sales_item_kits.sale_id = phppos_sales_item_kits_taxes.sale_id
   AND phppos_sales_item_kits.item_kit_id = phppos_sales_item_kits_taxes.item_kit_id
   AND phppos_sales_item_kits.line = phppos_sales_item_kits_taxes.line
WHERE date(sale_time) BETWEEN "2011-07-28" and "2011-07-28" 
GROUP BY sale_id
, item_kit_id
, line
)
ORDER BY sale_id
, line