使用SQL -如何聚合支付类型

Question

如何聚合PaymentType字段，以便如果客户以前使用过所有支付类型，则支付类型屏幕将显示“all”而不是“现金”、“支票”、“信用卡/借记卡”或“公司帐户”？下面是演示这个问题所需的脚本。

​

​

CREATE TABLE #Customer (
    CustomerId int,
    CustomerName nvarchar(100),
    Address nvarchar(100),
)
INSERT INTO #Customer
VALUES (1, 'Bill', '123 1st St'),
       (2, 'Fred', '111 Market St'),
       (3, 'Lisa', '01 Boulevard')

CREATE TABLE #Product (
    ProductId int,
    ProductName nvarchar(100)
)
INSERT INTO #Product
VALUES (1, 'Corn Flakes'),
       (2, 'Cheerios'),
       (3, 'Granoloa')

CREATE TABLE #PaymentType (
    PaymentId int,
    PaymentTypeName nvarchar(100)
)

INSERT INTO #PaymentType
VALUES (1, 'Cash'),
       (2, 'Check'),
       (3, 'Credit/Debit Card'),
       (4, 'Corporate Account')

CREATE TABLE #TransactionLog (
    LogId int,
    ProductId int,
    CustomerId int,
    PaymentId int,
    Amount float
)
INSERT INTO #TransactionLog
VALUES (1, 1, 1, 1, 2.00),
       (2, 2, 1, 2, 2.40),
       (3, 3, 1, 3, 1.80),
       (4, 1, 1, 4, 2.00),
       (5, 1, 2, 4, 2.00),
       (6, 2, 3, 2, 2.40)

SELECT * from #Customer
SELECT * from #Product
SELECT * from #PaymentType
SELECT * from #TransactionLog

SELECT
    c.CustomerName,
    p.ProductName,
    pt.PaymentTypeName as PaymentType
FROM #TransactionLog t
join #Product p on t.ProductId = p.ProductId
join #Customer c on t.CustomerId = c.CustomerId
join #PaymentType pt on t.PaymentId = pt.PaymentId

DROP TABLE #Customer
DROP TABLE #Product
DROP TABLE #PaymentType
DROP TABLE #TransactionLog

Answer 1

像这样的东西会有用的。但是，如果有大量数据或数据库实例不足，则可能需要对其进行调优。

;WITH
Data AS(
    SELECT
        c.CustomerName,
        p.ProductName,
        pt.PaymentTypeName as PaymentType
    FROM #TransactionLog t
    join #Product p on t.ProductId = p.ProductId
    join #Customer c on t.CustomerId = c.CustomerId
    join #PaymentType pt on t.PaymentId = pt.PaymentId
)
SELECT DISTINCT
    d.CustomerName,
    d.ProductName,
    PaymentType = IIF( ( (SELECT COUNT(DISTINCT sd.PaymentType) FROM Data sd WHERE sd.CustomerName = d.CustomerName) = 4), 
                    'All', 
                    d.PaymentType
                )
FROM
    Data d

Answer 2

select CustomerName,case when count(distinct(PaymentType)) = 4 then 'All' end as PaymentType from (SELECT
    c.CustomerName,
    p.ProductName,
    pt.PaymentTypeName as PaymentType
FROM #TransactionLog t
join #Product p on t.ProductId = p.ProductId
join #Customer c on t.CustomerId = c.CustomerId
join #PaymentType pt on t.PaymentId = pt.PaymentId )
group by CustomerName 

这是一种方法，你可以根据你的需要调整它。