mysql - Return multiple selected value inside one query?

Question

Sorry for title excuse my ignorance in developing a straight to the point title.

This code/scenario is simply for understanding where I'm coming from (I will not post real code), i'm not asking to evaluate this code nor to debug, there might be index errors etc. this was written in here without testing.

The tables are fabricated in here too, so if they look silly and doesn't make sense, you are right. But please try to keep in mind of what the problem is as that is universal and can be applied to any database schema in real world.

Problem

I need to count how many times a staff has sold to a particular customer, as well as return last item sold from that customer. The bold words are the problematic bit. I don't know how to create this bit of the query without damaging the counting part (How many customer a seller sold to), I tried using Order By but didnt returned what I needed.

 SELECT StaffName, Count(SoldToCustomerId)
 AS TimesSoldToCustomer, CustomerName, Item FROM CustomerHistory
 INNER JOIN Seller ON SoldToCustomerId = CustomerId
 GROUP BY SoldToCustomerId;  

Database

CustomerHistory
CustomerId     CustomerName   PurchasedDate  Item
1              John           01/02/2018     Iphone 
2              Tom            02/02/2018     Galaxy
3              Peter          03/02/2018     Ps4
1              John           05/02/2018     Xbox One
1              John           06/02/2018     Ps4
1              John           03/02/2018     PC
1              John           07/01/2017     graphic card

Seller
StaffId   StaffName     SoldToCustomerId
1         James         1
2         Tim           2
..


Ideal result from sql query
StaffName   TimesSoldToCustomer    CustomerName   lastSoldItem
James       5                      John           Ps4    -- Last Item Sold
Tim         1                      Tom            Galaxy -- Last Item Sold

标签： mysqlsql

Answer 1

use join and subquery like below

 select a.CustomerName,a.TimesSoldToCustomer ,
 s.StaffName     ,c.Item
 from  (
  SELECT  CustomerName, Count(SoldToCustomerId) as TimesSoldToCustomer 
  ,min(CustomerId) as  CustomerId  
  FROM CustomerHistory group by CustomerName
 ) a join Seller s on a.CustomerId =s.SoldToCustomerId
   join ( select CustomerName,PurchasedDate,Item
             from CustomerHistory t1 where PurchasedDate=( select max(PurchasedDate)
                          from CustomerHistory t2 where 
                           t1.CustomerName=t2.CustomerName)
        ) c on a.CustomerName=c.CustomerName