当前位置：文江博客话题详情

可以用SQL建这个表吗？

发布于 2025-01-11 15:25:24 字数 520 浏览 0 评论 0原文

我有 2 个表，一个销售表和一个付款表，结构如下。

这两个值使用 ContractID 列连接起来。我想看到的是一个矩阵，在顶部显示每月（销售量）的总和。然后在左侧，我想按月查看付款日期，以及已付款的任何付款。我理想的输出如下所示。

黄线是按月、年销售的总金额，绿线是付款表中已支付的所有付款。我真的不知道从哪里开始，有人对如何实现这一目标有任何建议吗？我将首先取消旋转已售出的表格，以使我的日期位于顶部，只是考虑下一步将这张表格整合在一起？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我要还你自由 2025-01-18 15:25:24

If I didn't understand wrong, it should be like this.

  WITH PaymentMatrix
  AS
  (
  SELECT
      PaymentMonth,
      SoldAmount,
      [1] AS Jan,
      [2] AS Feb,
      [3] AS Mrz,
      [4] AS Apr,
      [5] AS Mai,
      [6] AS Jun,
      [7] AS Jul,
      [8] AS Aug,
      [9] AS Sep,
      [10] AS Okt,
      [11] AS Nov,
      [12] AS Dez
  FROM
  (
  Select
  MONTH(S.SoldDate) as  SoldMonth,
  MONTH(P.PaymentDate) as PaymentMonth,
  SUM(S.SoldAmount) as SoldAmount, 
  SUM(P.PaymentAmount) as PaymentAmount
  from Sales S
  INNER JOIN Payment P ON S.ContractID = P.ContractID 
  GROUP BY 
  MONTH(S.SoldDate), 
  MONTH(P.PaymentDate) 
    ) source
  PIVOT
  (
      SUM(PaymentAmount)
      FOR SoldMonth
      IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] )
  ) AS pvtMonth
  )

  SELECT
   PaymentMonth, 
   SUM(SoldAmount) AS Sold,
   sum(Jan)as Jan , sum(Feb) as Feb, sum(Mrz) as Mrz, sum(Apr) as Apr, sum(Mai) as Mai, 
   sum(Jun)as Jun , sum(Jul) as Jul, sum(Aug) as Aug, sum(Sep) as Sep, sum(Okt) as Okt,
   sum(Nov) as Nov, sum(Dez) as Dez   
  FROM PaymentMatrix
  GROUP BY PaymentMonth

Fidler 示例

示例图片

If I didn't understand wrong, it should be like this.

  WITH PaymentMatrix
  AS
  (
  SELECT
      PaymentMonth,
      SoldAmount,
      [1] AS Jan,
      [2] AS Feb,
      [3] AS Mrz,
      [4] AS Apr,
      [5] AS Mai,
      [6] AS Jun,
      [7] AS Jul,
      [8] AS Aug,
      [9] AS Sep,
      [10] AS Okt,
      [11] AS Nov,
      [12] AS Dez
  FROM
  (
  Select
  MONTH(S.SoldDate) as  SoldMonth,
  MONTH(P.PaymentDate) as PaymentMonth,
  SUM(S.SoldAmount) as SoldAmount, 
  SUM(P.PaymentAmount) as PaymentAmount
  from Sales S
  INNER JOIN Payment P ON S.ContractID = P.ContractID 
  GROUP BY 
  MONTH(S.SoldDate), 
  MONTH(P.PaymentDate) 
    ) source
  PIVOT
  (
      SUM(PaymentAmount)
      FOR SoldMonth
      IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] )
  ) AS pvtMonth
  )

  SELECT
   PaymentMonth, 
   SUM(SoldAmount) AS Sold,
   sum(Jan)as Jan , sum(Feb) as Feb, sum(Mrz) as Mrz, sum(Apr) as Apr, sum(Mai) as Mai, 
   sum(Jun)as Jun , sum(Jul) as Jul, sum(Aug) as Aug, sum(Sep) as Sep, sum(Okt) as Okt,
   sum(Nov) as Nov, sum(Dez) as Dez   
  FROM PaymentMatrix
  GROUP BY PaymentMonth

Fidler Sample

Sample Image

回复收藏 0 原文

从来不烧饼 2025-01-18 15:25:24

我建议使用条件聚合和联合。
由于 PIVOT 语法更受限制。

选择 [已售出]、[1 月 22 日]、[2 月 22 日]、[3 月 22 日]
从
（
选择 0 作为序列，“已付款”AS [已售出]
, SUM(CASE WHEN FORMAT([销售日期],'MMM-yy') = 'Jan-22' 
      THEN [售出金额] ELSE 0 END) AS [1 月 22 日]
, SUM(CASE WHEN FORMAT([销售日期],'MMM-yy') = '2 月 22 日' 
      THEN [售出金额] ELSE 0 END) AS [2 月 22 日]
, SUM(CASE WHEN FORMAT([销售日期],'MMM-yy') = 'Mar-22' 
      THEN [已售出金额] ELSE 0 END) AS [3 月 22 日]
来自销售

联合所有

选择 m.Seq、m.PaymentMonth 
, SUM(CASE WHEN SoldMonth = 'Jan-22' THEN PaymentAmount ELSE 0 END) AS [Jan-22]
, SUM(CASE WHEN SoldMonth = 'Feb-22' THEN PaymentAmount ELSE 0 END) AS [Feb-22]
, SUM(CASE WHEN SoldMonth = 'Mar-22' THEN PaymentAmount ELSE 0 END) AS [Mar-22]
来自（值
(1,'1 月 22 日'),
(2,'2 月 22 日'),
(3,'3 月 22 日')
) m(Seq, 付款月份)
左连接 (
  选择合约ID
  , FORMAT(EOMONTH([付款日期]), 'MMM-yy') AS 付款月份
  , SUM([付款金额]) AS PaymentAmount
  从付款
  GROUP BY ContractID, EOMONTH([付款日期])
) p ON p.PaymentMonth = m.PaymentMonth
左连接（
  选择合约ID
  , FORMAT(MAX([销售日期]), 'MMM-yy') AS SoldMonth
  , SUM([销售金额]) AS 销售金额
  来自销售
  按合约 ID 分组
) s ON s.ContractID = p.ContractID
按 m.Seq、m.PaymentMonth 分组
）q
按顺序排序；

<表类=“s-表”>
<标题>
已售出
1 月 22 日
2 月 22 日
3 月 22 日

<正文>
付费
2500
100
0
1 月 22 日
300
0
0
2 月 22 日
400
50
0
3 月 22 日
0
0
0

在 db<>fiddle 此处

I suggest using conditional aggregation and a union.
Since the PIVOT syntax is more limited.

SELECT [Sold], [Jan-22], [Feb-22], [Mar-22]
FROM
(
SELECT 0 as Seq, 'Paid' AS [Sold]
, SUM(CASE WHEN FORMAT([Sold Date],'MMM-yy') = 'Jan-22' 
      THEN [Sold Amount] ELSE 0 END) AS [Jan-22]
, SUM(CASE WHEN FORMAT([Sold Date],'MMM-yy') = 'Feb-22' 
      THEN [Sold Amount] ELSE 0 END) AS [Feb-22]
, SUM(CASE WHEN FORMAT([Sold Date],'MMM-yy') = 'Mar-22' 
      THEN [Sold Amount] ELSE 0 END) AS [Mar-22]
FROM Sales

UNION ALL

SELECT m.Seq, m.PaymentMonth 
, SUM(CASE WHEN SoldMonth = 'Jan-22' THEN PaymentAmount ELSE 0 END) AS [Jan-22]
, SUM(CASE WHEN SoldMonth = 'Feb-22' THEN PaymentAmount ELSE 0 END) AS [Feb-22]
, SUM(CASE WHEN SoldMonth = 'Mar-22' THEN PaymentAmount ELSE 0 END) AS [Mar-22]
FROM (VALUES
(1,'Jan-22'),
(2,'Feb-22'),
(3,'Mar-22')
) m(Seq, PaymentMonth)
LEFT JOIN (
  SELECT ContractID
  , FORMAT(EOMONTH([Payment Date]), 'MMM-yy') AS PaymentMonth
  , SUM([Payment Amount]) AS PaymentAmount
  FROM Payment
  GROUP BY ContractID, EOMONTH([Payment Date])
) p ON p.PaymentMonth = m.PaymentMonth
LEFT JOIN (
  SELECT ContractID
  , FORMAT(MAX([Sold Date]), 'MMM-yy') AS SoldMonth
  , SUM([Sold Amount]) AS SoldAmount
  FROM Sales
  GROUP BY ContractID
) s ON s.ContractID = p.ContractID
GROUP BY m.Seq, m.PaymentMonth
) q
ORDER BY Seq;