一对多sql

  

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
<!--  分页查询派货成本  -->
  <select id="queryCostRegionPriceBycondtion" parameterMap="CostRegionPriceQueryEntity" resultMap="CostRegionPriceResultEntity">
    SELECT
        b.AUDIT_HOUR,
        b.OPER_STATUS,
        b.RELE_ID,
        b.STAR_REGION_ID,
        b.END_REGION_ID,
        b.START_REGION__NAME,
        b.END_REGION_NAME,
        b.CREATE_USER_CODE||'('||e1.EMPLOYEE_NAME||')' CREATE_USER_CODE,
        b.MODIFY_USER_CODE,
        b.CREATE_TIME,
        b.MODIFY_TIME,
        b.DEL_FLAG,
        b.REMARK,
        b.COST_NAME,
        b.START_REGION_TYPE,
        b.END_REGION_TYPE,
        b.CALC_TYPE,
        b.BIZ_TYPE,
        b.TIME_CODE,
        b.PRODUCT_CODE,
        b.DIS_TYPE,
        b.START_TIME,
        b.END_TIME,
        b.PRICE_ITEM_CODE,
         
        c.audit_time,
        c.audit_user_code||'('||e2.EMPLOYEE_NAME||')' audit_user_code,
        case WHEN 
        c.AUDIT_STATUS = 0 and
        <![CDATA[SYSDATE > c.START_TIME]]>
        then 2
        WHEN 
        c.AUDIT_STATUS = 0 and
        <![CDATA[SYSDATE < c.START_TIME]]>
        then 0
        WHEN
        c.AUDIT_STATUS = 1 then 1
        WHEN
        c.AUDIT_STATUS = -1 then -1
        ELSE 4
        END AUDIT_STATUS
    FROM
        T_COST_REGION_PRICE b
         
        left join T_BASE_EMPLOYEE e1
        on b.CREATE_USER_CODE = e1.EMPLOYEE_CODE
        left join
        (
            SELECT
                A .audit_time,
                A .audit_user_code,
                A .rele_id,
                A .AUDIT_status,
            A.START_TIME,
            A.audit_hour
            FROM
                (
                    SELECT
                        ROW_NUMBER () OVER (
                            PARTITION BY T .rele_id
                            ORDER BY
                                T .audit_time ASC
                        ) AS rn,
                        T .*
                    FROM
                        t_cost_price_ad T
                        where 1=1
                         
                ) A
            WHERE
                A .rn = 1
        ) c
          on b.rele_id=c.rele_id
    left join T_BASE_EMPLOYEE e2
          on c.audit_user_code =e2.EMPLOYEE_CODE
    WHERE 1=1
        <if test="auditStatus == 0" >
            and c.AUDIT_STATUS = '0' and
            <![CDATA[SYSDATE < c.START_TIME]]>
        </if>
        <if test="auditStatus == 1" >
            and c.AUDIT_STATUS = #{auditStatus,jdbcType=NUMERIC}
        </if>
        <if test="auditStatus == -1" >
            and c.AUDIT_STATUS = #{auditStatus,jdbcType=NUMERIC}
        </if>
        <if test="auditStatus == 2" >
            and c.AUDIT_STATUS = '0' and
            <![CDATA[SYSDATE > c.START_TIME]]>
        </if>
         <!-- 报价名称 -->
      <if test="costName != null and costName !=''" >
            AND b.COST_NAME like concat(concat('%',#{costName,jdbcType=VARCHAR}),'%')
      </if>
         <!-- 产品类型 -->
      <if test="productCode != null and productCode !=''" >
            AND b.PRODUCT_CODE = #{productCode,jdbcType=NUMERIC}
      </if>
         <!-- 派送方式 -->
      <if test="disType != null and disType !='' or disType == 0" >
            AND b.DIS_TYPE = #{disType,jdbcType=NUMERIC}
      </if>
         <!-- 开始派货区间 -->
      <if test="starRegionId != null and starRegionId !='' and starRegionId !='empty'" >
            AND b.STAR_REGION_ID = #{starRegionId,jdbcType=VARCHAR}
      </if>
         <!-- 结束派货区间 -->
      <if test="endRegionId != null and endRegionId !='' and endRegionId !='empty'" >
            AND b.END_REGION_ID = #{endRegionId,jdbcType=VARCHAR}
      </if>
         <!-- 业务类型 -->
      <if test="bizType != null and bizType !=''" >
            AND b.BIZ_TYPE = #{bizType,jdbcType=NUMERIC}
      </if>
         <!-- 启用状态-->
      <if test="delFlag != null and delFlag !=''" >
            AND b.DEL_FLAG = #{delFlag,jdbcType=NUMERIC}
      </if>
         <!-- 产品时效-->
      <if test="timeCode != null and timeCode !=''" >
            AND b.TIME_CODE = #{timeCode,jdbcType=NUMERIC}
      </if>
         <!-- 计费类型-->
      <if test="calcType != null and calcType !=''" >
            AND b.CALC_TYPE = #{calcType,jdbcType=NUMERIC}
      </if>
      <if test="startTimeStart != null and startTimeEnd != null">
          and <![CDATA[ b.START_TIME  >= #{startTimeStart,jdbcType=TIMESTAMP}]]>
          and <![CDATA[ b.START_TIME  <= #{startTimeEnd,jdbcType=TIMESTAMP}]]>
      </if>
      <if test="endTimeStart != null and endTimeEnd != null">
          and <![CDATA[ b.END_TIME  >= #{endTimeStart,jdbcType=TIMESTAMP}]]>
          and <![CDATA[ b.END_TIME  <= #{endTimeEnd,jdbcType=TIMESTAMP}]]>
      </if>
         <!-- 计费类型-->
      <if test="priceItemCode != null and priceItemCode !=''" >
            AND b.PRICE_ITEM_CODE = #{priceItemCode,jdbcType=NUMERIC}
      </if>
      order by b.CREATE_TIME desc
  </select>
SELECT b.AUDIT_HOUR, b.OPER_STATUS, b.RELE_ID, b.STAR_REGION_ID, b.END_REGION_ID, b.START_REGION__NAME, b.END_REGION_NAME, b.CREATE_USER_CODE||'('||e1.EMPLOYEE_NAME||')' CREATE_USER_CODE, b.MODIFY_USER_CODE, b.CREATE_TIME, b.MODIFY_TIME, b.DEL_FLAG, b.REMARK, b.COST_NAME, b.START_REGION_TYPE, b.END_REGION_TYPE, b.CALC_TYPE, b.BIZ_TYPE, b.TIME_CODE, b.PRODUCT_CODE, b.DIS_TYPE, b.START_TIME, b.END_TIME, b.PRICE_ITEM_CODE, c.audit_time, c.audit_user_code||'('||e2.EMPLOYEE_NAME||')' audit_user_code FROM T_COST_REGION_PRICE b left join T_BASE_EMPLOYEE e1 on b.CREATE_USER_CODE = e1.EMPLOYEE_CODE left join ( SELECT A .audit_time, A .audit_user_code, A .rele_id, A .AUDIT_status, A.START_TIME, A.audit_hour FROM ( SELECT ROW_NUMBER () OVER ( PARTITION BY T .rele_id ORDER BY T .audit_time ASC ) AS rn, T .* FROM t_cost_price_ad T where 1=1 ) A WHERE A .rn = 1 ) c on b.rele_id=c.rele_id left join T_BASE_EMPLOYEE e2 on c.audit_user_code =e2.EMPLOYEE_CODE WHERE 1=1

  

复制代码
SELECT
                A .audit_time,
                A .audit_user_code,
                A .rele_id,
                A .AUDIT_status,
            A.START_TIME,
            A.audit_hour
            FROM
                (
                    SELECT
                        ROW_NUMBER () OVER (
                            PARTITION BY T .rele_id
                            ORDER BY
                                T .audit_time ASC
                        ) AS rn,
                        T .*
                    FROM
                        t_cost_price_ad T
                        where 1=1
                        
                ) A
            WHERE
                A .rn = 1



复制代码

 

 

一对多,(多)条件符合,取(多)时间最近的信息

posted @   【clover】  阅读(445)  评论(0编辑  收藏  举报
点击右上角即可分享
微信分享提示