求一SQL语句

Question

 SELECT * FROM TRAIN_JL_DEFINE WHERE TO_DATE(TO_CHAR(CUR_DATE,'YYYY-MM-DD'),'YYYY-MM-DD')=TO_DATE('2011-2-20','YYYY-MM-DD') and  train_name='Z74/Z73'   order by id
这是我的SQL语句，查出来的数据是ID       TRAIN_NAME   BZ_NAME          CD_NAME       bz_state
331564 Z74/Z73      Z74/73直一   合京车队 Z73/Z74
331592 Z74/Z73     Z74/73直二   合京车队 Z74/Z73
331620 Z74/Z73      Z74/73直三   合京车队 Z74                要这条
331648 Z74/Z73     Z74/73直四   合京车队 休
331676 Z74/Z73     T63/64特二   合京车队 休
331704 Z74/Z73     T63/64特一   合京车队 休
331732 Z74/Z73     K747/748快三   合京车队 Z73                 要这条
331760 Z74/Z73     K747/748快四   合京车队 Z73/Z74现在只想要上面的那两条，排序就是 order by id，一个在休字的前面，一个在休字的后面。

Answer 1

SELECT * FROM TRAIN_JL_DEFINE WHERE TO_DATE(TO_CHAR(CUR_DATE,'YYYY-MM-DD'),'YYYY-MM-DD')=TO_DATE('2011-2-20','YYYY-MM-DD') and train_name='Z74/Z73' and bz_state IN ('Z73','Z74') order by id不知道你那个是啥意思

Answer 2

这个不用管，现在自要两条记录，一条在休字的上面，一天在休字的下面。
ID TRAIN_NAME BZ_NAME CD_NAME bz_state
331564 Z74/Z73 Z74/73直一 合京车队 Z73/Z74
331592 Z74/Z73 Z74/73直二 合京车队 Z74/Z73
331620 Z74/Z73 Z74/73直三 合京车队 Z74 要这条
331648 Z74/Z73 Z74/73直四 合京车队 休
331676 Z74/Z73 T63/64特二 合京车队 休
331704 Z74/Z73 T63/64特一 合京车队 休
331732 Z74/Z73 K747/748快三 合京车队 Z73 要这条
331760 Z74/Z73 K747/748快四 合京车队 Z73/Z74

Answer 3

需求很模糊。
331620 Z74/Z73 Z74/73直三 合京车队 Z74 要这条
331732 Z74/Z73 K747/748快三 合京车队 Z73 要这条order by id是按id升序还是降序呢？如果以TRAIN_NAME BZ_NAME CD_NAME 分组的话,
331620 Z74/Z73 Z74/73直三 合京车队 Z74 要这条    --这条id最大
331732 Z74/Z73 K747/748快三 合京车队 Z73 要这条  --这条id最小

Answer 4

[code=SQ]SELECT * FROM TRAIN_JL_DEFINE WHERE CUR_DATE=
TO_DATE('2011-2-20','YYYY-MM-DD')AND train_name='Z74/Z73'
 AND (bz_state in ('Z73','Z74')ORDER BY id[/code]

Answer 5

[code=SQ]
ROWNUM 做个行标识，在结果集里找出下一条或上一条那个字段还有 “休” 字的，然后在外边的嵌套查询里把有“休” 字的记录除去，应该是你要的结果！
[/code]

Answer 6

[code=SQ]WITH tab AS(
SELECT '331564' id, 'Z74/Z73' train_name, 'Z74/73直一' bz_name, '合京车队' cd_name, 'Z73/Z74' bz_state FROM dual
UNION ALL 
SELECT '331592', 'Z74/Z73', 'Z74/73直二', '合京车队', 'Z74/Z73' FROM dual 
UNION ALL 
SELECT '331620', 'Z74/Z73', 'Z74/73直三', '合京车队', 'Z74 要这条' FROM dual
UNION ALL 
SELECT '331648', 'Z74/Z73', 'Z74/73直四', '合京车队', '休' FROM dual
UNION ALL
SELECT '331676', 'Z74/Z73', 'T63/64特二', '合京车队', '休' FROM dual
UNION ALL
SELECT '331704', 'Z74/Z73', 'T63/64特一', '合京车队', '休' FROM dual
UNION ALL
SELECT '331732', 'Z74/Z73', 'K747/748快三', '合京车队', 'Z73 要这条' FROM dual
UNION ALL
SELECT '331760', 'Z74/Z73', 'K747/748快四', '合京车队', 'Z73/Z74' FROM dual
)
SELECT id, train_name, bz_name, cd_name, bz_state FROM(
    SELECT id, train_name, bz_name, cd_name, bz_state,
           lead(bz_state)over(order by id) lead_state,
           lag(bz_state)over(order by id) lag_state
    FROM tab
)
WHERE (lead_state='休' OR lag_state='休') AND bz_state!='休'
--结果：
ID      TRAIN_NAME   BZ_NAME        CD_NAME    bz_state
331620  Z74/Z73      Z74/73直三     合京车队   Z74 要这条
331732  Z74/Z73      K747/748快三   合京车队   Z73 要这条
                                         
[/code]

Answer 7

[code=SQ]SQL> select * from TRAIN_JL_DEFINE;
 
        ID TRAIN_NAME                                                                       BZ_NAME                                                                          CD_NAME                                                                          BZ_STATE
---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
    331564 Z74/Z73                                                                          Z74/73直一                                                                       合京车队                                                                         Z73/Z74
    331592 Z74/Z73                                                                          Z74/73直二                                                                       合京车队                                                                         Z74/Z73
    331620 Z74/Z73                                                                          Z74/73直三                                                                       合京车队                                                                         Z74
    331648 Z74/Z73                                                                          Z74/73直四                                                                       合京车队                                                                         休
    331676 Z74/Z73                                                                          T63/64特二                                                                       合京车队                                                                         休
    331704 Z74/Z73                                                                          T63/64特一                                                                       合京车队                                                                         休
    331732 Z74/Z73                                                                          K747/748快三                                                                     合京车队                                                                         Z73
    331760 Z74/Z73                                                                          K747/748快四                                                                     合京车队                                                                         Z73/Z74
 
8 rows selected
 
SQL> 
SQL> with tab as (select a.*,row_number()over(order by id) rn from train_jl_define a)
  2  SELECT a.id, a.train_name, a.bz_name, a.cd_name, a.bz_state
  3    FROM tab a
  4   WHERE (EXISTS (SELECT 1
  5                    FROM tab b
  6                   WHERE a.rn = b.rn - 1
  7                     AND b.bz_state = '休') OR EXISTS
  8          (SELECT 1
  9             FROM tab b
 10            WHERE a.rn = b.rn + 1
 11              AND b.bz_state = '休'))
 12     AND a.bz_state <> '休';
 
        ID TRAIN_NAME                                                                       BZ_NAME                                                                          CD_NAME                                                                          BZ_STATE
---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
    331620 Z74/Z73                                                                          Z74/73直三                                                                       合京车队                                                                         Z74
    331732 Z74/Z73                                                                          K747/748快三                                                                     合京车队                                                                         Z73
 
SQL> [/code]

Answer 8

哎。。替CSDN管事的人悲哀啊，这格式编辑器都不行好几天了，维护人员都是吃剩饭啊？
只能说，给我的感觉很不爽！

Answer 9

奇怪,现在不能用sql模式贴回复了么?

Answer 10

我的数据是从数据库查出来的，你用
WITH tab AS(
SELECT '331564' id, 'Z74/Z73' train_name, 'Z74/73直一' bz_name, '合京车队' cd_name, 'Z73/Z74' bz_state FROM dual
UNION ALL
SELECT '331592', 'Z74/Z73', 'Z74/73直二', '合京车队', 'Z74/Z……
这种方式，小弟对这个方式的用法不是很明白。还望指教。