"SELECT * FROM jiradata WHERE project_key in 'MUL'" 哪里错了
时间: 2024-05-03 22:18:50 浏览: 7
这个 SQL 查询语句中,`project_key` 应该是一个列名,而不是一个字符串,所以需要将 `in` 后面的字符串用括号括起来表示一个项目列表,如下所示:
```
SELECT * FROM jiradata WHERE project_key IN ('MUL')
```
如果需要查询多个项目,可以在括号中用逗号分隔,如下所示:
```
SELECT * FROM jiradata WHERE project_key IN ('MUL', 'PRO', 'DEV')
```
相关问题
SET @batch_date = '20230222'; set @partition_name=concat('p',@batch_date); set @stat=concat('alter table tb_bond_price_comparision_reuslt truncate partition ',@partition_name); prepare stmt from @stat; execute stmt; insert into tb_bond_price_comparision_reuslt select distinct org.ISIN, mul.Broker_Yield, mul.Broker_Price, org.MARKET_PRICE, case when org.MARKET_PRICE > mul.Broker_Price then 'Broker Price' else 'BVAL' end as MARKET_PRICE_MIN, org.TRADE_REF, case when org.MARKET_PRICE > mul.Broker_Price then mul.Broker_Price else org.MARKET_PRICE end as MARKET_PRICE_MIN, case when org.MARKET_PRICE > mul.Broker_Price then mul.Broker_Price * org.NOMINAL_AMT else org.MARKET_PRICE * org.NOMINAL_AMT end as MARKET_VALUE_MIN, org.dt from tb_trade_blotter_op_sec org left join tb_bond_price_comparision_trade_blotter_op_sec_mul mul on org.TRADE_REF = mul.TRADE_REF and org.ISIN = mul.ISIN and org.dt = mul.dt where org.dt = @batch_date转换为mybatis执行语句以及生成service层代码
首先,将SQL语句转换为MyBatis执行语句:
```sql
<insert id="insertBondPriceComparisonResult" parameterType="string">
SET @batch_date = #{batchDate};
SET @partition_name = CONCAT('p', @batch_date);
SET @stat = CONCAT('ALTER TABLE tb_bond_price_comparision_reuslt TRUNCATE PARTITION ', @partition_name);
PREPARE stmt FROM @stat;
EXECUTE stmt;
INSERT INTO tb_bond_price_comparision_reuslt
SELECT DISTINCT
org.ISIN,
mul.Broker_Yield,
mul.Broker_Price,
org.MARKET_PRICE,
CASE
WHEN org.MARKET_PRICE > mul.Broker_Price THEN 'Broker Price'
ELSE 'BVAL'
END AS MARKET_PRICE_MIN,
org.TRADE_REF,
CASE
WHEN org.MARKET_PRICE > mul.Broker_Price THEN mul.Broker_Price
ELSE org.MARKET_PRICE
END AS MARKET_PRICE_MIN,
CASE
WHEN org.MARKET_PRICE > mul.Broker_Price THEN mul.Broker_Price * org.NOMINAL_AMT
ELSE org.MARKET_PRICE * org.NOMINAL_AMT
END AS MARKET_VALUE_MIN,
org.dt
FROM tb_trade_blotter_op_sec org
LEFT JOIN tb_bond_price_comparision_trade_blotter_op_sec_mul mul
ON org.TRADE_REF = mul.TRADE_REF AND org.ISIN = mul.ISIN AND org.dt = mul.dt
WHERE org.dt = @batch_date;
</insert>
```
然后,生成对应的Service层代码:
```java
@Service
public class BondPriceComparisonResultServiceImpl implements BondPriceComparisonResultService {
@Autowired
private BondPriceComparisonResultMapper bondPriceComparisonResultMapper;
@Override
public void insertBondPriceComparisonResult(String batchDate) {
bondPriceComparisonResultMapper.insertBondPriceComparisonResult(batchDate);
}
}
```
SET @batch_date = '20230222'; set @partition_name=concat('p',@batch_date); set @stat=concat('alter table tb_bond_price_comparision_reuslt truncate partition ',@partition_name); prepare stmt from @stat; execute stmt; insert into tb_bond_price_comparision_reuslt select distinct org.ISIN, mul.Broker_Yield, mul.Broker_Price, org.MARKET_PRICE, case when org.MARKET_PRICE > mul.Broker_Price then 'Broker Price' else 'BVAL' end as MARKET_PRICE_MIN, org.TRADE_REF, case when org.MARKET_PRICE > mul.Broker_Price then mul.Broker_Price else org.MARKET_PRICE end as MARKET_PRICE_MIN, case when org.MARKET_PRICE > mul.Broker_Price then mul.Broker_Price * org.NOMINAL_AMT else org.MARKET_PRICE * org.NOMINAL_AMT end as MARKET_VALUE_MIN, org.dt from tb_trade_blotter_op_sec org left join tb_bond_price_comparision_trade_blotter_op_sec_mul mul on org.TRADE_REF = mul.TRADE_REF and org.ISIN = mul.ISIN and org.dt = mul.dt where org.dt = @batch_date转化为mybatis语句,并且service层如何设计
Mapper文件:
```
<select id="getBondPriceComparisonResult" parameterType="java.util.Map" resultMap="BondPriceComparisonResult">
SET @batch_date = #{batchDate};
SET @partition_name=concat('p',@batch_date);
SET @stat=concat('alter table tb_bond_price_comparision_reuslt truncate partition ',@partition_name);
PREPARE stmt from @stat;
EXECUTE stmt;
insert into tb_bond_price_comparision_reuslt select distinct org.ISIN, mul.Broker_Yield, mul.Broker_Price, org.MARKET_PRICE, case when org.MARKET_PRICE > mul.Broker_Price then 'Broker Price' else 'BVAL' end as MARKET_PRICE_MIN, org.TRADE_REF, case when org.MARKET_PRICE > mul.Broker_Price then mul.Broker_Price else org.MARKET_PRICE end as MARKET_PRICE_MIN, case when org.MARKET_PRICE > mul.Broker_Price then mul.Broker_Price * org.NOMINAL_AMT else org.MARKET_PRICE * org.NOMINAL_AMT end as MARKET_VALUE_MIN, org.dt from tb_trade_blotter_op_sec org left join tb_bond_price_comparision_trade_blotter_op_sec_mul mul on org.TRADE_REF = mul.TRADE_REF and org.ISIN = mul.ISIN and org.dt = mul.dt where org.dt = @batch_date
</select>
```
Service层:
```
public interface BondPriceComparisonService {
List<BondPriceComparisonResult> getBondPriceComparisonResult(String batchDate);
}
```
实现类:
```
@Service
public class BondPriceComparisonServiceImpl implements BondPriceComparisonService {
@Autowired
private BondPriceComparisonMapper bondPriceComparisonMapper;
@Override
public List<BondPriceComparisonResult> getBondPriceComparisonResult(String batchDate) {
Map<String, Object> params = new HashMap<>();
params.put("batchDate", batchDate);
return bondPriceComparisonMapper.getBondPriceComparisonResult(params);
}
}
```