Shulou(Shulou.com)06/01 Report--

This article introduces you how to achieve MySQL library in the orderly enumeration of conditional grouping, the content is very detailed, interested friends can refer to, I hope to help you.

SQL has only equivalent grouping, enumeration grouping generally uses case when transition, but does not maintain the order, to preserve the order you have to join, such as written like this:

with T2(g,ord) as {

select 'firstGroup',1

union all select 'secondGroup',2

……

}

select T1.*

from T2 join

(select (case

when condition1 then 'firstGroup'

when condition2 then 'secondGroup'

…… end) g

, count(*) n

from A

group by g) T1

on T1.g=T2.g

order by T2.ord asc

And, even then, null group loss can still occur.

This situation is much more convenient to use SPL, one sentence:

=connect("mysqlDB").query("select * from A").enum([condition1,condition2,…]).new(["firstGroup","secondGroup",…](#):g, ~.len():n)

SPL supports ordered set operations very thoroughly. It can explicitly express the data set (including grouped subsets) in the operation process. In addition to enumerating groups in fixed order, it is also easy to implement overlapping groups. Refer to Simplified SQL Case: Fixed Groups.

When the data is not in the database, it is still convenient for SPL to perform complex calculations:

=file("d:/t.csv").import(;,",").enum...

On how to achieve the MySQL library in the orderly enumeration of conditional grouping to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.