select count(*),dept,sex from table group by dept,sex order by dept
select Deptcount=malecount+famalecount,maleCount,famaleCount form (select dept, maleCount=count(*) from table group by dept where sex='male') as a join (select dept, famaleCount=count(*) from table group by dept where sex='famale') as b on a.dept=b.dept以上语句应该可以实现你的要求
select sum(sex) 人数,sum(case sex when '男' then 1 esle 0) 男人数, sum(case sex when '女' then 1 esle 0) 女人数 from table_name group by dept
select dept 单位,sum(sex) 人数,sum(case sex when '男' then 1 esle 0) 男人数, sum(case sex when '女' then 1 esle 0) 女人数 from table_name group by dept
select dept,sum(case sex='man' then 1 else 0 end),sum(case sex='woman' then 1 else 0 end) from table group by dept
The CASE expression allows SQL expressions to be simplified for conditional values. The CASE expression in SQL Server 6.0 is ANSI SQL-92-compliant and allowed anywhere an expression is used. Syntax Simple CASE expression: CASE expression WHEN expression1 THEN expression1 [[WHEN expression2 THEN expression2] [...]] [ELSE expressionN] END Searched CASE expression: CASE WHEN Boolean_expression1 THEN expression1 [[WHEN Boolean_expression2 THEN expression2] [...]] [ELSE expressionN] END CASE-related functions: COALESCE (expression1, expression2) COALESCE (expression1, expression2, ...expressionN) NULLIF (expression1, expression2) where expression Is a constant, column name, function, subquery, and any combination of arithmetic, bitwise, and string operators. In a simple CASE expression, the first expression is compared to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned. Boolean_expression Determines whether to use the THEN clause. The searched CASE expression allows comparison operators, and the use of AND and/or OR between each Boolean_expression. A simple CASE expression does not. The simple CASE expression checks only for equivalent values. ELSE expressionN Specifies the result to be returned when all other WHEN clauses fail. ELSE expressionN is optional. When not included, if all other WHEN clauses fail, CASE returns NULL. COALESCE (expression1, expression2) Is equivalent to a searched CASE expression where a NOT NULL expression1 returns expression1 and a NULL expression1 returns expression2. In searched CASE expression form, it would look like this: CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END COALESCE (expression1, expression2, ... expressionN) Is equivalent to the COALESCE function where each of the expressions return that expression when the value is NOT NULL. A NULL expressionN will return the first non-null expressionN in the list. If no non-null values are found, CASE returns NULL. In searched CASE statement form, it would look like this:
id,name,sex,dept,age select dept,(select count(name) from tablename where dept=tb.dept),(select count(name) from tablename where dept=tb.dept and sex='男' and age>60), (select count(name) from tablename where dept=tb.dept and sex='女' and age>60), (select count(name) from tablename where dept=tb.dept and sex='男'), (select count(name) from tablename where dept=tb.dept and sex='女') from tablename tb
CASE WHEN value_expression1 IS NOT NULL THEN value_expression1 ELSE COALESCE(value_expression2, ... value_expressionN) END NULLIF (expression1, expression2) Is equivalent to a searched CASE expression where expression1 = expression2 and the resulting expression is NULL. In searched CASE expression form, it would look like this: CASE WHEN expression1=expression2 THEN NULL ELSE expression1 ENDRes All datatypes used for the replacement expression in the THEN clause must include compatible datatypes. This table shows compatible and resulting datatypes.
Datatypes in THEN expressions Resulting datatype Mixed. If the datatype used is not compatible (implicit conversion not supported by SQL Server), an error will occur. Combination of fixed-length char with lengths cl1, cl2, and cl3. Fixed-length char with length equal to the greater of cl1, cl2, and cl3. Combination of fixed-length binary with lengths bl1, bl2, and bl3. Fixed-length binary with length equal to the greater of bl1, bl2, and bl3. Combination of fixed and variable-length char. Variable-length char with length equal to the maximum-length expression. Combination of fixed and variable-length binary. Variable-length binary with length equal to the maximum-length expression. Combination of numeric datatypes (for example, smallint, int, float, money, numeric, and decimal). Datatype equal to the maximum precision expression. For example, if one expression resulted in an int and another in a float, the resulting datatype would be float, because float is more precise than int.Examples A. SELECT Statement with a Simple CASE Expression Within a SELECT statement, a simple CASE expression allows only an equality check; no other comparisons are made. In this example, the CASE expression is used to alter the display of book categories to make them more understandable. SELECT Category = CASE type WHEN 'popular_comp' THEN 'Popular Computing' WHEN 'mod_cook' THEN 'Modern Cooking' WHEN 'business' THEN 'Business' WHEN 'psychology' THEN 'Psychology' WHEN 'trad_cook' THEN 'Traditional Cooking' ELSE 'Not yet categorized' END, "Shortened Title" = CONVERT(varchar(30), title), Price = price FROM titles WHERE price IS NOT NULL ORDER BY type COMPUTE AVG(price) BY type goCategory Shortened Title Price ------------------- ------------------------------ ----------- Business Cooking with Computers: Surrep 11.95 Business Straight Talk About Computers 19.99 Business The Busy Executive's Database 19.99 Business You Can Combat Computer Stress 2.99 avg =========== 13.73Category Shortened Title Price ------------------- ------------------------------ ----------- Modern Cooking Silicon Valley Gastronomic Tre 19.99 Modern Cooking The Gourmet Microwave 2.99 avg =========== 11.49Category Shortened Title Price ------------------- ------------------------------ ----------- Popular Computing But Is It User Friendly? 22.95 Popular Computing Secrets of Silicon Valley 20.00 avg =========== 21.48Category Shortened Title Price ------------------- ------------------------------ ----------- Psychology Computer Phobic And Non-Phobic 21.59 Psychology Emotional Security: A New Algo 7.99 Psychology Is Anger the Enemy? 10.95 Psychology Life Without Fear 7.00 Psychology Prolonged Data Deprivation: Fo 19.99 avg =========== 13.50Category Shortened Title Price ------------------- ------------------------------ ----------- Traditional Cooking Fifty Years in Buckingham Pala 11.95 Traditional Cooking Onions, Leeks, and Garlic: Coo 20.95 Traditional Cooking Sushi, Anyone? 14.99 avg =========== 15.96(21 row(s) affected)B. SELECT Statement with Simple and Searched CASE Expressions Within a SELECT statement, the searched CASE expression allows values to be replaced in the results set based on comparison values. In this example, the price (a money column) is displayed as a text comment based on ranges of cost for the books. It is important that all possibilities are checked. SELECT "Price Category" = CASE WHEN price IS NULL THEN 'Not yet priced' WHEN price < 10 THEN 'Very Reasonable Title' WHEN price >= 10 and price < 20 THEN 'Coffee Table Title' ELSE 'Expensive book!' END, "Shortened Title" = CONVERT(varchar(20), title), Category = CASE type WHEN 'popular_comp' THEN 'Popular Computing' WHEN 'mod_cook' THEN 'Modern Cooking' WHEN 'business' THEN 'Business' WHEN 'psychology' THEN 'Psychology' WHEN 'trad_cook' THEN 'Traditional Cooking' ELSE 'Not yet categorized' END FROM titles ORDER BY price goPrice Category Shortened Title Category --------------------- -------------------- ------------------- Not yet priced Net Etiquette Popular Computing Not yet priced The Psychology of Co Not yet categorized Very Reasonable Title The Gourmet Microwav Modern Cooking Very Reasonable Title You Can Combat Compu Business Very Reasonable Title Life Without Fear Psychology Very Reasonable Title Emotional Security: Psychology Coffee Table Title Is Anger the Enemy? Psychology Coffee Table Title Cooking with Compute Business Coffee Table Title Fifty Years in Bucki Traditional Cooking Coffee Table Title Sushi, Anyone? Traditional Cooking Coffee Table Title Prolonged Data Depri Psychology Coffee Table Title Silicon Valley Gastr Modern Cooking Coffee Table Title Straight Talk About Business Coffee Table Title The Busy Executive's Business Expensive book! Secrets of Silicon V Popular Computing Expensive book! Onions, Leeks, and G Traditional Cooking Expensive book! Computer Phobic And Psychology Expensive book! But Is It User Frien Popular Computing(18 row(s) affected)C. SELECT Statement with Searched CASE Expressions and Nested Subquery Within a SELECT statement, the searched CASE expression allows values to be replaced in the results set based on comparison values. In this example, the royalty percentage is checked in a separate table to determine a royalty category for each author. SELECT "Author's Full Name" = CONVERT(varchar(25), (RTRIM(au_fname) ' ' RTRIM(au_lname))), "Shortened Title" = CONVERT(varchar(25), title), "Royalty Category" = CASE WHEN (SELECT AVG(royaltyper) FROM titleauthor ta WHERE t.title_id = ta.title_id) > 60 THEN 'High Royalty' WHEN (SELECT AVG(royaltyper) FROM titleauthor ta WHERE t.title_id = ta.title_id) BETWEEN 41 and 59 THEN 'Medium Royalty' ELSE 'Low Royalty' END FROM authors a, titles t, titleauthor ta WHERE a.au_id = ta.au_id AND ta.title_id = t.title_id ORDER BY "Royalty Category", "Author's Full Name" go
Author's Full Name Shortened Title Royalty Category ------------------------- ------------------------- ---------------- Akiko Yokomoto Sushi, Anyone? Low Royalty Burt Gringlesby Sushi, Anyone? Low Royalty Michael O'Leary Sushi, Anyone? Low Royalty Albert Ringer Life Without Fear High Royalty Charlene Locksley Emotional Security: A New High Royalty Charlene Locksley Net Etiquette High Royalty Cheryl Carson But Is It User Friendly? High Royalty Innes del Castillo Silicon Valley Gastronomi High Royalty Johnson White Prolonged Data Deprivatio High Royalty Marjorie Green You Can Combat Computer S High Royalty Reginald Blotchet-Halls Fifty Years in Buckingham High Royalty Sylvia Panteley Onions, Leeks, and Garlic High Royalty Abraham Bennet The Busy Executive's Data Medium Royalty Albert Ringer Is Anger the Enemy? Medium Royalty Ann Dull Secrets of Silicon Valley Medium Royalty Anne Ringer Is Anger the Enemy? Medium Royalty Anne Ringer The Gourmet Microwave Medium Royalty Livia Karsen Computer Phobic And Non-P Medium Royalty Marjorie Green The Busy Executive's Data Medium Royalty Michael O'Leary Cooking with Computers: S Medium Royalty Michel DeFrance The Gourmet Microwave Medium Royalty Sheryl Hunter Secrets of Silicon Valley Medium Royalty Stearns MacFeather Computer Phobic And Non-P Medium Royalty Stearns MacFeather Cooking with Computers: S Medium Royalty(25 row(s) affected)D. UPDATE Statement with CASE Expressions With an UPDATE statement and CASE expressions, it's easy to modify a significant number of rows based on multiple columns of conditional information. In this example, reviews have been turned in and salary increases are due. A review rating of 4 will double the worker's salary, 3 will increase it by 60 percent, 2 will increase it by 20 percent, and a rating lower than 2 results in no raise. Also, a raise will not be given if the employee has been at the company for less than 18 months. UPDATE employee_salaries SET salary = CASE WHEN (review = 4 AND (DATEDIFF(month, hire_date, GETDATE()) > 18)) THEN salary * 2 WHEN (review = 3 AND (DATEDIFF(month, hire_date, GETDATE()) > 18)) THEN salary * 1.6 WHEN (review = 2 AND (DATEDIFF(month, hire_date, GETDATE()) > 18)) THEN salary * 1.2 ELSE salary ENDE. CASE-related Function (COALESCE) In this example, a wages table is shown to include three columns with information about an employee's yearly wage: hourly_wage, salary, and commission. However, an employee receives only one type of pay. To determine the total amount paid to all employees, use the COALESCE function to receive only the non-null value found in hourly_wage, salary, and commission. CREATE TABLE wages ( emp_id tinyint identity, hourly_wage decimal NULL, salary decimal NULL, commission decimal NULL, num_sales tinyint NULL ) INSERT wages VALUES(10.00, NULL, NULL, NULL) INSERT wages VALUES(20.00, NULL, NULL, NULL) INSERT wages VALUES(30.00, NULL, NULL, NULL) INSERT wages VALUES(40.00, NULL, NULL, NULL) INSERT wages VALUES(NULL, 10000.00, NULL, NULL) INSERT wages VALUES(NULL, 20000.00, NULL, NULL) INSERT wages VALUES(NULL, 30000.00, NULL, NULL) INSERT wages VALUES(NULL, 40000.00, NULL, NULL) INSERT wages VALUES(NULL, NULL, 15000, 3) INSERT wages VALUES(NULL, NULL, 25000, 2) INSERT wages VALUES(NULL, NULL, 20000, 6) INSERT wages VALUES(NULL, NULL, 14000, 4) goSELECT "Total Salary" = CONVERT(money, (COALESCE(hourly_wage * 40 * 52, salary, commission * num_sales))) FROM wages goTotal Salary -------------------------- 20,800.00 41,600.00 62,400.00 83,200.00 10,000.00 20,000.00 30,000.00 40,000.00 45,000.00 50,000.00 120,000.00 56,000.00 (12 row(s) affected)F. CASE-related Functions (NULLIF and COALESCE) In this example, the budgets table is created to show a department (dept), its current budget (current_year) and its previous budget (previous_year). For the current year, NULL is used for departments whose budget will not change from the prior year, and 0 is used for budgets that have not yet been determined. To find out the average of only those departments that have received a budget as well as include the budget value from the prior year (use the previous_year value, where the current_year is 0), combine the NULLIF and COALESCE functions. CREATE TABLE budgets ( dept tinyint IDENTITY, current_year decimal NULL, previous_year decimal NULL ) INSERT budgets VALUES(100000, 150000) INSERT budgets VALUES(NULL, 300000) INSERT budgets VALUES(0, 100000) INSERT budgets VALUES(NULL, 150000) INSERT budgets VALUES(300000, 250000)SELECT "Average Budget" = AVG(NULLIF(COALESCE(current_year, previous_year), 0.00)) FROM budgets goAverage Budget ---------------------------------------- 212500.000000 (1 row(s) affected)See AlsoExpressions SELECT Search Conditions UPDATE 以上来自SQL6.5的Books Online
select dept 单位,sum(sex) 人数,sum(case when sex= '男' and 年林>35 then 1 esle 0 end) 男人数, sum(case when sex='女' and 年林>35 then 1 esle 0 end ) 女人数 from table_name group by dept
我给中文帮助 ---------------------- CASE 计算条件列表并返回多个可能结果表达式之一。 CASE 具有两种格式: 简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。 CASE 搜索函数计算一组布尔表达式以确定结果。 两种格式都支持可选的 ELSE 参数。 语法 简单 CASE 函数:CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ENDCASE 搜索函数:CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression END参数 input_expression是使用简单 CASE 格式时所计算的表达式。Input_expression 是任何有效的 Microsoft® SQL Server™ 表达式。 WHEN when_expression使用简单 CASE 格式时 input_expression 所比较的简单表达式。When_expression 是任意有效的 SQL Server 表达式。Input_expression 和每个 when_expression 的数据类型必须相同,或者是隐性转换。 n占位符,表明可以使用多个 WHEN when_expression THEN result_expression 子句或 WHEN Boolean_expression THEN result_expression 子句。THEN result_expression当 input_expression = when_expression 取值为 TRUE,或者 Boolean_expression 取值为 TRUE 时返回的表达式。result expression 是任意有效的 SQL Server 表达式。 ELSE else_result_expression当比较运算取值不为 TRUE 时返回的表达式。如果省略此参数并且比较运算取值不为 TRUE,CASE 将返回 NULL 值。Else_result_expression 是任意有效的 SQL Server 表达式。Else_result_expression 和所有 result_expression 的数据类型必须相同,或者必须是隐性转换。WHEN Boolean_expression使用 CASE 搜索格式时所计算的布尔表达式。Boolean_expression 是任意有效的布尔表达式。 结果类型 从 result_expressions 和可选 else_result_expression 的类型集合中返回最高的优先规则类型。有关更多信息,请参见数据类型的优先顺序。 结果值 简单 CASE 函数: 计算 input_expression,然后按指定顺序对每个 WHEN 子句的 input_expression = when_expression 进行计算。 返回第一个取值为 TRUE 的 (input_expression = when_expression) 的 result_expression。 如果没有取值为 TRUE 的 input_expression = when_expression,则当指定 ELSE 子句时 SQL Server 将返回 else_result_expression;若没有指定 ELSE 子句,则返回 NULL 值。 CASE 搜索函数: 按指定顺序为每个 WHEN 子句的 Boolean_expression 求值。 返回第一个取值为 TRUE 的 Boolean_expression 的 result_expression。 如果没有取值为 TRUE 的 Boolean_expression,则当指定 ELSE 子句时 SQL Server 将返回 else_result_expression;若没有指定 ELSE 子句,则返回 NULL 值。
select dept,sum(case sex when 'man' then 1 else 0 end),sum(case when 'woman' then 1 else 0 end) from table where age>60 group by dept
to : shibinx(逸云) 答案给你了,你都不看.. 用相关子查询,,基础版有好多这类的贴子 select dept,(select count(name) from tablename where dept=tb.dept),(select count(name) from tablename where dept=tb.dept and sex='男' and age>60), (select count(name) from tablename where dept=tb.dept and sex='女' and age>60), (select count(name) from tablename where dept=tb.dept and sex='男'), (select count(name) from tablename where dept=tb.dept and sex='女') from tablename tb
加上限定词,避免出现重复数据 select DISTINCT dept,(select count(name) from tablename where dept=tb.dept),(select count(name) from tablename where dept=tb.dept and sex='男' and age>60), (select count(name) from tablename where dept=tb.dept and sex='女' and age>60), (select count(name) from tablename where dept=tb.dept and sex='男'), (select count(name) from tablename where dept=tb.dept and sex='女') from tablename tb
shibinx(逸云) 把你写的贴出来
哦,是不是每个表的别名都是"tb"呢?tb这个别名是固定的呢还是其他
to shibinx(逸云) 别名是随便取的. 但在使用中必须一致(不能取了tb的别名用ta去引用它)
: xlhl(顽皮兔子) sum(case sex when '男' and age when '60' then 1 esle 0 end) 提示的是and 语法错了
: yonghengdizhen(秋叶映红了天) 别名是在写SQL时取的吗?
to shibinx(逸云) 你为什么总纠缠在那个什么case when上.. 他的有语法错误, case fieldname when xxx then ttt when yy then uuu else www end
SELECT "Author's Full Name" = CONVERT(varchar(25), (RTRIM(au_fname) ' ' RTRIM(au_lname))), "Shortened Title" = CONVERT(varchar(25), title), "Royalty Category" = CASE WHEN (SELECT AVG(royaltyper) FROM titleauthor ta WHERE t.title_id = ta.title_id) > 60 THEN 'High Royalty' WHEN (SELECT AVG(royaltyper) FROM titleauthor ta WHERE t.title_id = ta.title_id) BETWEEN 41 and 59 THEN 'Medium Royalty' ELSE 'Low Royalty' END FROM authors a, titles t, titleauthor ta WHERE a.au_id = ta.au_id AND ta.title_id = t.title_id ORDER BY "Royalty Category", "Author's Full Name" go 看例子套用
你把我的SQL考过去并把所有tablename换成你的表名就了OK了
to shibinx(逸云) 说了让你加限定词distinct呀
我来宣布最简明的方法是用cube或者rollup(看需要) select count(*) 人数 ,case when GROUPING( sex )=0 then sex else '男女共' end 性别, case when GROUPING( dept )=0 then dept else ‘所有单位' end 单位 from test_dept group by dept,sex with cube
sum(case sex when '男' and age when '60' then 1 esle 0 end) ---》 sum(case when sex= '男' and age = '60' then 1 esle 0 end)
group by dept,sex
order by dept
sum(case sex when '女' then 1 esle 0) 女人数 from table_name
group by dept
sum(case sex when '女' then 1 esle 0) 女人数 from table_name
group by dept
我试了一下:case不能用=,只能用when。
而且我想知道,case能不能有复合条件,比如性别是男而且年龄>60岁
case能不能有复合条件,比如性别是男而且年龄>60岁(如果在表中加个age字段),能否教我现在用case该怎么写呢?
Syntax
Simple CASE expression:
CASE expression
WHEN expression1 THEN expression1
[[WHEN expression2 THEN expression2] [...]]
[ELSE expressionN]
END
Searched CASE expression:
CASE
WHEN Boolean_expression1 THEN expression1
[[WHEN Boolean_expression2 THEN expression2] [...]]
[ELSE expressionN]
END
CASE-related functions:
COALESCE (expression1, expression2)
COALESCE (expression1, expression2, ...expressionN)
NULLIF (expression1, expression2)
where
expression
Is a constant, column name, function, subquery, and any combination of arithmetic, bitwise, and string operators.
In a simple CASE expression, the first expression is compared to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.
Boolean_expression
Determines whether to use the THEN clause. The searched CASE expression allows comparison operators, and the use of AND and/or OR between each Boolean_expression. A simple CASE expression does not. The simple CASE expression checks only for equivalent values.
ELSE expressionN
Specifies the result to be returned when all other WHEN clauses fail. ELSE expressionN is optional. When not included, if all other WHEN clauses fail, CASE returns NULL.
COALESCE (expression1, expression2)
Is equivalent to a searched CASE expression where a NOT NULL expression1 returns expression1 and a NULL expression1 returns expression2. In searched CASE expression form, it would look like this:
CASE
WHEN expression1 IS NOT NULL THEN expression1
ELSE expression2
END
COALESCE (expression1, expression2, ... expressionN)
Is equivalent to the COALESCE function where each of the expressions return that expression when the value is NOT NULL. A NULL expressionN will return the first non-null expressionN in the list. If no non-null values are found, CASE returns NULL. In searched CASE statement form, it would look like this:
select dept,(select count(name) from tablename where dept=tb.dept),(select count(name) from tablename where dept=tb.dept and sex='男' and age>60),
(select count(name) from tablename where dept=tb.dept and sex='女' and age>60),
(select count(name) from tablename where dept=tb.dept and sex='男'),
(select count(name) from tablename where dept=tb.dept and sex='女')
from tablename tb
WHEN value_expression1 IS NOT NULL THEN value_expression1
ELSE COALESCE(value_expression2, ... value_expressionN)
END
NULLIF (expression1, expression2)
Is equivalent to a searched CASE expression where expression1 = expression2 and the resulting expression is NULL. In searched CASE expression form, it would look like this:
CASE
WHEN expression1=expression2 THEN NULL
ELSE expression1
ENDRes
All datatypes used for the replacement expression in the THEN clause must include compatible datatypes. This table shows compatible and resulting datatypes.
Datatypes in THEN expressions Resulting datatype
Mixed. If the datatype used is not compatible (implicit conversion not supported by SQL Server), an error will occur.
Combination of fixed-length char with lengths cl1, cl2, and cl3. Fixed-length char with length equal to the greater of cl1, cl2, and cl3.
Combination of fixed-length binary with lengths bl1, bl2, and bl3. Fixed-length binary with length equal to the greater of bl1, bl2, and bl3.
Combination of fixed and variable-length char. Variable-length char with length equal to the maximum-length expression.
Combination of fixed and variable-length binary. Variable-length binary with length equal to the maximum-length expression.
Combination of numeric datatypes (for example, smallint, int, float, money, numeric, and decimal). Datatype equal to the maximum precision expression. For example, if one expression resulted in an int and another in a float, the resulting datatype would be float, because float is more precise than int.Examples
A. SELECT Statement with a Simple CASE Expression
Within a SELECT statement, a simple CASE expression allows only an equality check; no other comparisons are made. In this example, the CASE expression is used to alter the display of book categories to make them more understandable.
SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END,
"Shortened Title" = CONVERT(varchar(30), title),
Price = price
FROM titles
WHERE price IS NOT NULL
ORDER BY type
COMPUTE AVG(price) BY type
goCategory Shortened Title Price
------------------- ------------------------------ -----------
Business Cooking with Computers: Surrep 11.95
Business Straight Talk About Computers 19.99
Business The Busy Executive's Database 19.99
Business You Can Combat Computer Stress 2.99 avg
===========
13.73Category Shortened Title Price
------------------- ------------------------------ -----------
Modern Cooking Silicon Valley Gastronomic Tre 19.99
Modern Cooking The Gourmet Microwave 2.99 avg
===========
11.49Category Shortened Title Price
------------------- ------------------------------ -----------
Popular Computing But Is It User Friendly? 22.95
Popular Computing Secrets of Silicon Valley 20.00 avg
===========
21.48Category Shortened Title Price
------------------- ------------------------------ -----------
Psychology Computer Phobic And Non-Phobic 21.59
Psychology Emotional Security: A New Algo 7.99
Psychology Is Anger the Enemy? 10.95
Psychology Life Without Fear 7.00
Psychology Prolonged Data Deprivation: Fo 19.99 avg
===========
13.50Category Shortened Title Price
------------------- ------------------------------ -----------
Traditional Cooking Fifty Years in Buckingham Pala 11.95
Traditional Cooking Onions, Leeks, and Garlic: Coo 20.95
Traditional Cooking Sushi, Anyone? 14.99 avg
===========
15.96(21 row(s) affected)B. SELECT Statement with Simple and Searched CASE Expressions
Within a SELECT statement, the searched CASE expression allows values to be replaced in the results set based on comparison values. In this example, the price (a money column) is displayed as a text comment based on ranges of cost for the books. It is important that all possibilities are checked.
SELECT "Price Category" =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
"Shortened Title" = CONVERT(varchar(20), title),
Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END
FROM titles
ORDER BY price
goPrice Category Shortened Title Category
--------------------- -------------------- -------------------
Not yet priced Net Etiquette Popular Computing
Not yet priced The Psychology of Co Not yet categorized
Very Reasonable Title The Gourmet Microwav Modern Cooking
Very Reasonable Title You Can Combat Compu Business
Very Reasonable Title Life Without Fear Psychology
Very Reasonable Title Emotional Security: Psychology
Coffee Table Title Is Anger the Enemy? Psychology
Coffee Table Title Cooking with Compute Business
Coffee Table Title Fifty Years in Bucki Traditional Cooking
Coffee Table Title Sushi, Anyone? Traditional Cooking
Coffee Table Title Prolonged Data Depri Psychology
Coffee Table Title Silicon Valley Gastr Modern Cooking
Coffee Table Title Straight Talk About Business
Coffee Table Title The Busy Executive's Business
Expensive book! Secrets of Silicon V Popular Computing
Expensive book! Onions, Leeks, and G Traditional Cooking
Expensive book! Computer Phobic And Psychology
Expensive book! But Is It User Frien Popular Computing(18 row(s) affected)C. SELECT Statement with Searched CASE Expressions and Nested Subquery
Within a SELECT statement, the searched CASE expression allows values to be replaced in the results set based on comparison values. In this example, the royalty percentage is checked in a separate table to determine a royalty category for each author.
SELECT "Author's Full Name" = CONVERT(varchar(25), (RTRIM(au_fname)
' ' RTRIM(au_lname))),
"Shortened Title" = CONVERT(varchar(25), title),
"Royalty Category" =
CASE
WHEN (SELECT AVG(royaltyper) FROM titleauthor ta
WHERE t.title_id = ta.title_id) > 60
THEN 'High Royalty'
WHEN (SELECT AVG(royaltyper) FROM titleauthor ta
WHERE t.title_id = ta.title_id)
BETWEEN 41 and 59
THEN 'Medium Royalty'
ELSE 'Low Royalty'
END
FROM authors a, titles t, titleauthor ta
WHERE a.au_id = ta.au_id AND ta.title_id = t.title_id
ORDER BY "Royalty Category", "Author's Full Name"
go
------------------------- ------------------------- ----------------
Akiko Yokomoto Sushi, Anyone? Low Royalty
Burt Gringlesby Sushi, Anyone? Low Royalty
Michael O'Leary Sushi, Anyone? Low Royalty
Albert Ringer Life Without Fear High Royalty
Charlene Locksley Emotional Security: A New High Royalty
Charlene Locksley Net Etiquette High Royalty
Cheryl Carson But Is It User Friendly? High Royalty
Innes del Castillo Silicon Valley Gastronomi High Royalty
Johnson White Prolonged Data Deprivatio High Royalty
Marjorie Green You Can Combat Computer S High Royalty
Reginald Blotchet-Halls Fifty Years in Buckingham High Royalty
Sylvia Panteley Onions, Leeks, and Garlic High Royalty
Abraham Bennet The Busy Executive's Data Medium Royalty
Albert Ringer Is Anger the Enemy? Medium Royalty
Ann Dull Secrets of Silicon Valley Medium Royalty
Anne Ringer Is Anger the Enemy? Medium Royalty
Anne Ringer The Gourmet Microwave Medium Royalty
Livia Karsen Computer Phobic And Non-P Medium Royalty
Marjorie Green The Busy Executive's Data Medium Royalty
Michael O'Leary Cooking with Computers: S Medium Royalty
Michel DeFrance The Gourmet Microwave Medium Royalty
Sheryl Hunter Secrets of Silicon Valley Medium Royalty
Stearns MacFeather Computer Phobic And Non-P Medium Royalty
Stearns MacFeather Cooking with Computers: S Medium Royalty(25 row(s) affected)D. UPDATE Statement with CASE Expressions
With an UPDATE statement and CASE expressions, it's easy to modify a significant number of rows based on multiple columns of conditional information. In this example, reviews have been turned in and salary increases are due. A review rating of 4 will double the worker's salary, 3 will increase it by 60 percent, 2 will increase it by 20 percent, and a rating lower than 2 results in no raise. Also, a raise will not be given if the employee has been at the company for less than 18 months.
UPDATE employee_salaries
SET salary =
CASE
WHEN (review = 4 AND
(DATEDIFF(month, hire_date, GETDATE()) > 18))
THEN salary * 2
WHEN (review = 3 AND
(DATEDIFF(month, hire_date, GETDATE()) > 18))
THEN salary * 1.6
WHEN (review = 2 AND
(DATEDIFF(month, hire_date, GETDATE()) > 18))
THEN salary * 1.2
ELSE salary
ENDE. CASE-related Function (COALESCE)
In this example, a wages table is shown to include three columns with information about an employee's yearly wage: hourly_wage, salary, and commission. However, an employee receives only one type of pay. To determine the total amount paid to all employees, use the COALESCE function to receive only the non-null value found in hourly_wage, salary, and commission.
CREATE TABLE wages
(
emp_id tinyint identity,
hourly_wage decimal NULL,
salary decimal NULL,
commission decimal NULL,
num_sales tinyint NULL
)
INSERT wages VALUES(10.00, NULL, NULL, NULL)
INSERT wages VALUES(20.00, NULL, NULL, NULL)
INSERT wages VALUES(30.00, NULL, NULL, NULL)
INSERT wages VALUES(40.00, NULL, NULL, NULL)
INSERT wages VALUES(NULL, 10000.00, NULL, NULL)
INSERT wages VALUES(NULL, 20000.00, NULL, NULL)
INSERT wages VALUES(NULL, 30000.00, NULL, NULL)
INSERT wages VALUES(NULL, 40000.00, NULL, NULL)
INSERT wages VALUES(NULL, NULL, 15000, 3)
INSERT wages VALUES(NULL, NULL, 25000, 2)
INSERT wages VALUES(NULL, NULL, 20000, 6)
INSERT wages VALUES(NULL, NULL, 14000, 4)
goSELECT "Total Salary" = CONVERT(money,
(COALESCE(hourly_wage * 40 * 52,
salary,
commission * num_sales)))
FROM wages
goTotal Salary
--------------------------
20,800.00
41,600.00
62,400.00
83,200.00
10,000.00
20,000.00
30,000.00
40,000.00
45,000.00
50,000.00
120,000.00
56,000.00 (12 row(s) affected)F. CASE-related Functions (NULLIF and COALESCE)
In this example, the budgets table is created to show a department (dept), its current budget (current_year) and its previous budget (previous_year). For the current year, NULL is used for departments whose budget will not change from the prior year, and 0 is used for budgets that have not yet been determined. To find out the average of only those departments that have received a budget as well as include the budget value from the prior year (use the previous_year value, where the current_year is 0), combine the NULLIF and COALESCE functions.
CREATE TABLE budgets
(
dept tinyint IDENTITY,
current_year decimal NULL,
previous_year decimal NULL
)
INSERT budgets VALUES(100000, 150000)
INSERT budgets VALUES(NULL, 300000)
INSERT budgets VALUES(0, 100000)
INSERT budgets VALUES(NULL, 150000)
INSERT budgets VALUES(300000, 250000)SELECT "Average Budget" = AVG(NULLIF(COALESCE(current_year,
previous_year), 0.00))
FROM budgets
goAverage Budget
----------------------------------------
212500.000000 (1 row(s) affected)See AlsoExpressions SELECT
Search Conditions UPDATE 以上来自SQL6.5的Books Online
在表中加个age(年纪)字段,还要统计每个单位性别是男而且年龄>60岁的人数
sum(case when sex='女' and 年林>35 then 1 esle 0 end ) 女人数 from table_name
group by dept
----------------------
CASE
计算条件列表并返回多个可能结果表达式之一。 CASE 具有两种格式: 简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。
CASE 搜索函数计算一组布尔表达式以确定结果。
两种格式都支持可选的 ELSE 参数。 语法
简单 CASE 函数:CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
ENDCASE 搜索函数:CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
END参数
input_expression是使用简单 CASE 格式时所计算的表达式。Input_expression 是任何有效的 Microsoft® SQL Server™ 表达式。 WHEN when_expression使用简单 CASE 格式时 input_expression 所比较的简单表达式。When_expression 是任意有效的 SQL Server 表达式。Input_expression 和每个 when_expression 的数据类型必须相同,或者是隐性转换。 n占位符,表明可以使用多个 WHEN when_expression THEN result_expression 子句或 WHEN Boolean_expression THEN result_expression 子句。THEN result_expression当 input_expression = when_expression 取值为 TRUE,或者 Boolean_expression 取值为 TRUE 时返回的表达式。result expression 是任意有效的 SQL Server 表达式。 ELSE else_result_expression当比较运算取值不为 TRUE 时返回的表达式。如果省略此参数并且比较运算取值不为 TRUE,CASE 将返回 NULL 值。Else_result_expression 是任意有效的 SQL Server 表达式。Else_result_expression 和所有 result_expression 的数据类型必须相同,或者必须是隐性转换。WHEN Boolean_expression使用 CASE 搜索格式时所计算的布尔表达式。Boolean_expression 是任意有效的布尔表达式。 结果类型
从 result_expressions 和可选 else_result_expression 的类型集合中返回最高的优先规则类型。有关更多信息,请参见数据类型的优先顺序。 结果值
简单 CASE 函数:
计算 input_expression,然后按指定顺序对每个 WHEN 子句的 input_expression = when_expression 进行计算。
返回第一个取值为 TRUE 的 (input_expression = when_expression) 的 result_expression。
如果没有取值为 TRUE 的 input_expression = when_expression,则当指定 ELSE 子句时 SQL Server 将返回 else_result_expression;若没有指定 ELSE 子句,则返回 NULL 值。
CASE 搜索函数:
按指定顺序为每个 WHEN 子句的 Boolean_expression 求值。
返回第一个取值为 TRUE 的 Boolean_expression 的 result_expression。
如果没有取值为 TRUE 的 Boolean_expression,则当指定 ELSE 子句时 SQL Server 将返回 else_result_expression;若没有指定 ELSE 子句,则返回 NULL 值。
答案给你了,你都不看..
用相关子查询,,基础版有好多这类的贴子
select dept,(select count(name) from tablename where dept=tb.dept),(select count(name) from tablename where dept=tb.dept and sex='男' and age>60),
(select count(name) from tablename where dept=tb.dept and sex='女' and age>60),
(select count(name) from tablename where dept=tb.dept and sex='男'),
(select count(name) from tablename where dept=tb.dept and sex='女')
from tablename tb
: yonghengdizhen(秋叶映红了天) 不理解tb是什么意思。
不过这里的tablename必须换成你实际的表名,因为我不知道你的表名,只好自己取了一个名字.别名你就不用管了.
select DISTINCT dept,(select count(name) from tablename where dept=tb.dept),(select count(name) from tablename where dept=tb.dept and sex='男' and age>60),
(select count(name) from tablename where dept=tb.dept and sex='女' and age>60),
(select count(name) from tablename where dept=tb.dept and sex='男'),
(select count(name) from tablename where dept=tb.dept and sex='女')
from tablename tb
别名是随便取的.
但在使用中必须一致(不能取了tb的别名用ta去引用它)
sum(case sex when '男' and age when '60' then 1 esle 0 end)
提示的是and 语法错了
别名是在写SQL时取的吗?
你为什么总纠缠在那个什么case when上..
他的有语法错误,
case fieldname when xxx then ttt
when yy then uuu
else www
end
我试了,取别名的方法没有分组啊,而且稍显麻烦一点,不过还是有帮助的
' ' RTRIM(au_lname))),
"Shortened Title" = CONVERT(varchar(25), title),
"Royalty Category" =
CASE
WHEN (SELECT AVG(royaltyper) FROM titleauthor ta
WHERE t.title_id = ta.title_id) > 60
THEN 'High Royalty'
WHEN (SELECT AVG(royaltyper) FROM titleauthor ta
WHERE t.title_id = ta.title_id)
BETWEEN 41 and 59
THEN 'Medium Royalty'
ELSE 'Low Royalty'
END
FROM authors a, titles t, titleauthor ta
WHERE a.au_id = ta.au_id AND ta.title_id = t.title_id
ORDER BY "Royalty Category", "Author's Full Name"
go
看例子套用
说了让你加限定词distinct呀
select count(*) 人数 ,case when GROUPING( sex )=0 then sex else '男女共' end 性别,
case when GROUPING( dept )=0 then dept else ‘所有单位' end 单位 from test_dept group by dept,sex with cube
---》
sum(case when sex= '男' and age = '60' then 1 esle 0 end)
简单啊!!