此篇介绍的是oracle、mysql、sqlserver、达梦、人大金仓、南大通用数据库的单主键和复合主键select in的查询总结。
Mysql
Select `id`,`name` from `t_db_task` where (id,name) in(('915','Oracle内到外全表同步'),('916','Oracle外到内全表同步'),('921','Oracle外到内的触发同步'));
select id from `t_db_task` where (id) in (('915'),('916'),('921'));
oracle
select "ID","OWNER_ID" from "B02" where ("ID","OWNER_ID") in(('1','2'),('2','2'),('3','2'));
select "ID" from "B02" where ("ID") in(('1'),('2'),('3'));
sqlserver
select [ID],[Number] from [Student] where ([ID]) in ((1),(2),(3),(10)) and ([Number]) in(('0'),('1'),('2'),('9'));
select [ID] from [dbo].[Student] where ([ID]) in (('1'),('2'),('3'));
达梦8
select "CHAR_1","NUMERIC_5" from "SYSDBA"."TABLE_2" where ("CHAR_1","NUMERIC_5") in (('1',1.000000),('2',2.000000))
select "CHAR_1" from "SYSDBA"."TABLE_2" where ("CHAR_1") in (('1'),('2'));
人大金仓7
select "sinteger","stext" from "PUBLIC"."DM_k9" where ("sinteger","stext") in ((1,'dfdsf'),(2,'dfdsf'));
select "sinteger" from "PUBLIC"."DM_k9" where ("sinteger") in ((1),(2));
GBase8s
select id,test1 from test.test3 where (id,test1) in ((1,'435435'),(21,'222'));
select id from test.test3 where (id) in ((1),(21));
在网上几乎找不到sqlserver多主键的select in的写法。
重点mark一下sqlserver:
我起初以为也是
select A字段, B字段 from 表名 where (A字段, B字段)in ((A数据1,B数据1),(A数据2,B数据2)...)
这样格式的。但结果却是差之千里啊。
测试表 Student 的原始数据:
按照最开始的写法是
SELECT [ID],[Number] FROM [dbo].[Student] where ([ID],[Number]) in ((1,'0'),(2,'1'),(3,'2'));
报错,不支持。于是换了新的格式,+号。
现在是没有报错了,但是查出来的数据明显有错,这种查询方法是不分前后书序的。
于是,再换一种新的方式。
终于正确了。