【急】sql中用case when then else end实现查询

[齊麟]

下面是我原来用的sql，现在我想实现这么个功能，当A.INDI_CODE='4002301'时改为'0031001',当A.INDI_CODE='4002332'时改为'0065701'，其余的不变，我试着用“case when then when then else end”来实现，但是没有获取到数据，可能是我对这个方法不熟悉吧，特此请教高手来帮忙！

SELECT

A.INDI_CODE AS indiCode,
A.INDI_NAME_CH AS indiNameCh,
A.DATETIME_CODE AS dateTimeCode,
C.DATETIME_NAME AS dateTimeName,
A.INDI_VALUE AS indiValue,
A.GRP1_ITEM_ID AS grpItemId,
A.GRP1_NAME_CH AS grpNameCh,
A.GRP1_ITEM_NAME_CH AS grpItemNameCh,
A.TIME_POINT_CODE AS timePointCode,
A.TIME_POINT_NAME AS timePointName,
C.QUARTER_NAME AS quarterName

FROM

ME_DATA_INDI_VALUE_GRP A,ME_DES_INDICATOR B,ME_DES_DATATIME C

WHERE

A.INDI_CODE=B.INDI_CODE AND A.DATETIME_CODE=C.DATETIME_CODE
AND A.GRP1_ITEM_ID = ?
AND A.DATETIME_CODE = ?

ORDER BY

A.INDI_CODE,A.DATETIME_CODE,A.TIME_POINT_CODE

[vber1010]

下面是我原来用的sql，现在我想实现这么个功能，当A.INDI_CODE='4002301'时改为'0031001',当A.INDI_CODE='4002332'时改为'0065701'，其余的不变，我试着用“case when then when then else end”来实现，但是没有获取到数据，可能是我对这个方法不熟悉吧，特此请教高手来帮忙！

SELECT

decode(A.INDI_CODE,'4002301','0031001','4002332','0065701',A.INDI_CODE) AS indiCode,
A.INDI_NAME_CH AS indiNameCh,
A.DATETIME_CODE AS dateTimeCode,
C.DATETIME_NAME AS dateTimeName,
A.INDI_VALUE AS indiValue,
A.GRP1_ITEM_ID AS grpItemId,
A.GRP1_NAME_CH AS grpNameCh,
A.GRP1_ITEM_NAME_CH AS grpItemNameCh,
A.TIME_POINT_CODE AS timePointCode,
A.TIME_POINT_NAME AS timePointName,
C.QUARTER_NAME AS quarterName

FROM

ME_DATA_INDI_VALUE_GRP A,ME_DES_INDICATOR B,ME_DES_DATATIME C

WHERE

A.INDI_CODE=B.INDI_CODE AND A.DATETIME_CODE=C.DATETIME_CODE
AND A.GRP1_ITEM_ID = ?
AND A.DATETIME_CODE = ?

ORDER BY

A.INDI_CODE,A.DATETIME_CODE,A.TIME_POINT_CODE

[vber1010]

decode(A.INDI_CODE,'4002301','0031001','4002332','0065701',A.INDI_CODE) AS indiCode,

[hy_ghb2]

你是不更新数据还是查询数据

[hy_ghb2]

如果是查询，2楼正解

[minitoy]

SQL>  select emp.*,case empno when 7369 then 'aha,it''s you' else 'not you' end from emp;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO CASEEMPNOWHEN7369THEN'AHA,IT''
----- ---------- --------- ----- ----------- --------- --------- ------ ------------------------------
 7369 SMITH      CLERK      7902 1980-12-17     800.10               20 aha,it's you
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30 not you
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30 not you
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20 not you
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30 not you
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30 not you
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10 not you
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20 not you
 7839 KING       PRESIDENT       1981-11-17    5000.00               10 not you
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30 not you
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20 not you
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30 not you
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20 not you
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10 not you
 
14 rows selected
 
SQL>

[齊麟]

在请教下：
这个SELECT (CASE A.INDI_CODE WHEN '4002301' THEN '0031001' WHEN '4002332' THEN '0065701' ELSE A.INDI_CODE END) AS indiCode,

和这个：SELECT (CASE WHEN A.INDI_CODE='4002301' THEN '0031001' WHEN A.INDI_CODE='4002332' THEN '0065701' ELSE A.INDI_CODE END)AS indiCode,

都没什么错误吧？

[minitoy]

SQL>  select emp.*,case when empno=7369 then 'aha,it''s you' else 'not you' end from emp;   
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO CASEWHENEMPNO=7369THEN'AHA,IT'
----- ---------- --------- ----- ----------- --------- --------- ------ ------------------------------
 7369 SMITH      CLERK      7902 1980-12-17     800.10               20 aha,it's you
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30 not you
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30 not you
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20 not you
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30 not you
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30 not you
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10 not you
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20 not you
 7839 KING       PRESIDENT       1981-11-17    5000.00               10 not you
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30 not you
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20 not you
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30 not you
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20 not you
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10 not you
 
14 rows selected
 
SQL> 

[minitoy]

恩,都对啊

[vber1010]

[Quote=引用 6 楼 java_amateur 的回复:]
在请教下：
这个SELECT (CASE A.INDI_CODE WHEN '4002301' THEN '0031001' WHEN '4002332' THEN '0065701' ELSE A.INDI_CODE END) AS indiCode,

和这个：SELECT (CASE WHEN A.INDI_CODE='4002301' THEN '0031001' WHEN A.IN……
[/Quote]
都对！

[齊麟]

嗯，虽然要查询的第一个字段的值变换了，但是排序时怎么没有变化啊？
有谁可以将其改变一下么？

[齊麟]

我将查询的第一个字段的值改变就是为了在排序时能够改变其其位置，但是值变化了，位置还是原来的位置。。。

[齊麟]

结贴，颁奖啦！
原因都是自己太马虎啦！！！！
哈哈~~~~~~~