SQL Main Table Paging Query association sub-table take any one efficient scheme

There is a business scenario where a single piece of data in the main table has multiple detail data in the child table. When the data is displayed, the product wants to be free to take a sub-table of data associated with the display, with a lot of solutions, but not good enough.

SQL query takes any one of the child tables, the scenario for multiple fields

Finally, an efficient scheme is recorded as follows:

Demand

First set of solutions:

Select Distincta.id,a.title,a.[description],a.xx1,a.xx2,a.xx3,b.id as Bid,b.xxa,b.xxb,b.xxc,b.xxdfrom Aleft join B on A.id=b.cgid

This scenario is generally not a problem, but if the field values of Table B are different, the results will still be duplicated.

The second scenario:

Using the grouping and aggregation functions max, Min this

But the personal feeling is too limited, not too right, do not paste the code.

The third scenario:

Used a lot of top 1

SelectA.id,a.title,a.[Description], a.xx1,a.xx2,a.xx3, (Select Top 1b.ID fromBwherea.ID=B.cgid) asBId, (Select Top 1B.xxa fromBwherea.ID=B.cgid) asXxA, (Select Top 1B.xxb fromBwherea.ID=B.cgid) asXxb, (Select Top 1B.xxc fromBwherea.ID=B.cgid) asXxC, (Select Top 1B.xxd fromBwherea.ID=B.cgid) asXxD fromA Left JoinB ona.ID=B.cgid

Because there are too many fields for a child table, this method is less efficient, but it is not really very low.

The fourth option, I think, is to look at the higher efficiency of the scheme:

Select *  from     (        SelectA.*,            (Select Top 1Id fromB asBwhereB.cgid=a.ID) asBid fromA asa) as Temp Left JoinB asB2 on Temp. bid=B2. Id

This method first links the primary table with the ID of one of the child table data as an "intermediate table", and then the "intermediate table" is connected to the child table.

Personally recommend this scheme, efficiency you can sit down and test.

The scheme is provided by a great God within the group.

SQL Main Table Paging Query association sub-table take any one efficient scheme