Is there a better way to group display

zwmmks

The parent table has 50 fields, and the child table has 100 fields. Now it needs to be grouped and displayed by the "number" of the parent table, but it also needs to display a piece of corresponding information in the child table, that is, what is needed is:

select parent table.*, child table.* from parent table left join child table group by parent table. No.

As a result, because group by needs an aggregate function, the above writing method cannot be passed. Now I don’t want to be backward compatible, and I don’t want to write all the fields with aggregate functions. Is there a better way? ? ?

zwmmks

I thought of a way, which is to remove the duplicate rows of the "number" in the child table, and then join left with the parent table. At this time, there is no need for group by, because for the parent table, the "number" is the ID, which is unique .

How do I remove the duplicate rows in the sub-table? ?

Is this method feasible? ?

fanfan1980

--try

select parent table.* from parent table
left join
(
select number from subtable group by number
) Child table on parent table. Number = child table. Number

zwmmks

It is also necessary to display one record among the multiple matches in the sub-table

Parent table.*, child table.*

fenghui1231

The parent table is best to have a unique column or primary key

zwmmks

The "number" is unique in the parent table, and the child table is associated with the parent table through the "number"

Now it is required to display all the records in the parent table and one of the corresponding multiple records in the child table.
Because of the number association, the sub-table may have multiple records, now only one is required to be displayed, and this one is extracted at will

Don't understand what I mean? ? ?

E.g:
Parent table:
0601001 dd bb
0601002 cc wa
.....
Child table:
0601001 11 22
0601001 22 13
0601001 a3 d2
0601002 a2 sz
0601002 d2 d2
.....

Now the desired result is similar:
0601001 dd bb 11 22
0601002 cc wa d2 d2

The above is an example, the actual parent table has about 50 fields, and the child table has about 100 fields.

fenghui1231

Give you a reference
use pubs
go
select * from jobs j left join
(select * from employee where emp_id in
(select min(emp_id) from employee group by job_id)) e on j.job_id =e.job_id order by j.job_id