there is a user table User (id, email), and a payment record table Transaction (id, transaction_type, transaction_amount) to find out the email and total payment amount of the three users with the highest total payment in each payment category
there is a user table User (id, email), and a payment record table Transaction (id, transaction_type, transaction_amount) to find out the email and total payment amount of the three users with the highest total payment in each payment category
using these two keywords should help you solve the problem: desc limit3;
SELECT user.id, user.email, SUM(transaction.transaction_amount) as total_payment
FROM User user LEFT JOIN Transaction transaction ON user.ID = transaction.user_id
GROUP BY transaction.transaction_type
order by total_payment desc limit 0,3idea, group according to the payment category, calculate the total payment of each user under each payment category, and take the first three
according to the descending order of the total payment. the specific sentence will not be written, let's talk about the train of thought.
mysql higher version, supports window functions, like this:
row_number over (partition by transaction_type order by amount desc)then according to the return value of the function < = 3, as a Filter condition
if it is not supported, mysql has @ variable, which is probably used as follows. You need to sort by transaction_type amount in sql to determine whether transaction_type is equal to the previous one, and reset the value of @ rownum. Finally, Filter is carried out according to the value of rank in the following statement.
search with the keyword "mysql row number" to find a complete example.
SELECT t.*,
@rownum := @rownum + 1 AS rank
FROM YOUR_TABLE t,
(SELECT @rownum := 0) rPrevious: When babel is compiled, it is prompted that the value of import cannot be read.
Next: Is it slow to install software center software on ubuntu 18?
subject table, which can be understood as a classification table. The course and source_book tables are associated with subject now use the subject table as the main table to query how many courses and books are under this category. query statemen...
background: 1. I am a java programmer 2. Recently interviewed for a senior development position 3. The previous project mainly used less oracle,mysql, and the companies interviewed basically used mysql s 4. I am weak in the database 5. Recently, I...
for example, if the database has a field type of enum ( axiomagementific database ), can you change this field to enum when there are millions of data in the database? will it be slow? ...
because batch update operations are always involved recently, I don t know if batch operations can be performed, so we can only update, one by one in the loop , so I d like to ask whether there is a sql statement in the sql database that enables updat...
jav inserts Mysql data exception Data too long exception information 2018-04-24 00:15:01 ERROR com.comall.cybershop.common.base.DaoSupport org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [update cs_awardCardRe...
the Buy, attribute of the data table is the product product, department department, time time, so how to write the sql statement "products purchased by the sales department in 2014 but not purchased by other departments ", I have been thinking about it ...
after entering the query statement, I kept turning and turning, but there was no response. Please see why http: www.nbjinshi.com bug.png. This is a screenshot. I couldn t upload the screenshot, so I had to upload it to my server ....
has a table, table_x, field A has a duplicate value: now you want to return the result of the sample diagram through the select query, counting the number of times the value of the corresponding field An is repeated. The ideal effect is shown in figur...
Name Class Score Zhang San Chinese 81 Zhang San Mathematics 75 Li Si Chinese 76 Li Si Mathematics 90 Wang Wu Chinese 81 Wang Wu Mathematics 100 Wang Wu English 90 use a SQL sentence to query the Name of the students with the lowest Chinese sco...
is there any way? such as recommended books, blogs, etc. ...
means there is no train of thought at all. if user An is only in the R & D group and id is 5, then the number after user An is 32 . How should I write this sentence? There is no external network. I don t know if I can see the pictures taken by pri...
(SELECT * FROM `dozhan_reply` t1 WHERE `reply_parent` IN ( SELECT `reply_id` FROM ( SELECT `reply_id` FROM `dozhan_reply` WHERE `reply_article` = 69 AND `reply_type` = 0 LIMIT 0, 5 ) t )) UNION (SEL...
SELECT * FROM `table_1` WHERE `reply_parent` IN (SELECT `reply_id` FROM (SELECT `reply_id` FROM `table_1` WHERE `reply_article`=69 AND `reply_type`=0 LIMIT 0, 5)AS t) UNION (SELECT * FR...
can you give me a column? ...
is like this, and the ultimate effect is to match the avatar address of the drink in the user table for each comment list first, query out the table_1 (article comment table) article comment result set then I found that these comments do not have...
there is a problem. What should I do if I want to take a record that matches multiple characters in a field? select * from actor where last_name like ( % DAVI% , MOSTEL% , ABC , kkk%aa ) the above sql statement is incorrect. Like does not sup...
these are the data of my two tables: flask: sqlalchemy: : ...
there seems to be no difference between the two SQL implementations. select * from A left join B on A.aid=B.aid where A.aid=100 select * from ( select * from A where A.aid=100 ) AA left join B on AA.aid=B.aid what do you think of SQL performing ...
is it feasible to delete and then insert when updating database tables? the contents of the table are many-to-many relationships ...
1. If you use select * from APowerB without any conditions, the result will be displayed associated with tables an and b, but the result will be repeated many times 2, and select * from a join b, will not add any query conditions, and the result will b...