精华内容
下载资源
问答
  • when表达式

    2021-05-22 11:49:34
    ### when条件语句when表达式类似于 switch-case 表达式。when取代了类 C 语言的 switch 操作符,when会对所有的分支进行检查直到有一个条件满足。但相比switch而言,when语句要更加的强大,灵活。* 给定条件* 满足...

    ### when条件语句

    when表达式类似于 switch-case 表达式。when取代了类 C 语言的 switch 操作符,when会对所有的分支进行检查直到有一个条件满足。但相比switch而言,when语句要更加的强大,灵活。

    * 给定条件

    * 满足什么条件执行什么任务

    **示例如下**

    ```

    package B流控语句

    fun main(args: Array) {

    var x = 0

    when (x) {

    //是否是Int类型

    is Int -> println("${x}是Int类型")

    //是否是0或者1

    0, 1 -> println("${x}不是0,就是1")

    //是否在[0,10]区间

    in 0..10 -> println("${x}在[0,10]")

    //是否不在[0,10]区间

    !in 0..10 -> println("${x}不在[0,10]区间")

    //是否是1

    1 -> println("${x}是1")

    //是否是2

    2 -> println("${x}是2")

    //如果都不满足

    else -> println("所有情况都不满足")

    }

    testWhen(1)

    }

    fun testWhen(obj: Any) {

    when (obj) {

    1 -> print("第一项")

    "hello" -> print("这个是字符串hello")

    is Long -> print("这是一个Long类型数据")

    is Int -> print("这是一个Int类型数据")

    !is String -> print("这不是String类型的数据")

    else -> print("else类似于Java中的default")

    }

    }

    ```

    运行结果

    ```

    0是Int类型

    第一项

    Process finished with exit code 0

    ```

    * when 将它的参数与所有的分支条件顺序比较,直到某个分支满足条件。

    * when 既可以被当做表达式使用也可以被当做语句使用。

    * 如果它被当做表达式, 符合条件的分支的值就是整个表达式的值,

    * 如果当做语句使用, 则忽略个别分支的值。(像 if 一样,每一个分支可以是一个代码块,它的值是块中最后的表达式的值。)

    * 如果其他分支都不满足条件将会求值 else 分支。 **如果 when 作为一个表达式使用,则必须有 else 分支, 除非编译器能够检测出所有的可能情况都已经覆盖了**[例如,对于 [枚举(enum)类](http://www.kotlincn.net/docs/reference/enum-classes.html)条目与[密封(sealed)类](http://www.kotlincn.net/docs/reference/sealed-classes.html)子类型]。

    * 如果很多分支需要用相同的方式处理,则可以把多个分支条件放在一起,用逗号分隔:

    ```

    when (x) {

    0, 1 -> print("x == 0 or x == 1")

    else -> print("otherwise")

    }

    ```

    * 我们可以用任意表达式(而不只是常量)作为分支条件

    ```

    when (x) {

    parseInt(s) -> print("s encodes x")

    else -> print("s does not encode x")

    }

    ```

    * 我们也可以检测一个值在(in)或者不在(!in)一个区间或者集合中:

    ```

    when (x) {

    in 1..10 -> print("x is in the range")

    in validNumbers -> print("x is valid")

    !in 10..20 -> print("x is outside the range")

    else -> print("none of the above")

    }

    ```

    * 另一种可能性是检测一个值是(*is*)或者不是(*!is*)一个特定类型的值。

    >[info]注意: 由于[智能转换](http://www.kotlincn.net/docs/reference/typecasts.html#智能转换),你可以访问该类型的方法与属性而无需任何额外的检测。

    ```

    fun hasPrefix(x: Any) = when(x) {

    is String -> x.startsWith("prefix")

    else -> false

    }

    ```

    * when也可以用来取代`if-else` if 链。 如果不提供参数,所有的分支条件都是简单的布尔表达式,而当一个分支的条件为真时则执行该分支:

    ```

    when {

    x.isOdd() -> print("x is odd")

    x.isEven() -> print("x is even")

    else -> print("x is funny")

    }

    ```

    示例

    ```

    fun main(args: Array) {

    var a: Int = 7

    var b: Int = 8

    when {

    a > b -> print("a大于b")

    a < b -> print("a小于b")

    else -> print("a等于b")

    }

    }

    ```

    运行结果

    ```

    a小于b

    ```

    * 自 Kotlin 1.3 以来, 可以使用以下语法捕获变量中的主题,变量的范围, 在主体时引入, 仅限于主体时。

    ```

    fun Request.getBody() =

    when (val response = executeRequest()) {

    is Success -> response.body

    is HttpError -> throw HttpException(response.status)

    }

    ```

    展开全文
  • CREATE OR REPLACE VIEW V_GAL_IMP_YB_B0001_0724 AS SELECT id,nd,mo,km_code,km_name,ylj, sum(ylj) over(partition by nd,id order by mo) nlj from (select A.ID,a.nd,a.MO,a.KM_CODE,a.KM_NAME, (case WHEN A....

    CREATE OR REPLACE VIEW V_GAL_IMP_YB_B0001_0724 AS

    SELECT id,nd,mo,km_code,km_name,ylj, sum(ylj) over(partition by nd,id order by mo) nlj from (select A.ID,a.nd,a.MO,a.KM_CODE,a.KM_NAME,

    (case

    WHEN A.ID='0001' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE (S.KMDM like '101%' OR S.KMDM like '103%' ) AND KMDM NOT like '10301%' AND KMDM NOT like '10306%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0002' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '101%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0003' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE (S.KMDM like '1010101%' OR S.KMDM like '1010102%' ) and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0004' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010101%' and s.nd=a.ND and s.mo=a.MO )

    ---

    WHEN A.ID='0014' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE (S.KMDM LIKE '101010150%' OR

    KMDM LIKE '101010122%' OR

    KMDM LIKE '101010125%' OR

    KMDM LIKE '101010126%' OR

    KMDM LIKE '101010127%' OR

    KMDM LIKE '101010128%' OR

    KMDM LIKE '101010129%') and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0018' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010201%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0021' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010104%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0023' THEN (SELECT SUM (YLJ) FROM V_SSSR_BB001 S WHERE (S.KMDM LIKE '1010102%' OR S.KMDM LIKE '1010202%') AND S.nd=A.ND AND S.MO

    =A.MO )

    WHEN A.ID='0024' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010102%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0025' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010202%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0028' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE (S.KMDM like '1010103%' OR S.KMDM like '1010203%') and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0029' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010203%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0030' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010103%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0033' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010105%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0034' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE (S.KMDM like '1010301%' OR S.KMDM like '1010302%' OR S.KMDM like '1010303%' OR S.KMDM like '1010304%' OR S.KMDM like '1010305%' OR S.KMDM like '1010320%' OR S.KMDM like '1010329%')

    and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0035' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010301%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0036' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010302%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0037' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010303%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0038' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010304%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0039' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010305%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0040' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010306%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0041' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010320%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0042' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010329%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0043' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE (S.KMDM like '10104%' AND S.KMDM NOT like '1010443%' AND S.KMDM NOT like '1010443%' ) and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0044' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE (KMDM LIKE '1010401%' OR KMDM LIKE'1010402%' OR KMDM LIKE'1010403%' OR KMDM LIKE'1010404%' OR KMDM LIKE'1010405%' OR KMDM LIKE'1010406%' OR KMDM LIKE'1010407%' OR KMDM LIKE'1010408%' OR KMDM LIKE'1010409%' OR KMDM LIKE'1010410%' OR KMDM LIKE'1010411%' OR KMDM LIKE'1010412%' OR KMDM LIKE'1010413%' OR KMDM LIKE'1010414%' OR KMDM LIKE'1010415%' OR KMDM LIKE'1010416%')

    and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0045' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010417%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0047' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010418%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0048' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010419%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0049' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010420%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0050' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010421%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0051' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010422%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0052' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010423%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0053' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010424%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0054' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010425%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0055' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010426%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0056' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010427%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0057' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010428%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0058' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010429%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0059' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010430%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0060' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010431%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0061' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010432%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0062' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010433%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0063' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010434%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0064' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010435%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0065' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010436%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0066' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010439%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0067' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010440%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0068' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010441%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0069' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010442%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0070' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010443%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0071' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010444%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0072' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010445%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0073' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010446%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0074' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010447%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0075' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010449%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0076' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010450%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0077' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10105%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0078' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE (KMDM LIKE '1010517%' OR KMDM LIKE'1010501%' OR KMDM LIKE'1010502%' OR KMDM LIKE'1010503%' OR KMDM LIKE'1010504%' OR KMDM LIKE'1010505%' OR KMDM LIKE'1010506%' OR KMDM LIKE'1010507%' OR KMDM LIKE'1010508%' OR KMDM LIKE'1010509%' OR KMDM LIKE'1010510%' OR KMDM LIKE'1010511%' OR KMDM LIKE'1010512%' OR KMDM LIKE'1010413%' OR KMDM LIKE'1010514%' OR KMDM LIKE'1010515%' OR KMDM LIKE'1010416%') and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0079' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010517%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0080' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010518%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0081' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010519%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0082' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010520%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0083' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010521%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0084' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010522%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0085' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010523%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0086' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010524%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0087' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010525%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0088' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010526%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0089' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010527%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0090' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010528%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0091' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010529%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0092' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010530%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0093' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010531%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0094' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010532%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0095' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010533%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0096' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010534%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0097' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010535%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0098' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010536%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0099' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010599%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0100' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10106%' and s.nd=a.ND and s.mo=a.MO )

    ----

    WHEN A.ID='0102' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10107%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0103' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10109%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0104' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010918%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0105' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010921%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0106' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1010922%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0107' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10110%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0108' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10111%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0109' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1011101%' and s.nd=a.ND and s.mo=a.MO )

    ---

    WHEN A.ID='0110' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10112%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0111' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10113%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0112' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10114%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0113' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10115%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0114' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10116%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0115' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10117%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0116' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10118%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0117' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10119%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0118' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10120%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0119' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10199%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0120' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103%' AND S.KMDM NOT like '10301%' AND S.KMDM NOT LIKE '10306%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0121' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10302%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0122' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030201%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0123' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030202%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0125' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030203%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0129' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030205%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0130' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030210%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0131' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030211%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0132' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030212%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0133' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030213%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0134' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030215%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0138' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030299%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0141' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10304%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0142' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030401%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0143' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030402%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0144' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030403%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0145' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030405%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0146' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030408%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0147' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030411%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0148' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030416%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0149' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030424%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0150' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030427%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0151' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030432%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0152' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030433%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0153' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030435%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0154' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030444%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0155' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030446%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0156' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030447%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0157' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030449%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0158' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030450%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0159' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030451%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0160' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030452%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0161' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030453%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0162' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE (KMDM like '1030404%' OR KMDM like '1030406%' OR KMDM like '1030407%' OR KMDM like '1030409%' OR KMDM like '1030410%' OR KMDM like '1030412%' OR KMDM like '1030413%' OR KMDM like '1030414%' OR KMDM like '1030415%' OR KMDM like '1030417%' OR KMDM like '1030418%' OR KMDM like '1030419%' OR KMDM like '1030420%' OR KMDM like '1030421%' OR KMDM like '1030422%' OR KMDM like '1030423%' OR KMDM like '1030425%' OR KMDM like '1030426%' OR KMDM like '1030428%' OR KMDM like '1030429%' OR KMDM like '1030430%' OR KMDM like '1030431%' OR KMDM like '1030434%' OR KMDM like '1030436%' OR KMDM like '1030437%' OR KMDM like '1030438%' OR KMDM like '1030439%' OR KMDM like '1030440%' OR KMDM like '1030441%' OR KMDM like '1030442%' OR KMDM like '1030443%' OR KMDM like '1030445%' OR KMDM like '1030448%' OR KMDM like '1030454%' OR KMDM like '1030455%' OR KMDM like '1030456%' OR KMDM like '1030457%' OR KMDM like '1030458%' OR KMDM like '1030459%' OR KMDM like '1030460%' OR KMDM like '1030461%' OR KMDM like '1030462%' OR KMDM like '1030488%' OR KMDM like '1030499%') and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0163' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10305%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0164' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030501%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0178' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE (KMDM LIKE '103050105%' OR KMDM LIKE '103050112%' OR KMDM LIKE '103050113%' OR KMDM LIKE '103050117%' OR KMDM LIKE '103050118%' OR KMDM LIKE '103050119%' OR KMDM LIKE '103050120%' OR KMDM LIKE '103050121%' OR KMDM LIKE '103050122%'OR KMDM LIKE '103050199%') and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0179' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030502%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0180' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030503%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0181' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030509%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID BETWEEN '0182' AND '0199' THEN 0

    ----

    WHEN A.ID='0200' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10307%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0201' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030701%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0202' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030702%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0203' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030703%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0204' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030704%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0205' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030705%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0206' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103070501%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0207' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103070502%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0208' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103070503%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0209' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM LIKE '103070599%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0210' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030706%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0211' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030707%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0212' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030708%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0213' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030799%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0214' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10399%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0215' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1039901%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0216' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103990101%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0217' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103990102%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0218' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103990103%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0219' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1039902%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0220' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1039903%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0221' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1039904%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0222' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1039905%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0223' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1039906%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0224' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1039907%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0225' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1039908%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0226' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1039909%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0227' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103990901%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0228' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103990902%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0229' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1039911%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0230' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1039912%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0231' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1039913%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0232' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1039960%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0233' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE ( KMDM like '1039999%' OR KMDM like '1039998%') and s.nd=a.ND and s.mo=a.MO )

    ------

    WHEN A.ID='0234' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10301%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0235' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030102%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0236' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030103%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0237' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030106%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0238' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030110%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0239' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030112%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0240' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030114%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0241' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030115%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0242' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030118%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0243' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030119%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0244' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030121%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0245' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030126%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0246' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030127%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0247' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030129%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0248' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030131%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0249' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030133%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0250' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030135%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0251' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030136%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0252' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030137%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0253' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030138%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0254' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030139%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0255' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030142%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0256' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030143%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0257' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103014301%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0258' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103014302%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0259' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103014303%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0260' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103014304%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0261' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103014305%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0262' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103014399%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0263' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030144%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0264' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030146%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0265' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030147%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0266' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030148%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0267' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103014801%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0268' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103014802%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0269' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103014803%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0270' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103014804%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0271' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103014805%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0272' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103014898%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0273' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103014899%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0274' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030149%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0275' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030150%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0276' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030152%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0277' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030153%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0278' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030154%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0279' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030155%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0280' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103015501%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0281' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103015502%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0282' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030156%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0283' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030157%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0284' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030158%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0285' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103015801%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0286' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103015802%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0287' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103015803%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0288' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030159%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0289' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030160%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0290' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030166%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0291' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030168%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0292' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030170%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0293' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030171%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0294' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030172%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0295' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030173%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0296' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030174%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0297' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030175%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0298' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030199%' and s.nd=a.ND and s.mo=a.MO )

    ------

    WHEN A.ID='0299' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10306%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0300' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030601%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0301' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060102%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0302' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060103%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0303' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060104%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0304' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060105%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0305' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060106%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0306' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060107%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0307' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060108%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0308' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060109%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0309' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060112%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0310' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060113%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0311' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060114%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0312' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060115%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0313' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060116%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0314' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060117%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0315' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060118%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0316' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060119%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0317' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060120%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0318' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060121%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0319' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060122%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0320' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060123%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0321' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060124%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0322' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060125%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0323' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060126%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0324' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060127%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0325' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060128%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0326' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060129%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0327' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060130%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0328' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060131%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0329' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060132%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0330' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060133%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0331' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060198%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0332' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030602%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0333' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060202%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0334' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060203%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0335' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060298%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0336' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030603%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0337' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060301%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0338' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060304%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0339' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060305%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0340' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060306%' and s.nd=a.ND and s.mo=a.MO )

    ---0341 修改~~~

    WHEN A.ID='0341' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060398%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0342' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030604%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0343' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060401%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0344' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060402%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0345' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '103060498%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0346' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '1030698%' and s.nd=a.ND and s.mo=a.MO )

    ----

    WHEN A.ID='0347' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '105%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0348' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10501%' and s.nd=a.ND and s.mo=a.MO )

    WHEN A.ID='0349' THEN (SELECT SUM(YLJ) FROM V_SSSR_BB001 S WHERE S.KMDM like '10502%' and s.nd=a.ND and s.mo=a.MO )

    ELSE NVL(SUM(NVL(YLJ, 0)),0)

    END) YLJ

    from (SELECT A.ID,A.ND,A.MO, A.KM_CODE, A.KM_NAME,

    NVL(SUM(NVL(B.YLJ, 0)),0) YLJ

    FROM GAL_IMP_YB_2013_KM A

    LEFT JOIN V_SSSR_BB001 B ON A.KM_CODE = B.KMDM AND A.ND = B.nd AND A.MO = B.mo

    GROUP BY A.ND, A.MO, A.ID, A.KM_CODE, A.KM_NAME) A

    GROUP BY A.ID,A.ND,A.MO,A.KM_CODE,A.KM_NAME);

    展开全文
  • 例子1、最近支持同事做报表,特提供数据源的sql脚本,当中就用到了case when。主要的视图结构如下:create or replace view v_card_channel_count5(icpcode,sumarea,count1201,count1202,count1203,count1204,count...

    例子1、最近支持同事做报表,特提供数据源的sql脚本,当中就用到了case when。主要的视图结构如下:

    create or replace view v_card_channel_count5

    (

    icpcode,sumarea,count1201,count1202,count1203,count1204,count1205,

    count1206,count1207,count1208,count1209,count1210,count1211,count1212,

    count1213,count1214,count1215,count1216,count1217,count1218,count1219,count1220,

    count1221,count1222,count1223,count1224,count1225,count1226,count1227,count1228,

    count1229,count1230,count1231)

    as

    select icpcode,'当期订购数',

    sum(case when to_char(subscribe_time,'dd')='01' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='02' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='03' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='04' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='05' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='06' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='07' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='08' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='09' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='10' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='11' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='12' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='13' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='14' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='15' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='16' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='17' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='18' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='19' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='20' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='21' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='22' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='23' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='24' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='25' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='26' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='27' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='28' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='29' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='30' then subscribe_num else 0 end),

    sum(case when to_char(subscribe_time,'dd')='31' then subscribe_num else 0 end)

    from temp_card_service_subscribe

    where to_char(subscribe_time,'yyyymm')='200612'

    group by icpcode

    union all

    select icpcode,'当期资源总数',

    count(distinct case when to_char(subscribe_time,'dd')='01' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='02' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='03' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='04' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='05' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='06' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='07' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='08' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='09' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='10' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='11' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='12' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='13' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='14' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='15' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='16' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='17' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='18' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='19' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='20' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='21' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='22' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='23' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='24' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='25' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='26' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='27' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='28' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='29' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='30' then contentid else null end),

    count(distinct case when to_char(subscribe_time,'dd')='31' then contentid else null end)

    from temp_card_service_subscribe

    where to_char(subscribe_time,'yyyymm')='200612'

    group by icpcode;

    另外一个例子是优化sql语句的,见如下:

    (原sql语句)

    select f.subject_id from subject_data_common f

    where validat ='A' and template_id = 1

    and f.miscid in ('999')

    and f.subject_id in (select subject_id

    from ( select -1 as subject_id from dual

    union all

    select subject_id from subject_data where field_id= 3 and field_value in ('wu')

    union all

    select subject_id from subject_data where field_id = 4 and field_value in ('money')

    union all

    select subject_id from subject_data where field_id =7 and field_value in ('1')

    union all

    select subject_id from subject_data_common

    where instr(',' || keyword || ',',',财富彩票投资,') > 0

    )

    group by subject_id

    having count(subject_id) >= 4

    )

    order by f.createdate asc;

    (从减少表遍历和利用索引上考虑如下改进的sql语句)

    select f.subject_id from subject_data_common f,(select subject_id

    from ( select -1 as subject_id from dual

    union all

    select case

    when field_id= 3 and field_value in ('wu') then subject_id

    when field_id= 4 and field_value in ('money') then subject_id

    when field_id= 7 and field_value in ('1') then subject_id

    else null

    end

    from subject_data

    union all

    select subject_id from subject_data_common

    where instr(',' || keyword || ',',',财富彩票投资,') > 0

    )

    group by subject_id

    having count(subject_id) >= 4) e

    where validat ='A' and template_id = 1

    and f.miscid in ('999')

    and f.subject_id = e.subject_id

    order by f.createdate asc;[@more@]

    展开全文
  • 大家好,我在写一个片区统计的报表程序,但总觉得使用sum((case when...这种语句在性能上不是太好,请大家出一下主意。语句如下:selectSUM((CASE When pq in ('p01') and test1 in('001') Then 1 Else 0 End)) into ...

    大家好,我在写一个片区统计的报表程序,但总觉得使用sum((case when...这种语句在性能上不是太好,请大家出一下主意。

    语句如下:

    select

    SUM((CASE When pq in ('p01') and test1 in('001') Then 1 Else 0 End)) into p01    ,

    SUM((CASE When pq in ('p01') and test1 in('002') Then 1 Else 0 End)) into p02    ,

    SUM((CASE When pq in ('p01') and test1 in('003') Then 1 Else 0 End)) into p03    ,

    SUM((CASE When pq in ('p01') and test1 in('004') Then 1 Else 0 End)) into p04    ,

    SUM((CASE When pq in ('p01') and test1 in('005') Then 1 Else 0 End)) into p05    ,

    SUM((CASE When pq in ('p01') and test1 in('006') Then 1 Else 0 End)) into p06    ,

    SUM((CASE When pq in ('p01') and test1 in('007') Then 1 Else 0 End)) into p07    ,

    SUM((CASE When pq in ('p01') and test1 in('008') Then 1 Else 0 End)) into p08    ,

    SUM((CASE When pq in ('p01') and test1 in('009') Then 1 Else 0 End)) into p09    ,

    SUM((CASE When pq in ('p01') and test1 in('010') Then 1 Else 0 End)) into p10    ,

    SUM((CASE When pq in ('p01') and test1 in('012') Then 1 Else 0 End)) into p11    ,

    SUM((CASE When pq in ('p01') and test1 in('013') Then 1 Else 0 End)) into p12    ,

    SUM((CASE When pq in ('p01') and test1 in('014') Then 1 Else 0 End)) into p13    ,

    SUM((CASE When pq in ('p01') and test1 in('015') Then 1 Else 0 End)) into p14    ,

    SUM((CASE When pq in ('p01') and test1 in('016') Then 1 Else 0 End)) into p15    ,

    ,

    SUM((CASE When pq in ('p02') and test1 in('001') Then 1 Else 0 End)) into pz01   ,

    SUM((CASE When pq in ('p02') and test1 in('002') Then 1 Else 0 End)) into pz02   ,

    SUM((CASE When pq in ('p02') and test1 in('003') Then 1 Else 0 End)) into pz03   ,

    SUM((CASE When pq in ('p02') and test1 in('004') Then 1 Else 0 End)) into pz04   ,

    SUM((CASE When pq in ('p02') and test1 in('005') Then 1 Else 0 End)) into pz05   ,

    SUM((CASE When pq in ('p02') and test1 in('006') Then 1 Else 0 End)) into pz06   ,

    SUM((CASE When pq in ('p02') and test1 in('007') Then 1 Else 0 End)) into pz07   ,

    SUM((CASE When pq in ('p02') and test1 in('008') Then 1 Else 0 End)) into pz08   ,

    SUM((CASE When pq in ('p02') and test1 in('009') Then 1 Else 0 End)) into pz09   ,

    SUM((CASE When pq in ('p02') and test1 in('010') Then 1 Else 0 End)) into pz10   ,

    SUM((CASE When pq in ('p02') and test1 in('012') Then 1 Else 0 End)) into pz11   ,

    SUM((CASE When pq in ('p02') and test1 in('013') Then 1 Else 0 End)) into pz12   ,

    SUM((CASE When pq in ('p02') and test1 in('014') Then 1 Else 0 End)) into pz13   ,

    SUM((CASE When pq in ('p02') and test1 in('015') Then 1 Else 0 End)) into pz14   ,

    SUM((CASE When pq in ('p02') and test1 in('016') Then 1 Else 0 End)) into pz15   ,

    ,

    ,

    SUM((CASE When pq in ('p03') and test1 in('001') Then 1 Else 0 End)) into pz01a  ,

    SUM((CASE When pq in ('p03') and test1 in('002') Then 1 Else 0 End)) into pz02a  ,

    SUM((CASE When pq in ('p03') and test1 in('003') Then 1 Else 0 End)) into pz03a  ,

    SUM((CASE When pq in ('p03') and test1 in('004') Then 1 Else 0 End)) into pz04a  ,

    SUM((CASE When pq in ('p03') and test1 in('005') Then 1 Else 0 End)) into pz05a  ,

    SUM((CASE When pq in ('p03') and test1 in('006') Then 1 Else 0 End)) into pz06a  ,

    SUM((CASE When pq in ('p03') and test1 in('007') Then 1 Else 0 End)) into pz07a  ,

    SUM((CASE When pq in ('p03') and test1 in('008') Then 1 Else 0 End)) into pz08a  ,

    SUM((CASE When pq in ('p03') and test1 in('009') Then 1 Else 0 End)) into pz09a  ,

    SUM((CASE When pq in ('p03') and test1 in('010') Then 1 Else 0 End)) into pz10a  ,

    SUM((CASE When pq in ('p03') and test1 in('012') Then 1 Else 0 End)) into pz11a  ,

    SUM((CASE When pq in ('p03') and test1 in('013') Then 1 Else 0 End)) into pz12a  ,

    SUM((CASE When pq in ('p03') and test1 in('014') Then 1 Else 0 End)) into pz13a  ,

    SUM((CASE When pq in ('p03') and test1 in('015') Then 1 Else 0 End)) into pz14a  ,

    SUM((CASE When pq in ('p03') and test1 in('016') Then 1 Else 0 End)) into pz15a  ,

    ,

    ,

    SUM((CASE When pq in ('p04') and test1 in('001') Then 1 Else 0 End)) into pz01ab ,

    SUM((CASE When pq in ('p04') and test1 in('002') Then 1 Else 0 End)) into pz02ab ,

    SUM((CASE When pq in ('p04') and test1 in('003') Then 1 Else 0 End)) into pz03ab ,

    SUM((CASE When pq in ('p04') and test1 in('004') Then 1 Else 0 End)) into pz04ab ,

    SUM((CASE When pq in ('p04') and test1 in('005') Then 1 Else 0 End)) into pz05ab ,

    SUM((CASE When pq in ('p04') and test1 in('006') Then 1 Else 0 End)) into pz06ab ,

    SUM((CASE When pq in ('p04') and test1 in('007') Then 1 Else 0 End)) into pz07ab ,

    SUM((CASE When pq in ('p04') and test1 in('008') Then 1 Else 0 End)) into pz08ab ,

    SUM((CASE When pq in ('p04') and test1 in('009') Then 1 Else 0 End)) into pz09ab ,

    SUM((CASE When pq in ('p04') and test1 in('010') Then 1 Else 0 End)) into pz10ab ,

    SUM((CASE When pq in ('p04') and test1 in('012') Then 1 Else 0 End)) into pz11ab ,

    SUM((CASE When pq in ('p04') and test1 in('013') Then 1 Else 0 End)) into pz12ab ,

    SUM((CASE When pq in ('p04') and test1 in('014') Then 1 Else 0 End)) into pz13ab ,

    SUM((CASE When pq in ('p04') and test1 in('015') Then 1 Else 0 End)) into pz14ab ,

    SUM((CASE When pq in ('p04') and test1 in('016') Then 1 Else 0 End)) into pz15ab ,

    ...

    from test

    展开全文
  • case when

    千次阅读 2020-12-22 14:13:07
    这篇文章在借鉴前人的基础上,进行了验证,感谢前人的分享一、case when的使用方法Case具有两种格式。简单Case函数和Case搜索函数。第一种 格式 :简单Case函数 :格式说明case 列名when 条件值1 then 选项1when 条件...
  • case when 怎么过滤null

    2021-05-06 02:17:33
    INSERT INTO DAPP.TB_MRT_SEC_BLL_REVSELECT CITY_CODE,''AL002'',CASE WHEN ROAM_TYPE_CODE=''2'' and system_type=''G'' THEN ''12010201''WHEN ROAM_TYPE_CODE=''3'' and system_type=''G'' THEN ''12010202''WHE...
  • when() - jquery when()方法

    2021-08-07 05:33:55
    如果单一延迟传递给jQuery.when,它是通过这个方法和延迟对象附加的其他方法可访问绑定的回调函数返回的,如defered.then。当延迟得到解决或者拒绝,通常的代码创建了原来的延迟,适当的回调将被调用。例如,jqXHR...
  • SQL中case when then用法

    2021-02-07 21:20:30
    如果现在希望将上表中各种性别的人数进行统计,sql语句如下: SQL> select 2 sum(case u.sex when 1 then 1 else 0 end)男性, 3 sum(case u.sex when 2 then 1 else 0 end)女性, 4 sum(case when u.sex <>1 and u....
  • SQL之CASE WHEN用法详解

    2021-02-09 01:30:01
    简单CASE WHEN函数:CASE SCORE WHEN 'A' THEN '优' ELSE '不及格' ENDCASE SCORE WHEN 'B' THEN '良' ELSE '不及格' ENDCASE SCORE WHEN 'C' THEN '中' ELSE '不及格' END等同于,使用CASE WHEN条件表达式函数实现...
  • dplyr提供的case_when()函数简化多个if_else语句,如果不匹配返回NA。结合mutate函数可基于已有变量创建新的变量。本文通过几个示例进行对比学习。 语法: library(dplyr) # df %>% # mutate(new_var = case_...
  • CASEWHEN 条件1 THEN 结果1WHEN 条件2 THEN 结果2WHEN 条件3 THEN 结果3WHEN 条件4 THEN 结果4.........WHEN 条件N THEN 结果NELSE 结果XENDCase具有两种格式。简单Case函数和Case搜索函数。--简单Case函数CASE sex...
  • mysql case when then 使用

    2021-01-18 19:37:27
    建表:create table hank (id int,name varchar(20));插入数据:insert into hank values(1,'A');insert into hank values(2,'B');...通过case when then更新数据:update hank set name = case idwhen...
  • sql之case when用法详解

    千次阅读 2021-03-09 17:33:32
    简单CASE WHEN函数只能应对一些简单的业务场景,而CASE WHEN条件表达式的写法则更加灵活。CASE WHEN条件表达式函数:类似JAVA中的IF ELSE语句。简单CASE WHEN函数:CASE SCORE WHEN 'A' THEN '优' ELSE '不及格' END...
  • 本帖最后由 Cynthia5 于 2014-10-8 20:35 编辑case when 中是否可以在嵌套一个case when ?例如:取对应的美食类型代码(美食类型类型代码='001' 为蔬菜,美食类型类型代码='002' 则为海鲜,美食类型类型代码='003' 则...
  • CASEWHEN 条件1THEN 结果1WHEN 条件2THEN 结果2WHEN 条件3THEN 结果3WHEN 条件4THEN 结果4.........WHEN 条件NTHEN 结果NELSE结果XENDCase具有两种格式。简单Case函数和Case搜索函数。--简单Case函数CASE sexWHEN '1...
  • 1. 前言 今天在面试三七互娱的时候两个面试官轮流上阵,那个时候...害,这么说来延迟消息的应该也就是一样加入到消息队列,只是标识了执行的时机,使用when来标识。那么岂不是面试官当时连着这两个问题是在做提示?麻了
  • select substr(unitcode,0,6),count(*),--01--sum(case substr(jqxzcode,0,4) when '0105' then 1 else 0 end) as sharen,sum(case substr(jqxzcode,0,4) when '0109' then 1 else 0 end) as qiangjie,sum(case subs...
  • sql语句之case when的用法

    千次阅读 2021-01-27 17:24:40
    当需要从数据源上直接判断数据显示带不动含义的时候,就可以在SQL语句中使用case when 函数。 语法如下: Case具有两种格式。简单Case函数和Case搜索函数。 简单Case函数格式: CASE 列名 WHEN 条件值1 THEN 选项1 ...
  • group by和case when结合使用 如下示代码块,group by 按case when得到的列分组计算,且select时,select 中的case when 与group by中保持一致。 select case when substr(upper(depend_name),1,4)='JCW_' then ...
  • CASE WHEN THEN

    2021-03-06 03:21:58
    SELECT(CASEWHEN News_Data.Type = 1 THEN (SELECT Sys_Department.Name FROM Sys_Department WHERE Sys_Department.Id = News_Data.ObjectId)WHEN News_Data.Type = 2 THEN (SELECT Basic_Community.Name FROM B...
  • SUM统计使用CASE WHEN

    2021-05-08 14:20:25
    SUM统计使用CASE WHEN如下建立表结构,createtabletest(idintnotnullAUTO_INCREMENT,typeintnotnull,valueintnotnull,primarykey(id));INSERTINTOtest(type,value)VALUES(1,1),(2,2),(3,3),(4,4);根据 根据type的值...
  • 介绍mysql数据库中case when语句的用法,首先介绍case when语句的基础知识,然后提供了相关例子。比较全面的掌握case when的用法,case when在平时的开发过程中还是使用得比较多的。 文章目录
  • CASE WHEN 的简单用法

    千次阅读 2021-03-17 13:34:44
    CASE有两种格式简单CASE函数:casesexwhen1then'男'when0then'女'else其他endCASE搜索函数:casewhensex=1then'男'whensex=0then'女'else其他end在工作中数据表中会有不少字段是以代码形式表示某些属性值,常见的如...
  • MYSQL:case when用法

    千次阅读 2021-01-30 00:56:17
    一、case when的使用方法Case具有两种格式。简单Case函数和Case搜索函数。第一种 格式 :简单Case函数 :格式说明case 列名when 条件值1 then 选项1when 条件值2 then 选项2.......else 默认值 endeg:selectcase job_...
  • C# WaitAll WaitAny WhenAll WhenAny傻傻分不清 WaitAll WaitAny WhenAll WhenAny都是Task的静态方法 WaitAll: 阻塞式,主线程会等待所有任务结束,然后才能执行后续语句 WaitAny, 阻塞式,主线程会等待任意任务...
  • sql语句判断方法之一selectcase when t.bk1='on' then 1else 0 end as 基础 ,case when t.bk2='on' then 1else 0 end as 法规 ,case when t.bk3='on' then 1else 0 end as 电算化from info tselect t.bk1,t.bk2,t....
  • CASE WHEN

    2021-04-01 17:12:39
    select case 字段 when 1 then ‘有’ else ‘无’ end as 字段 from 表 select (case when password is null then ‘1’ else password ) end ) as 字段 from 表 select c.MRL,c.OPERATION_ID,c.PRODUCT_ID, sum(if...
  • I am writing a procedure in which i have sql statement which will insert values in TEMP_WF_WORKFLOW table using CASE WHEN statement. The condition is when STATUS_ID is 0 then the EVENT_ID=10003 and wh...
  • UPDATE sjgx_fwml_sqxx WHEN id=#{i.id} THEN #{i.zdqqcs} WHEN id=#{i.id} THEN #{i.ljqqcs} WHEN id=#{i.id} THEN #{i.gxsj} WHERE id IN #{i.id} 控制台打印执行的sql为: UPDATE sjgx_fwml_sqxx SET zdqqcs = ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,706,027
精华内容 682,410
关键字:

when