SQLSERVER SQL编程集合 (part 3)
2013年08月26日
⁄ 综合
⁄ 共 3127字 ⁄ 字号
小 中 大
////////////////////////////////////查询，表达式，总计函数
USE sqlstudy
GO
/**//*先进行分组再进行聚合函数的计算*/
SELECT last_name, trainer, COUNT(*) AS num_trainers, SUM(sales_amt) AS gross_sales, AVG(sales_amt)
FROM employees, sales
WHERE sales > 0 AND emp_id = sold_by
GROUP BY last_name, trainer
HAVING AVG(sales_amt) > 6000
/**//*不能将AS 后的名字用在group by 中，但是可以用在order by 中 如upSale*/
SELECT emp_id, sales, cust_id, sale升值后 = '升值后=', 'ckcs', (sales_amt * 10) AS upSale, (sales_amt - 10 * 0.5) AS testSale
FROM sales, employees
GROUP BY emp_id, sales, cust_id, sales_amt
ORDER BY emp_id DESC, upSale
SELECT emp_id
FROM employees
WHERE (emp_id< 5 AND NOT emp_id = '2') OR last_name = 'Green' /**//* not <> != */
SELECT emp_id, last_name, trainer, (sales * 10)
FROM employees
ORDER BY 1 ASC, 3 DESC, 4 DESC /**//*当某列不在表中的列（无名）的列*/
/**//*ORDER BY emp_id ASC, trainer DESC*/
USE sqlstudy
GO
SELECT last_name, CASE emp_id
WHEN 1 THEN 'good'
WHEN 2 THEN 'so good'
WHEN 3 THEN 'bad'
END AS 'check'
FROM employees
WHERE sales > 0
SELECT last_name, CASE WHEN sales = 2 THEN 'good'
WHEN emp_id = (SELECT emp_id FROM employees WHERE emp_id = 4) THEN 'bad'
WHEN trainer = 'Greg' THEN 'so good'
END AS 'check sales'
FROM employees
WHERE emp_id <> 5
USE sqlstudy
GO
UPDATE employees
SET sales = CASE WHEN emp_id = 1 THEN sales * 1.5
WHEN emp_id = 2 THEN sales * 2.5
WHEN emp_id = 3 THEN sales * 3.5
WHEN emp_id = 4 THEN sales * 4.5
ELSE sales * 5.5
END
WHERE emp_id <> 5
SELECT trainer, CASE WHEN sales <> 0 THEN 100/sales
ELSE '0'
END AS 'test sales'
FROM employees
SELECT trainer, CASE WHEN sales > 20 THEN sales - 20
ELSE '20'
END AS 'test sales'
FROM employ
USE sqlstudy
Go
/**//*nullif 在sales的值为6时返回null，否则返回sales的原值*/
SELECT last_name ,NULLIF(sales, 6) AS 'test sales'
FROM employees
/**//*isnull 在sales的值为null是返回6，否则返回sales的原值*/
SELECT last_name ,ISNULL(sales, 6) AS 'test sales'
FROM employees
USE sqlstudy
GO --coalesce(a, b, c, ..。)返回第一个不为null的值，当全为null的时候返回null值。
SELECT last_name, coalesce(sales, (SELECT MIN(sales) FROM employees), 0) AS 'test sales'
FROM employees
WHERE emp_id <> 4
ALTER TABLE employees ADD first_name char(10)
ALTER TABLE employees DROP COLUMN total_emp
UPDATE employees SET total_emp = (SELECT COUNT(DISTINCT sales) FROM employees) /**//*distinct 不可以和* 一起连用*/
SELECT COUNT('1') FROM employees
SELECT COUNT(first_name), COUNT(last_name),
COUNT(DISTINCT (first_name + last_name))
FROM employees
USE sqlstudy
GO
SELECT emp_id, trainer, SUM(sales) AS 'totalsales'
FROM employees
GROUP BY emp_id, trainer
WITH ROLLUP
ORDER BY emp_id
SELECT emp_id, trainer, SUM(sales) AS 'totalsales'
FROM employees
GROUP BY emp_id, trainer
WITH CUBE
ORDER BY emp_id
SELECT SUM(sales), SUM(DISTINCT sales)
FROM employees
SELECT AVG(sales) AS 'testsales', AVG(DISTINCT sales) AS 'unique testsales'
FROM employees
WHERE emp_id <> '10'
SELECT sales, COUNT(*)
FROM employees
GROUP BY sales
USE sqlstudy
![]()