/*
for SQL SERVER
我没有调试过,听说有人已经通过
CASE [ < input_expression>] WHEN < when_expression> THEN [ELSE < else_expression>] END
*/select
ksxxbh,
xslsh,
SUM(CASE kmmc WHEN '语文' THEN dkcj ELSE 0 END) AS 语文,
SUM(CASE kmmc WHEN '数学' THEN dkcj ELSE 0 END) AS 数学,
SUM(CASE kmmc WHEN '英语' THEN dkcj ELSE 0 END) AS 英语,
SUM(CASE kmmc WHEN '物理' THEN dkcj ELSE 0 END) AS 物理,
SUM(语文+数学+英语+物理) / 4 AS 平均分,
SUM(语文+数学+英语+物理) AS 总成绩
from WSH_CJGL_XSDKCJ
group by ksxxbh, xslsh
for SQL SERVER
我没有调试过,听说有人已经通过
CASE [ < input_expression>] WHEN < when_expression> THEN [ELSE < else_expression>] END
*/select
ksxxbh,
xslsh,
SUM(CASE kmmc WHEN '语文' THEN dkcj ELSE 0 END) AS 语文,
SUM(CASE kmmc WHEN '数学' THEN dkcj ELSE 0 END) AS 数学,
SUM(CASE kmmc WHEN '英语' THEN dkcj ELSE 0 END) AS 英语,
SUM(CASE kmmc WHEN '物理' THEN dkcj ELSE 0 END) AS 物理,
SUM(语文+数学+英语+物理) / 4 AS 平均分,
SUM(语文+数学+英语+物理) AS 总成绩
from WSH_CJGL_XSDKCJ
group by ksxxbh, xslsh
解决方案 »
- 急,FastReport 中richtext内容显示不全?只有特定的字号能显示
- 请教日期相差计算
- "不正常的定义了参数对象.提供了不一致或不完整的信息",这是为什么呢?
- 如何绘制类似于Windows开始菜单的菜单
- 客户端如何知道服务器端已经断开连接(TTcpServer和TTcpClient)
- listview控件 有什么办法让它的item排成多列而不是一列
- 什么Delphi杂志比较好?
- Delphi动态调用dll返回字符串为乱码
- 关于Delphi能不能写一个asp的服务端疑问?
- 在Table中用filter过滤,如何使两个过滤条件结合起来???
- 最近上网用QQ聊天总是被人炸掉线或死机,我还是菜鸟,想知道用delphi是怎么实现的
- 在Delphi中如何处理文件名和扩展名的8+3问题。
然后用SQL来查询
select * from tablename order by 要排序的字段名
我用interbase数据库,执行sql时报错,
报错信息如下:
(Dynamic SQL Error
SQL error code = -104
Token unknown - line 4, char 10
KMMC
Statement: select
ksxxbh,
xslsh,
SUM(CASE KMMC WHEN '语文' THEN dkcj ELSE 0 END) AS 语文,
SUM(CASE kmmc WHEN '数学' THEN dkcj ELSE 0 END) AS 数学,
SUM(CASE kmmc WHEN '英语' THEN dkcj ELSE 0 END) AS 英语,
SUM(CASE kmmc WHEN '物理' THEN dkcj ELSE 0 END) AS 物理,
SUM(语文+数学+英语+物理) / 4 AS 平均分,
SUM(语文+数学+英语+物理) AS 总成绩
from WSH_CJGL_XSDKCJ
group by ksxxbh, xslsh)。
请问您用的是什么数据库??
for SQL SERVER
for SQL SERVER
for SQL SERVER你看IB里有没有象IIf这样的函数,再看看有没有CASE这样的语法
把说明复制出来,大家帮你看看,我安装没有InterBase :(
谢谢,interbase好像没有IIf函数和CASE这样的语法。
但公司要求必须用interbase,希望继续得到您的帮助。
Retrieves data from one or more tables. Available in SQL, DSQL, and
isql.Syntax SELECT [TRANSACTION transaction]
[DISTINCT | ALL]
{* | <val> [, <val> 匽}
[INTO :var [, :var 匽]
FROM <tableref> [, <tableref> 匽
[WHERE <search_condition>]
[GROUP BY col [COLLATE collation] [, col [COLLATE collation] 匽
[HAVING <search_condition>]
[UNION <select_expr> [ALL]]
[PLAN <plan_expr>]
[ORDER BY <order_list>]
[FOR UPDATE [OF col [, col 匽]];<val> = {
col [<array_dim>] | :variable
| <constant> | <expr> | <function>
| udf ([<val> [, <val> 匽])
| NULL | USER | RDB$DB_KEY | ?
} [COLLATE collation] [AS alias]
<array_dim> = [[x:]y [, [x:]y 匽]
<constant> = num | 'string' | charsetname 'string'
<function> = COUNT (* | [ALL] <val> | DISTINCT <val>)
| SUM ([ALL] <val> | DISTINCT <val>)
| AVG ([ALL] <val> | DISTINCT <val>)
| MAX ([ALL] <val> | DISTINCT <val>)
| MIN ([ALL] <val> | DISTINCT <val>)
| CAST (<val> AS <datatype>)
| UPPER (<val>)
| GEN_ID (generator, <val>)<tableref> = <joined_table> | table | view | procedure
[(<val> [, <val> 匽)] [alias]
<joined_table> = <tableref> <join_type> JOIN <tableref>
ON <search_condition> | (<joined_table>)
<join_type> = [INNER] JOIN
| {LEFT | RIGHT | FULL } [OUTER]} JOIN<search_condition> = <val> <operator> {<val> | (<select_one
>)}
| <val> [NOT] BETWEEN <val> AND <val>
| <val> [NOT] LIKE <val> [ESCAPE <val>]
| <val> [NOT] IN (<val> [, <val> 匽 | <select_list>)
| <val> IS [NOT] NULL
| <val> {>= | <=}
| <val> [NOT] {= | < | >}
| {ALL | SOME | ANY} (<select_list>)
| EXISTS (<select_expr>) | SINGULAR (<select_expr>)
| <val> [NOT] CONTAINING <val>
| <val> [NOT] STARTING [WITH] <val>
| (<search_condition>)
| NOT <search_condition>
| <search_condition> OR <search_condition> | <search_condition> AND <search_condition>
<operator> = {= | < | > | <= | >= | !< | !> | <> | !=}
<plan_expr> =
[JOIN | [SORT] [MERGE]] ({<plan_item> | <plan_expr>}
[, {<plan_item> | <plan_expr>} 匽)<plan_item> = {table | alias} {NATURAL | INDEX (<index> [, <index> 匽)| ORDER <index>}
<order_list> =
{col | int} [COLLATE collation]
[ASC[ENDING] | DESC[ENDING]]
[, <order_list> 匽Argument Description
expr A valid SQL expression that results in a single value
select_one A SELECT on a single column that returns exactly one value
select_list A SELECT on a single column that returns zero or more rows
select_expr A SELECT on a list of values that returns zero or more rows
?
n In DSQL and isql, val cannot be a variable.
n You cannot specify a COLLATE clause for Blob columns.Important In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.Argument Description
TRANSACTION transaction Name of the transaction under control of which the statement is executed; SQL only
SELECT [DISTINCT | ALL] Specifies data to retrieve. DISTINCT prevents duplicate values from being returned. ALL, the default, retrieves every value
{*|val [, val 匽} The asterisk (*) retrieves all columns for the specified tablesval [, val 匽 retrieves a list of specified columns, values, and expressions
INTO :var [, var 匽 Singleton select in embedded SQL only; specifies a list of host-language variables into which to retrieve values
FROM tableref
[, tableref 匽 List of tables, views, and stored procedures from which to retrieve data; list can include joins and joins can be nested
table Name of an existing table in a database
view Name of an existing view in a database
procedure Name of an existing stored procedure that functions like a SELECT statement
alias Brief, alternate name for a table, view, or column; after declaration in tableref, alias can stand in for subsequent references to a table or view
joined_table A table reference consisting of a JOIN
join_type Type of join to perform. Default: INNER
WHERE search_condition Specifies a condition that limits rows retrieved to a subset of all available rows
GROUP BY col [, col 匽 Partitions the results of a query into groups containing all rows with identical values based on a column list
COLLATE collation Specifies the collation order for the data retrieved by the query
HAVING search_condition Used with GROUP BY; specifies a condition that limits grouped rows returned
UNION [ALL] Combines two or more tables that are fully or partially identical in structure; the ALL option keeps identical rows separate instead of folding them together into one
PLAN plan_expr Specifies the access plan for the InterBase optimizer to use during retrieval
plan_item Specifies a table and index method for a plan
ORDER BY order_list Specifies columns to order, either by column name or ordinal number in the query, and the order (ASC or DESC) in which rows to return the rows
?Description SELECT retrieves data from tables, views, or stored procedures. Variations of the SELECT statement make it possible to:n Retrieve a single row, or part of a row, from a table. This operation is referred to as a singleton select.In embedded applications, all SELECT statements that occur outside the context of a cursor must be singleton selects.n Retrieve multiple rows, or parts of rows, from a table.In embedded applications, multiple row retrieval is accomplished by embedding a SELECT within a DECLARE CURSOR statement.
In isql, SELECT can be used directly to retrieve multiple rows.n Retrieve related rows, or parts of rows, from a join of two or more tables.
n Retrieve all rows, or parts of rows, from union of two or more tables.All SELECT statements consist of two required clauses (SELECT, FROM), and possibly others (INTO, WHERE, GROUP BY, HAVING, UNION, PLAN, ORDER BY
). The following table explains the purpose of each clause, and when they are required:TABLE 6 SELECT statement clauses?Clause Purpose Singleton SELECT Multi-row SELECT
SELECT Lists columns to retrieve Required Required
INTO Lists host variables for storing retrieved columns Required Not allowed
FROM Identifies the tables to search for values Required Required
WHERE Specifies the search conditions used to restrict retrieved rows to a subset of all available rows; a WHERE clause can contain its own SELECT statement, referred to as a subquery Optional Optional
GROUP BY Groups related rows based on common column values; used in conjunction with HAVING Optional Optional
HAVING Restricts rows generated by GROUP BY to a subset of those rows Optional Optional
UNION Combines the results of two or more SELECT statements to produce a single, dynamic table without duplicate rows Optional Optional
ORDER BY Specifies which columns to order, either by column name or by ordinal number in the query, and the sort order of rows returned: ascending (ASC) [default] or descending (DESC) Optional Optional
PLAN Specifies the query plan that should be used by the query optimizer instead of one it would normally choose Optional Optional
FOR UPDATE Specifies columns listed after the SELECT clause of a DECLARE CURSOR statement that can be updated using a WHERE CURRENT OF clause ? Optional
?Because SELECT is such a ubiquitous and complex statement, a meaningful discussion lies outside the scope of this reference. To learn how to use SELECT in isql, see the Operations Guide. For a complete explanation of SELECT and its clauses, see the Embedded SQL Guide.