select查询基础(1)

SELECT 语句的常用方法：

• 基本方法
• 查询条件(WHERE;=><;BETWEEN AND)
• AND;OR;IN;OR IN;
• 模糊查询（LIKE;’_’;’%’）
• 对查询排序(ORDER BY:ASC;DESC)
• SQL内置函数和计算(COUNT;AVG;SUM;MAX;MIN)
• 子查询与连接查询

-- 子查询
SELECT of_dpt,COUNT(proj_name) AS count_project FROM project GROUP BY of_dpt
HAVING of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');

-- 连接查询
SELECT id,name,people_num
FROM employee,department
WHERE employee.in_dpt = department.dpt_name
ORDER BY id;
-- 等价于（on后的表名可省略）
SELECT id,name,people_num
FROM employee JOIN department
ON employee.in_dpt = department.dpt_name
ORDER BY id;

例：查询员工所在部门的人数及工程数

• table1;table2;table3
  
  
• 查询方法1
  
  
• 查询方法2
  

-- 三表查询  写入新表
select a.*, b.Q2QTD_NP_Spending, b.[0501_0507_NP_Spending], b.[0508_0514_NP_Spending], 
c.Q2QTD_Infeeds_Spending, c.[0501_0507_Infeeds_Spending], c.[0508_0514_Infeeds_Spending]
into P4P_temp1
from P4P_temp a
left join NP_temp b
on a.用户名 = b.用户名
left join Infeeds_temp c
on b.用户名 = c.用户名

-- case when ... then * else( ... ) end as 别名
-- 分母判定
case when sum([0501_0507_Total_Spending])=0 then 0 else
(sum([0508_0514_Total_Spending])-sum([0501_0507_Total_Spending]))/sum([0501_0507_Total_Spending]) end as [Diff%_Total_Spending]