MySQL: le Retour de plusieurs colonnes à partir d'une sous-requête

Question

Je suis de la création d'une instruction SQL qui sera de retour une, mois par mois, résumé sur les ventes.

Le résumé est une liste de certains des colonnes simples pour la date, le nombre total de ventes et la valeur totale des ventes.

Cependant, en plus de ces colonnes, j'aimerais inclure des 3 plus qui liste les mois meilleur client du montant dépensé. Pour ces colonnes, j'ai besoin d'une sorte de inline sous-requête peut renvoyer son ID, le Nom et le Montant qu'ils ont dépensé.

Mon effort actuel utilise un inline SELECT déclaration, cependant, à partir de mes connaissances sur la façon de mettre en œuvre les présentes, vous ne pouvez retourner une colonne, et rangée par dans la ligne de déclaration.

Pour contourner ce problème avec mon scénario, je peut bien sûr créer 3 distincte dans les instructions de ligne, cependant, à côté de cette soi-disant impossible, il augmente la durée de la requête plus que nécessaire.

SELECT  
    DATE_FORMAT(OrderDate,'%M %Y') AS OrderMonth,
    COUNT(OrderID) AS TotalOrders, 
    SUM(OrderTotal) AS TotalAmount, 

    (SELECT SUM(OrderTotal) FROM Orders WHERE DATE_FORMAT(OrderDate,'%M %Y') = OrderMonth GROUP BY OrderCustomerFK ORDER BY SUM(OrderTotal) DESC LIMIT 1) AS TotalCustomerAmount,

    (SELECT OrderCustomerFK FROM Orders WHERE DATE_FORMAT(OrderDate,'%M %Y') = OrderMonth GROUP BY OrderCustomerFK ORDER BY SUM(OrderTotal) DESC LIMIT 1) AS CustomerID,

    (SELECT CustomerName FROM Orders INNER JOIN Customers ON OrderCustomerFK = CustomerID WHERE DATE_FORMAT(OrderDate,'%M %Y') = OrderMonth GROUP BY OrderCustomerFK ORDER BY SUM(OrderTotal) DESC LIMIT 1) AS CustomerName

FROM Orders     
GROUP BY DATE_FORMAT(OrderDate,'%m%y')
ORDER BY DATE_FORMAT(OrderDate,'%y%m') DESC

Comment puis-je améliorer la structure de cette requête?

---

SOLUTION COMPLÈTE

Après quelques réglages de Dave Barkers solution, j'ai une version finale pour n'importe qui dans l'avenir à la recherche de l'aide.

La solution par Dave Barker a fonctionné parfaitement avec les coordonnées du client, cependant, il a fait le plus simple, le Total des Ventes et le Montant Total de la Vente des colonnes d'obtenir quelques fous des chiffres.

SELECT  
        Y.OrderMonth, 	Y.TotalOrders,	Y.TotalAmount,
        Z.OrdCustFK,  Z.CustCompany, 	Z.CustOrdTotal, Z.CustSalesTotal   


 FROM 
    	(SELECT
    		OrdDate,
    		DATE_FORMAT(OrdDate,'%M %Y') AS OrderMonth, 
    		COUNT(OrderID) AS TotalOrders, 
    		SUM(OrdGrandTotal) AS TotalAmount
    		FROM Orders
    		WHERE OrdConfirmed = 1    
    		GROUP BY DATE_FORMAT(OrdDate,'%m%y') 
    		ORDER BY DATE_FORMAT(OrdDate,'%Y%m') DESC)
    Y INNER JOIN 
    	(SELECT 
    		DATE_FORMAT(OrdDate,'%M %Y') AS CustMonth, 
    		OrdCustFK, 
    		CustCompany, 
    		COUNT(OrderID) AS CustOrdTotal,
    		SUM(OrdGrandTotal) AS CustSalesTotal 
    	FROM Orders INNER JOIN CustomerDetails ON OrdCustFK = CustomerID
    	WHERE OrdConfirmed = 1
    	GROUP BY DATE_FORMAT(OrdDate,'%m%y'), OrdCustFK 
    	ORDER BY SUM(OrdGrandTotal) DESC) 
    Z ON Z.CustMonth = Y.OrderMonth

GROUP BY DATE_FORMAT(OrdDate,'%Y%m')
ORDER BY DATE_FORMAT(OrdDate,'%Y%m') DESC

Answer 1

Déplacer la ligne de SQL d'une requête de jointure interne. Donc, si vous voulez avoir quelque chose comme...

SELECT  DATE_FORMAT(OrderDate,'%M %Y') AS OrderMonth, COUNT(OrderID) AS TotalOrders, SUM(OrderTotal) AS TotalAmount,  Z.OrderCustomerFK, Z.CustomerName, z.OrderTotal as CustomerTotal   
  FROM Orders     
  INNER JOIN (SELECT DATE_FORMAT(OrderDate,'%M %Y') as Mon, OrderCustomerFK, CustomerName, SUM(OrderTotal) as OrderTotal 
                FROM Orders 
               GROUP BY  DATE_FORMAT(OrderDate,'%M %Y'), OrderCustomerFK, CustomerName ORDER BY SUM(OrderTotal) DESC LIMIT 1) Z
          ON Z.Mon = DATE_FORMAT(OrderDate,'%M %Y')
    GROUP BY DATE_FORMAT(OrderDate,'%m%y'), Z.OrderCustomerFK, Z.CustomerName
    ORDER BY DATE_FORMAT(OrderDate,'%y%m') DESC

Answer 2

Donner un coup de cette:

  SELECT CONCAT(o.order_month, ' ', o.order_year),
         o.total_orders,
         o.total_amount,
         x.sum_order_total,
         x.ordercustomerfk,
         x.customername
    FROM (SELECT MONTH(t.orderdate) AS order_month,
                 YEAR(t.orderdate) AS order_year
                 COUNT(t.orderid) AS total_orders, 
                 SUM(t.ordertotal) AS total_amount
            FROM ORDERS t
        GROUP BY MONTH(t.orderdate), YEAR(t.orderdate)) o
    JOIN (SELECT MONTH(t.orderdate) AS ordermonth,
                 YEAR(t.orderdate) AS orderyear
                 SUM(t.ordertotal) 'sum_order_total',
                 t.ordercustomerfk,
                 c.customername
            FROM ORDERS t
            JOIN CUSTOMERS c ON c.customerid = o.ordercustomerfk
        GROUP BY t.ordercustomerfk, MONTH(t.orderdate), YEAR(t.orderdate)) x ON x.order_month = o.order_month
                                                                            AND x.order_year = o.order_year
ORDER BY o.order_year DESC, o.order_month DESC

Answer 3

Vous pouvez aussi faire quelque chose comme:

SELECT 
    a.`y`,
    ( SELECT @c:=NULL ) AS `temp`,
    ( SELECT @d:=NULL ) AS `temp`,
    ( SELECT 
          CONCAT(@c:=b.`c`, @d:=b.`d`) 
      FROM `b`
      ORDER BY b.`uid` 
      LIMIT 1 ) AS `temp`,
    @c as c,
    @d as d
 FROM `a`