Nick Loay Oraby

看起来您正在尝试添加一个生成的列,在MySQL中,其语法为:

Code VARCHAR(3) AS (SUBSTRING(Num, 3, 3))

Here 是中生成的列的手动页 CREATE TABLE 语句。

您可以将第二个查询包装为子查询,然后选择 MAX 价值 cap (应与任何其他项相同),分组依据 VM :

SELECT VM, MAX(cap)
FROM (SELECT `VM`,`datestamp`, SUM(CapacityGB) as cap 
      FROM stats 
      GROUP BY `VM`,`datestamp`) s
GROUP BY VM

没有样本数据是很难确定的,但是这样的事情应该是有效的:

SELECT DISTINCT c.*
FROM LGCUSTOMER c
JOIN LGINVOICE i ON i.Cust_Code = c.Cust_Code
JOIN LGLINE l ON l.Inv_Num = i.Inv_Num
JOIN LGPRODUCT p ON p.Prod_SKU = l.Prod_SKU
JOIN LGBRAND b ON b.Brand_ID = p.Brand_ID
WHERE b.Brand_Name = 'Foresters Best' AND
      p.Prod_Category = 'Top Coat' AND
      i.Inv_Date BETWEEN '2013-07-15' AND '2013-07-31'
ORDER BY c.Cust_State, c.Cust_Lname, c.Cust_Fname

这不起作用,因为 GROUP 惯性导航与制导。你需要把 组 作为子查询,并根据该查询计算运行总数:

SELECT monthname as month, year, contracts, totals,
       @running_total := @running_total + totals AS running_totals
FROM (SELECT 
          MONTHNAME(w.app_date) monthname,
          MONTH(w.app_date) monthnum,
          YEAR(w.app_date) year,
          COUNT(*) contracts,
          SUM(w.total_price) totals
      FROM (SELECT 
                app_date,
                SUM(price * term) total_price
                FROM _loans l
                GROUP BY l.loan_id
      ) w
      GROUP BY year, monthnum, monthname
) t
JOIN (SELECT @running_total := 0) r
ORDER BY year, monthnum ASC

输出:

month       year    contracts   totals      running_totals
January     2019    6           63234.72    63234.71923828125
February    2019    10          85703.04    148937.75939941406
March       2019    4           46727.04    195664.79919433594

Demo on dbfiddle