数据库多主键in查询组合篇(sqlserver特殊) | 您所在的位置:网站首页 › select组合查询 › 数据库多主键in查询组合篇(sqlserver特殊) |
此篇介绍的是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],[Number] from [Student] where [ID] in(1,2,3,9) and [Number] in('2','2','2','8'); 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 `test3` where (`id`,`test1`) in ((1,'435435'),(2,'45435'),(21,'222')); select `id` from `test3` where (`id`) in (1,2,3) 在网上几乎找不到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')); 报错,不支持。于是换了新的格式,+号。 现在是没有报错了,但是查出来的数据明显有错,这种查询方法是不分前后书序的。 于是,再换一种新的方式。 终于正确了。 |
CopyRight 2018-2019 实验室设备网 版权所有 |