Question

I have the following query that gives me the data that I want, however, I need the total sum of the Cash, Credit and Check columns inside my CASE statements. How can I achieve this? I d like to use a procedure for this if possible.

而且,对我来说,这种问候似乎完全如此有效。谁能改善这种状况? 在我看来,我应当能够确定变量,然后在我的化学文摘社国际协会声明中加以使用,但不能确定它是如何做到的。

SELECT
t1.order_id,
t3.price * SUM( t1.quantity ) AS subtotal,
( SUM( t1.discount ) + t3.discount ) / 100 AS disc,
t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 AS tax,
t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 AS total,
CASE t2.payment_type WHEN  Cash   THEN t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 ELSE 0.00 END AS cash,
CASE t2.payment_type WHEN  Card   THEN t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 ELSE 0.00 END AS card,
CASE t2.payment_type WHEN  Check  THEN t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 ELSE 0.00 END AS check
FROM pos_item_order AS t1
Join pos_order AS t2 ON t2.order_id = t1.order_id
Join inv_item AS t3 ON t3.item_id = t1.item_id
GROUP BY t1.order_id, t1.item_id

EDIT:当我说“需要现金、信贷和支票栏的总和”,我指的是各栏的总额。

Answer 1

这是一种令人痛心的争辩,但简单地延伸到你已经给你的东西,我认为你要求:

SELECT t1.order_id,
       t3.price * SUM( t1.quantity ) AS subtotal,
       ( SUM( t1.discount ) + t3.discount ) / 100 AS disc,
       t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 AS tax,
       t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 AS total,
       CASE t2.payment_type WHEN  Cash 
       THEN t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100
       ELSE 0.00 END AS cash,
       CASE t2.payment_type WHEN  Card 
       THEN t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100
       ELSE 0.00 END AS card,
       CASE t2.payment_type WHEN  Check 
       THEN t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100
       ELSE 0.00 END AS check,
       CASE WHEN t2.payment_type IN ( Cash ,  Card ,  Check )
       THEN t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 ELSE 0.00 END AS cash_card_check
  FROM pos_item_order AS t1
  JOIN pos_order AS t2 ON t2.order_id = t1.order_id
  JOIN inv_item AS t3 ON t3.item_id = t1.item_id
 GROUP BY t1.order_id, t1.item_i

。然而,我可以提供帮助,但认为必须有一个更好的办法来安排你的询问。

这使你了解基本细节,包括付款类型。如果房舍管理处支持的话,将其保留为临时表格,或用一个条款表示。

SELECT t1.order_id,
       t2.payment_type,
       t3.price * SUM( t1.quantity ) AS subtotal,
       ( SUM( t1.discount ) + t3.discount ) / 100 AS disc,
       t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 AS tax,
       t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 AS total,
       t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 AS payment
  FROM pos_item_order AS t1
  JOIN pos_order      AS t2 ON t2.order_id = t1.order_id
  JOIN inv_item       AS t3 ON t3.item_id = t1.item_id
 GROUP BY t1.order_id, t1.item_i, t2.payment_type

然后,你可以单独和共同地将卡片、支票和现金合并起来。

Answer 2

不幸的是,你可以使用另一个表述中的衍生栏目,但为了实现你这样的目标,你可以

如果其他人已经对我说了话,那么我就给它一个明天,但我想我给你在时间上走方向。

友情链接