背景:? ? ? ??
????????今天產(chǎn)品驗(yàn)收的時(shí)候,導(dǎo)入了大量數(shù)據(jù);發(fā)現(xiàn)造價(jià)項(xiàng)目某個(gè)查詢列表數(shù)據(jù)多出了幾條數(shù)據(jù);看了Mybatis查詢,才發(fā)現(xiàn)是同時(shí)使用了多個(gè)IN查詢導(dǎo)致的問題;入?yún)⑹菍ο罅斜恚琁n值是分開循環(huán)賦值的,問題就出在這里。
????????需要根據(jù)兩個(gè)字段去查詢多個(gè)值,這兩個(gè)字段值不同的數(shù)據(jù),以前一直以為In查詢只能查詢一個(gè)字段的多個(gè)值。今天百度發(fā)現(xiàn)了一種寫法,可以同時(shí)In查詢多個(gè)字段的不同值。
解決辦法:
SELECT * FROM more_in
WHERE (lmm_id, item_id, `name`) IN ((1,2, '工料機(jī)1'),(2,2, '工料機(jī)2'),(3,3, ''));
腳本實(shí)例:
-- ----------------------------
-- Table structure for more_in
-- ----------------------------
DROP TABLE IF EXISTS `more_in`;
CREATE TABLE `more_in` (
`id` int NOT NULL AUTO_INCREMENT,
`lmm_id` int NULL DEFAULT NULL,
`item_id` int NULL DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`detail` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of more_in
-- ----------------------------
INSERT INTO `more_in` VALUES (1, 1, 2, '工料機(jī)1', NULL);
INSERT INTO `more_in` VALUES (2, 2, 2, '工料機(jī)2', NULL);
INSERT INTO `more_in` VALUES (3, 3, 3, '工料機(jī)3', NULL);
INSERT INTO `more_in` VALUES (4, 4, 3, '工料機(jī)4', NULL);
INSERT INTO `more_in` VALUES (5, 5, 1, '工料機(jī)5', NULL);
INSERT INTO `more_in` VALUES (6, 2, 3, '工料機(jī)6', NULL);
查詢需要的入?yún)⒘斜頂?shù)據(jù),如下:
-- lmm_id,? item_id
-- 1,2
-- 2,2
-- 3,3
錯(cuò)誤寫法:
通過如下語句查詢,會多出ID為6的數(shù)據(jù):
SELECT * FROM more_in a
WHERE a.lmm_id IN (1,2,3) AND a.item_id IN (2,2,3);
正確寫法:
IN同時(shí)可以In多個(gè)字段,In的左側(cè)兩個(gè)字段,右側(cè)必須倆字段,左右兩側(cè)能對應(yīng)上:
SELECT * FROM more_in WHERE (lmm_id, item_id) IN ((1,2),(2,2),(3,3));
MyBatis XML 寫法:
//mapper接口定義
List<Map<String, Object>> getResult(@Param("list") List<Map<String, String>> list);
<select id="getResult" resultType="java.util.HashMap">
select * from demo t where (t.id, t.name) in
<foreach collection="list" item="item" open="(" close=")" separator=",">
(#{item.id},#{item.name})
</foreach>
</select>
其他:
/**
* 獲取相同item_code相關(guān)相同工機(jī)下含量不一致的item_code列表
* @return
**/
List<TbProjectItemDTO> getDiscrepancyList(@Param("data") TbProjectDTO dto);
<select id="getDiscrepancyList" resultType="org.dto.TbProjectItemDTO">
SELECT
tl.lmm_id, cq.item_code
FROM `tb_project_lmm` AS tl
LEFT JOIN `tb_project` AS cq ON cq.project_id= tl.project_id
AND cq.state_deleted = 0
WHERE
tl.state_deleted = 0
<if test="data.projectId != '' and data.projectId != null">
AND tl.`project_id` = #{data.projectId}
</if>
GROUP BY cq.`item_code`, tl.lmm_id HAVING COUNT(DISTINCT IF(tl.`quantity` IS NULL,0,tl.`quantity`)) > 1
</select>
如果本篇文章對你有幫助的話,很高興能夠幫助上你。文章來源:http://www.zghlxwxcb.cn/news/detail-641056.html
當(dāng)然,如果你覺得文章有什么讓你覺得不合理、或者有更簡單的實(shí)現(xiàn)方法又或者有理解不來的地方,希望你在看到之后能夠在評論里指出來,我會在看到之后盡快的回復(fù)你。文章來源地址http://www.zghlxwxcb.cn/news/detail-641056.html
到了這里,關(guān)于MySQL同時(shí)In倆個(gè)字段,In多個(gè)字段,Mybatis多個(gè)In查詢問題,Mysql多個(gè)IN查詢多出數(shù)據(jù)問題,Mysql多個(gè)IN查詢 數(shù)據(jù)準(zhǔn)確問題的文章就介紹完了。如果您還想了解更多內(nèi)容,請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章,希望大家以后多多支持TOY模板網(wǎng)!