SQL query to sort data from two tables (MySQL)

Table A
ID_A,Name_A

Table B
ID_B,Name_B,Gender,ID_A

I'd like to sort out all the data (ID_B, Name_B, Gender, ID_A, Name_A) from these two data tables, but the sorting condition is
①Name_BDESC,
②If there are multiple IDs_A, the order in which the number is higher.

When I use the count, I cannot get all the data,
I would appreciate your advice.

mysql

2022-09-30 20:25

1 Answers

I don't understand the correlation of the table and the results required, so this is an imaginary answer.

SELECT
  T1.id_b,
  T1.name_b,
  T1.gender,
  T1.id_a,
  T2.name_a,
  (SELECT count(*) FROM table_b T3 WHERE T3.name_b=T1.name_bAND T3.id_a=T1.id_a) AS my_count
from
  table_bAST1
LEFT JOIN
  table_a AST2USING(id_a)
ORDER BY
  T1.name_b DESC, my_count DESC;

I think it's better to do it like this

SELECT clauses that return rows and columns can be treated as values

2022-09-30 20:25

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656