将新输入的行与另一个列值相乘，然后在SQL中找到总和 [英] Multiply newly entered row with another column value and find Total Sum in SQL

问题描述

我这里有4个表，我需要将一个表中新输入的行值与另一行相乘，并使用CustomerId查找总和:

I have 4 tables here, I need to multiply newly entered row value in a table with another row and find the total sum using CustomerId:

CustomerTable :

CustomerId Name   EmailId
-------------------------
1          Paul   [e-mail]
2          John   [e-mail]

LoyaltyPointTable :

LoyaltyPointsId LoyaltyType     Points
---------------------------------------
1               Registration       10
2               Loginstatus         1
3               Downloading        10
4               Redemming           1
5               Sharing            20
6               Refer              10

忠诚度详细信息表:

 LoyaltyDetailsId LoyaltyPointsId CustomerId Dates
 -------------------------------------------------
  1                    1            1       2015-01-22 
  2                    2            1       2015-01-22 
  3                    3            2       2015-01-22
  4                    3            1       2015-01-22 
  5                    4            1       2015-01-22
  6                    4            1       2015-01-24 
  7                    5            1       2015-01-24 

此查询对于每个LoyaltyType

SELECT   
   LoayaltyPointsTable.LoyaltyType,
   COUNT(CustomerTable.CustomerId) AS UserActions,
   SUM(LoayaltyPointsTable.Points) AS TotalPoints 
FROM 
   LoayaltyPointsTable 
JOIN 
   LoyaltyDetailsTable ON LoayaltyPointsTable.LoyaltyPointsId = LoyaltyDetailsTable.LoyaltyPointsId
JOIN
   CustomerTable ON CustomerTable.CustomerId = LoyaltyDetailsTable.CustomerId 
WHERE
   CustomerTable.CustomerId = 1
GROUP BY
   LoyaltyDetailsTable.CustomerId ,LoayaltyPointsTable.LoyaltyType 

在RedeemPointsTable下面的

是与与LoyaltyPointTable中的行兑换有关的创建的:

below RedeemPointsTable is created with relation to row redeeming in LoyaltyPointTable:

RedeemPointsTable :

 RedeemPointsId CustomerId ShopName BillNo Amount
 ------------------------------------------------
 1                   1      Mall x  4757    100
 3                   1      Mall y  SH43    50
 4                   1      Mall x  7743    10
 6                   1      Mall x  s34a    60

我期望的是在计算总和之前，我希望将LoyaltyPointTable中Redeeming中的Amount总和(100+50+10+60) * 1列与每个CustomerId

What I am expecting is before calculating the total sum, I want column Amount sum (100+50+10+60) * 1 in Redeeming in LoyaltyPointTable to be added with total points for each CustomerId

预期输出

LoyaltyType UserActions TotalPoints
-------------------------------------
Downloading     1        10
Loginstatus     1         1
Redemming       4   (100+50+10+60)*1(here using Amount in RedeemPointsTable)
Refer           1        10
Registration    1        10
Sharing         1        20

用户操作计数为4，它基于他在RedeemPointsTable

User actions count is 4, it is based on the Amount he entered in RedeemPointsTable

我需要在RedeemPointsTable中添加外键列时进行更改，还是可以指出我的错误?

Should I need to make changes in adding a foreign key column in RedeemPointsTable or can you point out my mistake?

任何帮助都会很棒.

推荐答案

这是返回所需结果的查询:

This is the query which returns desired result:

SELECT 
   LoyaltyPointTable.LoyaltyType, 
   CASE 
      WHEN LoyaltyPointTable.LoyaltyPointsId=4 THEN (SELECT COUNT(amount) FROM RedeemPointsTable where CustomerId=1) 
      ELSE COUNT(CustomerTable.CustomerId) 
   END as UserActions, 
   CASE 
      WHEN LoyaltyPointTable.LoyaltyPointsId=4 THEN (SELECT SUM(amount) FROM RedeemPointsTable where CustomerId=1)*Points
      ELSE SUM(LoyaltyPointTable.Points) 
    END as TotalPoints
FROM 
   LoyaltyPointTable 
JOIN 
   LoyaltyDetailsTable ON LoyaltyPointTable.LoyaltyPointsId =     LoyaltyDetailsTable.LoyaltyPointsId
JOIN
   CustomerTable ON CustomerTable.CustomerId = LoyaltyDetailsTable.CustomerId 
WHERE
   CustomerTable.CustomerId = 1
GROUP BY
   LoyaltyDetailsTable.CustomerId ,LoyaltyPointTable.LoyaltyType 

您可以在此处

这篇关于将新输入的行与另一个列值相乘，然后在SQL中找到总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！