Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
344 views
in Technique[技术] by (71.8m points)

group by - how to groupBY using spring data

hi i'm using spring data in My project and I'm trying group by two fields, heres the request:

@Query( "SELECT obj from Agence obj GROUP BY obj.secteur.nomSecteur,obj.nomAgence" )
  Iterable<Agence> getSecteurAgenceByPc();

but it doesnt work for me..what i want is this result:

-Safi
  -CTM
    CZC1448YZN
    2UA13817KT

-Rabat
  -CTM
    CZC1349G1B
    2UA0490SVR
  -Agdal
    G3M4NOJ

-Essaouira
  -CTM
    CZC1221B85
  -Gare Routiere Municipale
    CZC145YL3

What I get is

{
    "status": 0,
    "data":
    [
        {
            "secteur": "Safi",
            "agence": "CTM"
        },
        {
            "secteur": "Safi",
            "agence": "Dep"
        },
        {
            "secteur": "Rabat",
            "agence": "Agdal"
        },
        {
            "secteur": "Rabat",
            "agence": "CTM"
        },
        {
            "secteur": "Essaouira",
            "agence": "CTM"
        },
        {
            "secteur": "Essaouira",
            "agence": "Gare Routiere Municipale"
        }
    ]
}
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

What you want is not possible with JPQL.

What does Group By do?

It combines all rows that are identical in the columns in the group by clause in to one row. Since it combines multiple rows into one, data in other columns can only be present in some combined fashion. For example, you can include MIN/MAX or AVG values, but never the orginal values.

Also the result with always be a table, never a tree.

Also note: there is no duplicated data. Every combination of secteur and agence appears exactly once.

If you want a tree structure, you have to write some java code for that.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...