Mysql_ yesterday Nanjing all in-service salesman performance ranking-20170116

#计算南京销售员总业绩排名 data results have been disrupted

#职工信息表包含在职和离职两种状态 Therefore, it is not possible to use this table as the main table, or the data of the departing person is also present in the gross margin table salesperson is limited to yesterday's in-service sales and is consistent with the table salesperson behind the left join check out the corresponding other fields

#@i:[email protected]+1 Growth variable

Select C.*, (@i:[email protected]+1) as rank from (select a. City, B. Working hours, a. Salesperson, B. department, B. Post, B. entry date, DATEDIFF (Current_date, B. Entry date) as in-service days, a. Sales confirmation, a. Gross profit margin from (SELECT city, salesperson, SUM (sales) as sales acknowledgement, sum (gross margin) as margin amount, sum (gross margin)/sum (sales) as gross margin from ' A0 05_account ' WHERE city = ' Nanjing ' and Salesperson in (SELECT name from a032_admin_user WHERE city = ' Nanjing ' and working take several time >current_date and status = ' in service ') G Roup by Salesperson ORDER by SUM (gross margin) DESC) as Aleft JOIN (SELECT name, Department, position, entry date, status, number of hours from a032_admin_userwhere city = ' Nanjing ' and job take Number of times >current_date and state = ' in service ') as B on a. Salesperson =b. Name order by a. Gross margin DESC) as C, (SELECT @i:=0) as it

　　

Mysql_ yesterday Nanjing all in-service salesman performance ranking-20170116