如何将表信息分组到两个组 [英] How do I group my table information to two groups

问题描述

我有一个销售发票表，其中包含现场名称付款方式，现金金额，卡金额，我需要将所有发票分组为两组现金账单/卡账单。

如果付款方式是现金然后卡金额字段为0.如果支付模式是卡，则现金金额字段为0.如果支付模式是现金和卡（客户支付现金和剩余余额作为卡的情况），则两个字段都包含值。 br />


问题是当支付模式都是，我的发票应该分两组。

请帮帮我



我尝试了什么：



当cash_amount = 0且card_amount！= 0然后'卡片时的SELECT情况比尔'

当card_amount = 0和cash_amount！= 0然后'现金账单'

当cash_amount！= 0和card_amount！= 0然后''

else''以BillTyps结尾

I have a sales invoice table having field names payment mode, cash amount, card amount and I need to group all my invoices under two groups cash bill/card bill.
if the payment mode is cash then card amount field is 0. if the payment mod is card then cash amount field is 0. if payment mod is both cash and card(situation when a customer pay as cash and remaining balance as card) then both fields contain value.

Issue is when payment mode is both, my invoice should go in both groups.
Please help me

What I have tried:

SELECT case when cash_amount=0 and card_amount!=0 then 'Card Bill'
when card_amount=0 and cash_amount!=0 then 'cash bill'
when cash_amount!=0 and card_amount!=0 then ''
else '' end as BillTyps

推荐答案

我不确定我是否理解你，但请查看：

I'm not sure i understand you well, but check this:

DECLARE @invoice TABLE(payment_mode VARCHAR(30), cash_amount DECIMAL(8,2), card_amount DECIMAL(8,2))

INSERT INTO @invoice(payment_mode, cash_amount, card_amount)
VALUES('cash', 500, 0), ('cash', 255, 0),
('card', 0, 1500), ('card', 0, 299),
('cash', 111, 0), ('cash', 222, 0),
('card', 0, 88), ('card', 0, 77),
('cash and card', 200, 300), ('cash and card', 300, 200)

;WITH CTE AS
(
	SELECT *
	FROM @invoice
	WHERE payment_mode <>'cash and card'
	UNION ALL
	SELECT 'cash', cash_amount, 0
	FROM @invoice
	WHERE payment_mode ='cash and card'
	UNION ALL
	SELECT 'card', 0, card_amount 
	FROM @invoice
	WHERE payment_mode ='cash and card'
)
SELECT C.*, CASE WHEN C.payment_mode = 'cash' THEN 'CashBill' ELSE 'CardBill' END AS BillType 
FROM CTE AS C

结果集包含12条记录，因为现金和卡片数据已分割为4行（而不是2行）。

A result set contains 12 records, because 'cash and card' data have been "splited" into 4 rows (instead of 2).

这篇关于如何将表信息分组到两个组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！