mysql - 将两列相乘为第三列，其中一列是sql中的count(*)

Question

我使用 group by 函数和 count(*) 对表进行了操作:

SELECT TOP 10000 
  [column1]
  ,[column2]
  ,[column3]
  ,count(*) as counts
FROM TableX
group by column1, column2,column3 

我的结果是看起来的表格:

column1 | column2 | column2 | Counts
------------------------------------        
   X1   |    X2   |    X3   |   X4
   X5   |    X6   |    X7   |   X8
   ...

现在如何添加一个附加列来显示 X3 * X4、X7 * X8 等的结果？

Answer 1

如果它实际上是 SQL-Server 而不是 MySQL，您可以使用以下内容:

WITH CTE AS
(
    SELECT TOP 10000 
       [column1]
      ,[column2]
      ,[column3]
      ,counts = count(*)  OVER (PARTITION BY column1, column2,column3)
      ,RN = row_number(*) OVER (PARTITION BY column1, column2,column3 Order By column1, column2,column3)
    FROM TableX
)
SELECT  [column1]
       ,[column2]
       ,[column3]
       ,counts
       ,Product1 = column3 * counts, 
       ...
FROM CTE
WHERE RN = 1