sql功底展示

功能性sql

/**ziping**/
SELECT
  a. NAME,
  a.REGION_CITY_NAME,
  a.REGION_DISTRICT_NAME,
  a.LAST_MODIFIED_DATE_,
  b.LAST_MODIFIED_DATE_,
  c.LAST_MODIFIED_DATE_,
  d.LAST_MODIFIED_DATE_
FROM
  (
      SELECT
          selff. NAME,
          selff.REGION_CITY_NAME,
          selff.REGION_DISTRICT_NAME,
          selff.EVALUATION_ORG_,
          selff.LAST_MODIFIED_BY_,
          selff.LAST_MODIFIED_DATE_,
          selff.CREATED_DATE_
      FROM
          (
              SELECT
                  es. NAME,
                  es.REGION_CITY_NAME,
                  es.REGION_DISTRICT_NAME,
                  es.EVALUATION_ORG_,
                  selfee.LAST_MODIFIED_BY_,
                  selfee.LAST_MODIFIED_DATE_,
                  selfee.CREATED_DATE_,
                  row_number () OVER (
                      PARTITION BY es. NAME
                      ORDER BY
                          selfee.LAST_MODIFIED_DATE_ DESC
                  ) AS nums
              FROM
                  ENTERPRISE_SCORE es
              LEFT JOIN ECE_NORMAL_CREDIT_EVALUATION_ ence ON es.NORMAL_ID = ence.id
              LEFT JOIN ece_self_normal_relation_ EsNR ON EsNR.ECE_NORMAL_CREDIT_EVALUATION__ID = ES.ID
              LEFT JOIN ece_evaluation_ selfee ON selfee.id = EsNR.SELF_NORMAL_EVALUATES_ID
              WHERE
                  es.EVALUATION_ORG_ = 'ST'
          ) selff
      WHERE
          nums = 1
  ) a
LEFT JOIN /** quxian**/
(
  SELECT
      *
  FROM
      (
          SELECT
              es. NAME,
              es.REGION_CITY_NAME,
              es.REGION_DISTRICT_NAME,
              es.EVALUATION_ORG_,
              countryee.LAST_MODIFIED_BY_,
              countryee.LAST_MODIFIED_DATE_,
              row_number () OVER (
                  PARTITION BY es. NAME
                  ORDER BY
                      countryee.LAST_MODIFIED_DATE_ DESC
              ) AS nums
          FROM
              ENTERPRISE_SCORE es
          LEFT JOIN ECE_NORMAL_CREDIT_EVALUATION_ ence ON es.NORMAL_ID = ence.id
          LEFT JOIN ECE_COUNTY_NORMAL_RELATION_ ECNR ON ECNR.ECE_NORMAL_CREDIT_EVALUATION__ID = ES.ID
          LEFT JOIN ece_evaluation_ countryee ON countryee.id = ecnr.COUNTY_NORMAL_EVALUATES_ID
          WHERE
              es.EVALUATION_ORG_ = 'ST'
      ) country
  WHERE
      nums = 1
) b ON a. NAME = b. NAME
LEFT JOIN /**shizou**/
(
  SELECT
      *
  FROM
      (
          SELECT
              es. NAME,
              es.REGION_CITY_NAME,
              es.REGION_DISTRICT_NAME,
              es.EVALUATION_ORG_,
              cityee.LAST_MODIFIED_BY_,
              cityee.LAST_MODIFIED_DATE_,
              row_number () OVER (
                  PARTITION BY es. NAME
                  ORDER BY
                      cityee.LAST_MODIFIED_DATE_ DESC
              ) AS nums
          FROM
              ENTERPRISE_SCORE es
          LEFT JOIN ECE_NORMAL_CREDIT_EVALUATION_ ence ON es.NORMAL_ID = ence.id
          LEFT JOIN ECE_CITY_NORMAL_RELATION_ ECNR ON ECNR.ECE_NORMAL_CREDIT_EVALUATION__ID = ES.ID
          LEFT JOIN ece_evaluation_ cityee ON cityee.id = ecnr.CITY_NORMAL_EVALUATES_ID
          WHERE
              es.EVALUATION_ORG_ = 'ST'
      ) city
  WHERE
      nums = 1
) c ON a. NAME = c. NAME
LEFT JOIN /**st**/
(
  SELECT
      *
  FROM
      (
          SELECT
              es. NAME,
              es.REGION_CITY_NAME,
              es.REGION_DISTRICT_NAME,
              es.EVALUATION_ORG_,
              stee.LAST_MODIFIED_BY_,
              stee.LAST_MODIFIED_DATE_,
              row_number () OVER (
                  PARTITION BY es. NAME
                  ORDER BY
                      stee.LAST_MODIFIED_DATE_ DESC
              ) AS nums
          FROM
              ENTERPRISE_SCORE es
          LEFT JOIN ECE_NORMAL_CREDIT_EVALUATION_ ence ON es.NORMAL_ID = ence.id
          LEFT JOIN ECE_PROVINCE_NORMAL_RELATION_ epnr ON epnr.ECE_NORMAL_CREDIT_EVALUATION__ID = ES.ID
          LEFT JOIN ece_evaluation_ stee ON stee.id = epnr.PROVINCE_NORMAL_EVALUATES_ID
          WHERE
              es.EVALUATION_ORG_ = 'ST'
      ) city
  WHERE
      nums = 1
) d ON a. NAME = d. NAME

说明

sql这么长，就是装个***
数据库是DB2
这里面其实就一个功能点
group by分组时，想根据某一列分组，但是又想查询其他列。
我这里的实现方法采用了row_number 函数方法

row_number 函数用法

row_number () OVER (PARTITION BY col1 ORDER BY col2)
col1 列名一，分组的列（非必须）
col2列名二，排序的列（非必须）
这样就会生成新的一列，按col1 进行分组，按col2进行排序。把结果看成新表，where条件新列，可以实现筛选。

其他方法

mysql可以关闭group by校验。