如何将以下查询转换为sqlite格式（语法）。或者如何在sql lite中使用'NOT IN' [英] How to convert the the below query to sqlite format (syntax). Or how to use 'NOT IN' in sql lite

问题描述

 开始  tran    -    rollback tran  
  insert   into  nfc_DeliveryPlan（patient_id，contact_id，isactive，createddate，modifieddate，CreatedUserId）
  select  patient_id，contact_id， 1 ，'  2013-12-05'， NULL ，patient_id 来自 nfc_careplan_contacts 
 其中 contact_id < span class =code-keyword>不  （ select  contact_id 来自 nfc_DeliveryPlan 其中 contact_id =  420 ）
 < span class = code-keyword>和 contact_id =  420  

解决方案

< blockquote>您的查询永远不会返回任何结果！因为你的条款总是假的！

因为你不能使用join，那就更优化了！

 String query =  选择patient_id，contact_id，1，'2013-12-05'，NULL，patient_id来自nfc_careplan_contacts左连接nfc_DeliveryPlan b on a .contact_id = b.contact_id，其中a.contact_id = 420，b.contact_id = 420，a.contact_id！= b.contact_id）和a.contact_id = 420;; 
 光标 c = 数据库 .rawQuery（查询，空）; 

begin tran --rollback tran
insert into nfc_DeliveryPlan (patient_id,contact_id,isactive,createddate,modifieddate,CreatedUserId)
select patient_id, contact_id, 1, '2013-12-05', NULL, patient_id from nfc_careplan_contacts
where contact_id not in (select contact_id from nfc_DeliveryPlan where contact_id = 420)
and contact_id = 420

解决方案

Your query never return any result! because of your terms which are always false!
for not in you can use join, that's more optimize!

String query = "select patient_id, contact_id, 1, '2013-12-05', NULL, patient_id from nfc_careplan_contacts a left join nfc_DeliveryPlan b on a.contact_id = b.contact_id where a.contact_id = 420 and b.contact_id = 420 and a.contact_id != b.contact_id) and a.contact_id = 420;";
Cursor c = database.rawQuery(query, null);

这篇关于如何将以下查询转换为sqlite格式（语法）。或者如何在sql lite中使用'NOT IN'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！