TransWikia.com

Как вставить значение в SELECT

Stack Overflow на русском Asked by VincentVega on December 16, 2021

Суть проблемы – у меня есть запрос:

SELECT c.street_id,
       c.street_name,
       'from (?) till (?)' AS period,
       c.cnt AS cnt
FROM
  (SELECT b.street_id,
          b.street_name,
          COUNT(*) AS cnt
   FROM
     (SELECT a.type AS TYPE,
             a.involved_person AS involved_person,
             a.operation AS OPERATION,
             a.operation_name AS operation_name,
             a.latitude AS latitude,
             a.longitude AS longitude,
             a.street_id AS street_id,
             a.street_name AS street_name,
             a.gender AS gender,
             a.age_range AS age_range,
             a.self_defined_ethnicity AS self_defined_ethnicity,
             a.officer_defined_ethnicity AS officer_defined_ethnicity,
             a.legislation AS legislation,
             a.object_of_search AS object_of_search,
             a.outcome AS outcome,
             a.outcome_linked_to_object_of_search AS outcome_linked_to_object_of_search,
             a.removal_of_more_than_outer_clothing AS removal_of_more_than_outer_clothing
      FROM
        (SELECT *
         FROM stopandsearchesbyforce
         WHERE datetime >= TO_DATE('2018-01', 'YYYY-MM')
           AND datetime <= TO_DATE('2018-02', 'YYYY-MM') )AS a) AS b
   GROUP BY b.street_id,
            b.street_name) AS c
ORDER BY c.cnt DESC
LIMIT 10

мне надо в самый верхний SELECT вставить значения from(?) till(?), но через такую реализацию не получается.

Вот сам метод:

@Override
public void getMostDangerousStreets(YearMonth from, YearMonth to) {
    String sqlScript = SQLParser.parseSQLFileToString(PATH_TO_SQL_QUERY_MOST_DANGEROUS_STREET);
    jdbcTemplate.query(sqlScript, new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps) throws SQLException {
            ps.setString(1, String.valueOf(from));
            ps.setString(2, String.valueOf(to));
        }
    }, new RowCallbackHandler() {
        @Override
        public void processRow(ResultSet rs) throws SQLException {
            System.out.println(rs.getLong("street_id"));
            System.out.println(rs.getString("street_name"));
            System.out.println(rs.getString("period"));
            System.out.println(rs.getString("cnt"));
        }
    });
}

One Answer

Я немного не понял вашего кода, так что просто приведу пример для произвольных переменных.

sql=`SELECT c.street_id,
       c.street_name,
       'from (?) till (?)' AS period,
       c.cnt AS cnt
FROM
  (SELECT b.street_id,
          b.street_name,
          COUNT(*) AS cnt
`
list=['a','b'] //a и b - то, что вам надо вставить.
connection.query(sql,list,function(err,result){
console.log(result)
})

Answered by Nezerix on December 16, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP