linq to sql生成not in语句的小技巧
以前一直觉得linq to sql生成类似where id not in (1,3,5)或where id not in (select id from ...)这样的条件不是很方便,每次我都是把条件ID事先取到一个数组里,然后用 !Arr.Contains(c.Id)这样处理,今天突然发现这样好傻,其实可以完全直接用linq写成一句,贴个示例在这里,以后备查
from a in TableA where !(from b in TableB Where ... select b.Id).Contains(a.Id)
最终翻译出来的语句并非跟not in 完全一样的,而是用not exists(...),不过效果完全相同,能达到要求就行了!
相关文档:
Subquery: (single-row subqueries and multi-rows subqueries).
select select_list
from table
where expr operator (select select_list from table);
single-row subqueries operator: =, >, >=, <, <=, <>
e.g.:
1. select department_id, min(salary) from employees group by department_id ......
GRANT
Name
GRANT -- 定义访问权限
Synopsis GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] tablename [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WI ......
Differring Constraints:
Constraints can have the following attributes: DEFFERRABLE / NOT DEFFERRABLE, INITIALLY DEFFERRED / INITIALLY IMMEDIATE.
e.g.:
alter table dept2 add constraint dept2_id_pk primary key (department_id) deferrable initially deferred; // deferring constraint on creation. ......
CREATE VIEW MYVIEW
AS
SELECT * from bjxxdiweb_database2007.dbo.bm_tongji
UNION ALL
SELECT * from aa.DBO.chen
select * into aa..chen from bjxxdiweb_database2007.dbo.bm_tongji where 1=2
说明:数据库A的表的字段名必须和数据库B的表的字段名相同,包括数据类型等。 ......
Group functions
SELECT [column,] group_function(column) ... from table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];
e.g.:
SELECT department_id, job_id, SUM(salary), COUNT(employee_id) from employees GROUP BY department_id, job_id ;
SELECT [column,] group_function(column).. ......