the structure of the table is as follows:
id id
student_id course_id score
1 1 60
1 2 50
1 3 70
2 1 20
2 2 40
...
...without considering that the total score is the same, how to query the number of people who are higher than the total score of student_id=10, that is, their own ranking?
try this, okay?
-sharp table_name
SELECT COUNT(student_id) FROM table_name GROUP BY student_id HAVING SUM(score) > (SELECT SUM(score) FROM table_name WHERE student_id = 10); score:'''select score from table where student_id=10'''
:'''select count(1) as cnt from table where score>'{0}' '''.format(score)how does sql write from the database table to the display of the page? requirement: the first-level classification needs sorting, the first-level classification shows its subcategories, and the subcategories also need sorting. ...
there are two insert statements in the mysql trigger. How to implement the second inert with the result of the first insert DELIMITER $$ USE `nightclub`$$ DROP TRIGGER *!50032 IF EXISTS * `create_histroy_room_statuses`$$ CREATE *!50017 DEFIN...
ask how to write the sql statement ...
has always thought that index optimization is effective in where is useless in select but it seems that there is a performance difference between select nonclustered index columns, clustered index columns, unindexed columns and select *? ...
Table T_USER T_ROLE : the following is the table creation and data script drop table if exists T_USER; CREATE TABLE T_USER( USER_ID INT, USER_NAME VARCHAR(25) ); INSERT INTO T_USER VALUES(1, USER_A ); INSERT INTO T_USER VALUES(2, USER_B )...
in mysql, set a regular expression to filter strings ending in number select number from test where number REGEXP xxxxxxxxx . ...
A a_id a_title a_key a_desc a_pid 1 A A A 1 B b_id b_title b_key b_desc b_pid 1 b 2 C c_id c_title c_key c_desc c_pid 1 $title= Ctitile || ...
for stand-alone service, the primary key ID is a long integer, which needs to be non-continuous. For example, self-increment cannot be implemented because it is continuous. I thought of using System.nanoTime () to do ID, because I haven t practiced it...
query the COUNT of a sentence (1), JOIN IN has 4 tables. The total number of records is 3800, and it takes 43 seconds this is SQL: select count(1) from ( SELECT a.id AS "id", a.batch_id AS "batchId", a.product_type AS "product...
my mysql database has two pieces of data when I print_r, it looks like this: [{"errno":"1","errstr":"2","errfile":"3","errline":"4"},{"errno":"12",...
is currently self-learning mysql,. When querying, sometimes you need to use as aliases, but after adding as aliases to the following two query statements, why is it incorrect? 1, select cat_id,goods_name,shop_price from goods where shop_price in (sele...
as shown in the figure: the fields that reply_article is " 69 " and reply_type is " 0 " and reply_aims_user is null are required. then! Here s the point. Use the reply_id in the previous query field as the primary key to associate the ...
as shown in the figure, this association query is different in that it is associated with the same table indicates that table_test requirement: first query the result set of the field whose reply_type is 0, and then use the reply_id of the resul...
Which method of HIbernate is more appropriate to use in the SQL statement select name,sex from db, for example: public T findByID (ID id, I dbName) throws Exception; public List<T> findAll(I dbName) throws Exception; public List<T> findBy...
how to realize the if judgment of mysql. There is now a table with two key fields SELECT * FROM house WHERE price= 8500 OR shoufu= 30 I think, when the $price passed in by php is empty, the sql search statement is SELECT * FROM house WHERE s...
problem description: a blog system is being developed that needs to achieve the top of an article, and then the foreground will display the article (or more than one) at the top. What I want is to add a new field to determine whether it is a top article...
Table 1 fields are as follows 2 3 1event_description_description_id2id1cel_order_item_order_item_id3id 123idnull id ask the boss for advice ...
I have found many articles on utf8_unicode_ci, utf8_general_ci and utf8_bin collation on the Internet, and I generally know that ci is case ignore, etc., but I didn t mention what other collations mean . what does the utf8mb4_polish_ci collation her...
uses union all to merge query results when doing a comprehensive statistical function, but the result set obtained by the query is found to be overwritten sql: SELECT studyCenterName, grade, weiji, done, zong FROM ( ...
for example, I like % R% answer, the query results include not only R, but also r, which is not what I want, I want to be case-sensitive. I guess it has something to do with the proofreading rules of the table. My table uses utf8mb4 encoding, and the pr...