oracle( 字段b) 取字面b 取字符串的前三位 如何進行分組求和 ？詳細如下~~~

select?t.*,count(1)?count?from?(select?substr(字段名b,0,3)?b?from?表名)?t?group?by?t.字段名b?order?by?字段名b

--第一步截取字符串前三位substr?第二部進行分組排序

oracle先分組再求和

1、最簡單的方法，使用 UNION

SELECT '蘋果' AS NAME, SUM(WEIGHT) AS WEIGHT FROM ZZZZ_TTTT WHERE NAME LIKE '%蘋果'

UNION ALL

SELECT '梨' AS NAME, SUM(WEIGHT) AS WEIGHT FROM ZZZZ_TTTT WHERE NAME LIKE '%梨'

2、使用 CASE WHEN

SELECT NAME, SUM(WEIGHT)

FROM (SELECT CASE

WHEN INSTR(NAME, '蘋果') 0 THEN

'蘋果'

ELSE

'梨'

END AS NAME,

WEIGHT

FROM ZZZZ_TTTT)

GROUP BY NAME

* ZZZZ_TTTT 為表名

oracle 多字段分組取每組求和

select t.* ,

sum(統(tǒng)計字段名) over(partition by 分組字段1),

sum(統(tǒng)計字段名) over(partition by 分組字段2),

sum(統(tǒng)計字段名) over(partition by 分組字段3),

......

sum(統(tǒng)計字段名) over(partition by 分組字段n)

from t ;

千鋒教育是專業(yè)的IT培訓機構，提供java培訓、大數(shù)據(jù)培訓、python培訓、web前端培訓等專業(yè)IT技能提升服務，如果您有IT培訓需求，歡迎聯(lián)系千鋒教育。