group by question

haigerrual

The use of group by does not seem to understand
If there is sum max avg. . . If there are aggregation parameters, there must be a group by
A big analogy. If you add up all the values ​​of a certain field, then if the other field is not the same as the system, it will not be recognized. You should add that column.

gaara1985

select TB. *, N.num1 from TB inner join (select a, b, sum (num) as num1 from TB group by a, b) as N On N.a = TB.a order by N.a

This idea may work, but this statement may be incorrect.

流浪的疾风

I'm afraid there aren't many good ways

jojo1224

Supportgaara1985, use the column to be grouped as a subquery and then join the original table, but not necessarily inner join, it may be left join or right join, depending on your needs

terrorlin

select b.C_CODE, b.C_NAME, SUBSTRing (b.F_CODE, P_NSTYLSTAR, P_NSTYLLEN) FA_CODE, b.F_NAME FA_NAME, b.F_PRICE FA_PRICE, a.NUM_1, a.SUM_1 from percur1 b inner join (select c_code, f_code, sum (num_1) num_1, sum (num_1 * f_price) sum_1 from percur1 group by c_code, f_code) a on b.c_code = a.c_code and b.f_code = a.f_code

Try this, but I don't know how fast it is. Ha ha

terrorlin

Finally, I missed an order by ....

lancelover

select a, b, sum (num) from table1 aaa group by a, b
union
select a, b, c from table1 bbb group by a, b

wgfxman

Algorithmically, all of the above

However, if there are only a few fields, I am afraid that moving 2 or 3 fields will not be too bad in terms of subquery efficiency. If this difference is large, you should split the table structure design (outside words ~)

Moreover, SUBSTRing (F_CODE, P_NSTYLSTAR, P_NSTYLLEN), I doubt whether F_CODE is the same if SUBSTRing (F_CODE, P_NSTYLSTAR, P_NSTYLLEN) is the same. If they are different, you need to consider filtering the results or the inner subquery is SUBSTRing (). . .

imapiggy

good question!
study ~~~

372162042

If you query a lot of information, the speed will be very slow. Grouping is mainly used in conjunction with aggregate functions. Group by followed by one or two groupings is fine. It is useless if it is more than what you want.
    The main reason is that it is too slow to query too much information. It is recommended that you add top (the number of rows to be queried) or top 20 percent to display the percentage when you query.
    If you have a lot of information, there is nothing you can do about it.