|
SQL |
LINQ | Lambda |
| SELECT *FROM Employees |
from e in Employees select e |
Employees .Select (e => e) |
|
SELECT e.LoginID,e.JobTitle FROM Employees AS e |
from e in Employees select new {e.LoginID,e.JobTitle} |
Employees.Select( e=>new{ LoginID=e.LoginID JobTitle=e.Jobtitle } ) |
|
变量取别名 SELECT e.LoginID AS ID,e.JobTitle AS Job FROM Employees AS e |
from e in Employees select new {ID=e.LoginID,Job=e.JobTitle} |
Employees.Select ( e=>new{ ID=e.LoginID Job=e.JobTitle } ) |
|
关键字：DISTINCT SELECT DISTINCT e.JobTitle FROM Employees |
(from e in Employees select e.JobTitle).Distinct() |
Employees.Select(e=>e.JobTitle) .Distinct() |
|
关键字：WHERE SELECT * FROM Employees AS e WHERE e.LoginID='test' |
from e in Employees where e.LoginID='test' select e |
Empoyees.where(e=>e.LoginID='test') |
| WHERE....AND....
SELECT * FROM Employees AS e WHERE e.LoginID='test' AND e.SalsriedFlag=1 |
from e in Employees where e.LoginID='test' && e.SalariedFlag=1 |
Employee.where( e=>(e.LoginID='test' && e.SalariedFlag=1) ) |
| 条件运算符>=
SELECT * FROM Employee AS e WHERE e.VacationHours >= 2 AND e.VacationHours <= 10 |
from e in Employees where e.VacationHours>=2 && e.VacationHours<=10 |
Employees.where( e=> ( (Int32) e.VacationHours >= 2 && (Int32)e.VacationHours <= 10 ) ) |
|
ORDER BY SELECT * FROM Employees AS e ORDER BY e.NationalIDNumber
|
from e in Employees orderby e.NationalIDNumber select e |
Employees .orderby (e=>e.NationalIDNumber) |
|
SELECT * FROM Employees AS e ORDER BY e.HireDate,e.NationalIDNumber |
from e in Employees orderby e.HireDate,e.NationalIDNumber select e |
Employees .OrderByDescending (e=>e.HireDate) .ThenBy (e=>e.NationalIDNumber) |
|
关键字：LIKE SELECT * FROM Employees AS e WHERE e.JobTitle LIKE 'Vice%' OR SUBSTRING(e.JobTitle,0,3)='Pro' |
from e in Employees where e.JobTitle.StartsWith("Vice") || e.JobTitle.Substring(0,3)=="Pro" select e |
Employees .Where(e=>(e.JobTitle.StartsWith("Vice") || (e.JobTitle.Substring(0,3)=="Pro") ) ) |
|
关键字：SUM SELECT SUM(e.VacationHours) FROM Employees AS e |
( from e in Employees select e.VacationHours).Sum() |
Employees.Sum(e=>e.VacationHous) |
|
关键字：COUNT SELECT COUNT(*) FROM Employees AS e |
( from e in Employees select e).Count() |
Employees.Count(); |
|
GROUP BY SELECT SUM(e.VacationHours) AS TotalVacation, e.JobTitle FROM Employees AS e GROUP BY e.JobTitle |
from e in Employees group e by e.JobTitle into g select new{ JobTitle=g.Key, Totalvacation=g.Sum(e=>e.VacationHours) } |
Employees .GroupBy (e=>e.JobTitle) .Select ( g=>new{ JobTitle=g.Key, TotalVacation=g.Sum (e => (Int32)(e.VacationHours)) } ) |
|
关键字：HAVING SELECT e.JobTitle, SUM(e.VacationHours) AS TotalVacation FROM Employees AS e GROUP BY e.JobTitle HAVING e.COUNT(*)>2 |
from e in Employees group e by e.JobTitle into g where g.Count()>2 select new { JobTitle=g.Key, TotalVacation=g.Sum(e=>e.vacationHours)} |
Employees .GroupBy (e=>e.JobTitle) .Where (g=>g.Count()>2) .Select( g=>new{ JobTitle=g.JobTitle， TotalVacation= g.Sum(e => (Int32)(e.VacationHours)) }) |
|
多表查询 SELECT * FROM Products AS p, ProductReviews AS pr |
from p in Products from pr in ProductReviews select new {p,pr} |
Products .SelectManay( p=>ProductReviews, (p,pr)=>new{ p=p; pr=pr }) |
|
INNER JOIN SELECT * FROM Products AS p INNER JOIN ProductReviews AS pr ON p.ProductID=pr.ProductID |
from p in Products join pr in ProductReviews on p.ProductID equals pr.ProductID select new {p,pr} |
Products .Join( ProductReviews,p=>p.ProductID, pr=>pr.ProductID, (p,pr)=>new{ p=p,pr=pr} ) |
|
JOIN.. ON...AND... SELECT * FROM Products AS p INNER JOIN ProductCostHistory AS pch ON p.ProductID = pch.ProductID AND p.SellStartDate = pch.StartDate |
from p in Poducts join pch in ProductCostHistory on new{ p.ProductID,StartDate=p.SellStartDate} equals new {pch.ProductID,StartDate=p.SellStartDate} select new {p,pch} |
Products .Join( ProductCostHistory, p=>new{ProductID=p.ProductID, StartDate=p.SellStartDate}, pch =>new{ProductID=pch.ProductID, StartDate=pch.SellStartDate}, (p,pch)=>new{p=p,pch=pch} ) |
|
LEFT OUTER JOIN SELECT * FROM Products AS p LEFT OUTER JOIN ProductReviews as pr ON p.ProductID = pr.ProductID |
from p in Products join pr in ProductReviews on p.ProductID equals pr.ProductID into prodrev select new{p,prodrev} |
Products .GroupJoin ( ProductReviews, p=>p.ProductId, pr=>pr.ProductID, (p,prodrev)=>new{p=p,prodrev=prodrev} ) |
|
关键字：UNION SELECT p.ProductID AS ID FROM Production.Product AS p UNION SELECT pr.ProductReviewID FROM Production.ProductReview AS pr |
(from p in Products select new{ID=p.ProductID}).Union (from pr in ProductReviews select new{ID=p.ProductReviewID} ) |
Products .Select( p=>new{ID=p.ProductID} ).Union( ProductReviews .Select( pr=>new{ID=pr.ProductReviewID} ) ) |
|
SELECT TOP(10) * FROM Products AS p WHERE p.StandardCost<100 |
(from p in Products where p.StandardCost<100 select p).Take(10) |
Products .Where(p=>p.StandardCost<100) .Take(10) |
|
嵌套查询 SELECT * FROM Products AS p WHERE p.ProductID IN( SELECT pr.ProductID FROM ProductReviews as pr WHERE pr.Rating=5) |
from p in Products where (from pr in ProductReviews where pr.rating==5 select pr.ProductID).Contains(p.ProductID) select p |
Products .Where( p=>ProductReviews .Where(pr=>pr.Rating==5) .Select(pr=>pr.ProductID) .Contains(p.ProductID) ) |
|
SELECT AVERAGE(e.Age) FROM Employee AS e ...MIN(xx)... ...MAX(xx)... |
(from e in Employee select e.Age).Average() ...Min()... ...Max()... |
Employees.Average(e.Age) ...Min()... ...Max()... |
解释：
UNION 操作符
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
UNION 语法
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
注释：默认地，UNION 操作符选取不同的值，即UNION是去了重的。如果允许重复的值，请使用 UNION ALL。
UNION ALL 语法
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
UNION 指令的目的是将两个 SQL 语句的结果合并起来。从这个角度来看， UNION 跟 JOIN 有些许类似，因为这两个指令都可以由多个表格中撷取资料。union只是将两个结果联结起来一起显示，并不是联结两个表