10.5ORM Review (2)

2018-10-5 14:47:57

The more you try, the luckier you will be!

An ORM aggregation and grouping query!!!

   ##################### #聚合和分组 ############################    #################### #聚合    #Check the price of all books     fromDjango.db.modelsImportSum, Count, AVG ret= Models. Bool.objects.all (). Aggregate (Price_sum=sum (" Price"))    Print(ret)#{' price_sum ': ' Decimal (' 554.00 ') '}    #Query The average age of all authorsRET = models. Author.objects.all (). Aggregate (Age_avg=avg (" Age"))    ## # #----------------------------------------grouping    """    Single-table group EMP ID name age DEP 1 Alex 22 Security Department 2 Egon 33 Security Department 1 Wenzhou 44 cleaning Department SQL SELECT Count (ID) from E    MP GROUP by DEP; ORM: Object Relational Mapping Models.emp.objects.values ("DEP"). Annotate (C=count ("id")) # which field is grouped, the values who, and then Count any one field is good #####      ################################################### EMP ID name age dep_id 1            Alex 1 2 Egon 1 1 Wenzhou 44 2 DEP ID Name 1 Security Department 2 Cleaning Department check the number of people in each department Sql:sel ECT * FROM EMP INNER join DEP in emp.dep_id= dep.id ID name age dep_id D     Ep.id Dep.name 1 Alex 22 1 1 Security Department                   2 Egon 33 1 1 Security Department 1 Wenzhou 44 2 2 Cleaning Department SELECT * from EMP inner join DEP on EMP.DEP     _id= dep.id GROUP by Dep.id, Dep.name ORM: Key points: 1. Query object. Annotate () 2. Annotate GROUP by 3 according to the field of the previous select. Annotate () return value is still Queryset object, added after grouping statistics key value pair # First Join table base table is DEP, then get EMP inside get name data, cross table reverse write table name lowercase,, and queryset cross-table lookup data Same as Models.dep.objects.all (). Annotate (Count ("Emp__name")) models.dep.objects.values ("name"). Annotate (C=count ("emp _name ")). VALUES (" name "," C ") # Reverse query models.emp.objects.values (" Dep__name "). Annotate (" name "). VALUES (" De P_name "," C ")) # Forward query SQL Selcet dep.name, Count (Emp.name) as C from DEP inner join EMP on ....        ... group by Dep.name; Group queries have an outline first select Count (emp.name) as C, Dep.name FROM emp inner join DEP on .... GROUP BY Dep.name;     Select _____ from ___ inner join______ on ... group by _______________"""    #query the name of each author and the book of the highest price published      fromDjang.db.modelsImportSum, Count, Max, AVG ret= Models. Author.objects.values ("name"). Annotate (Max_price=max ("Book_price"). VALUES ("name", Max_price)"""Select Max (book.price) as Max_price from author inner join book_authors on .... INNER JOIN book on ... group by Author.name;"""    #Query the average price of books published by each publisher # you can press fields. VALUES ("") can also be directly. All ()Models. Publish.objects.all (). Annotate (Avg_price=avg ("Book__price"). VALUES ("name","Avg_price")    #Query the number of authors for each bookModes. Book.values ("title"). Annotate (C=count ("Authors__name"). VALUES ("title","C")    #query the name of each category and the number of articles that correspondmodels. Category.objects.all (). Annotate (c=count ("Articles__title"). VALUES ("title","C")    #count more than one author's book name    """sql: # First group and then count select Book.title, Count (Author.name) as C from book inner join Book-authors on .... Inner join author on .... GROUP BY Book    . ID having c >1; """    #The filter () is translated here into having meaning, and the former are translated into whereModels. Book.objects.all (). Annotate (C=count ("Authors__name"). Filter (c__gt=1). VALUES ("title","C")

10.5ORM Review (2)