扫二维码下载作业帮
拍照搜题，秒出答案，一键查看所有搜题记录
下载作业帮安装包
扫二维码下载作业帮
拍照搜题，秒出答案，一键查看所有搜题记录
求数据库SQL语句。表有A、B、C三个字段，把A字段相同数据中B字段，当C字段为1相加，当C字段为2时相减。A
2结果-----------------------
|按A字段中值相同的分组，将C字段为1的所有B字段的值的和，然后减去C字段为2的所有B字段的值的和表：
2结果-----------------------
SUM（B）（当C=1时）— SUM（B ）（当C=2时）　|
0 　　　　　　　　　　　　　　　　　　　　　　　 |
1 　　　　　　　　　　　　　　　　　　　　　　　 |
作业帮用户
扫二维码下载作业帮
拍照搜题，秒出答案，一键查看所有搜题记录
oracle 的语法：select A, sum(N) as B from (select A,
(case C when 1 then B
when 2 then -1 * B
as N from t
tt group by A问题很BT，也不知道怎么整出来的设计！！！
为您推荐：
扫描下载二维码SQL 2000要从一个表中找出A字段相等B字段不相等的语句怎么写【sql吧】_百度贴吧
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&签到排名：今日本吧第个签到，本吧因你更精彩，明天继续来努力！
本吧签到人数：0成为超级会员，使用一键签到本月漏签0次！成为超级会员，赠送8张补签卡连续签到：天&&累计签到：天超级会员单次开通12个月以上，赠送连续签到卡3张
关注：44,748贴子：
SQL 2000要从一个表中找出A字段相等B字段不相等的语句怎么写收藏
SQL 2000要从一个表中找出A字段相等B字段不相等的语句怎么写
sql数据库入门,江苏Oracle授权培训合作伙伴,OCM讲师亲授,真实企业项目实战教学+考前辅导,考试通过率全国领先!&详情咨询&
NB正解如下：select A,B ,
count(distinct B) from tmp1 t1 where t1.A = t2.A ) cntfrom tmp1 t2where (select
count(distinct B) from tmp1 t1 where t1.A = t2.A ) &1
Select *From table1 t1where exists(Select top 1 1 From table1 Where A = t1.A and B &&t1.B)
登录百度帐号在一个SQL SERVER从Oracle同步数据的案例中发现：
SQL Server数据库任务中错误提示如下:
OLE DB 提供程序 'MSDAORA' 为列提供的元数据不一致。执行时更改了元数据信息。 [SQLSTATE 42000]（错误 7356）& OLE DB 错误跟踪［Non-interface error:& Column 'STOTAL' (compile-time ordinal 6) of object '"USR01"."T_SALE"' was reported to have a DBTYPE of 131 at compile time and 130 at run time］。
查询分析器执行语句select * from ORCL..USR01.T_SALE测试,错误提示如下:
服务器: 消息 7356，级别 16，状态 1，行 1
OLE DB 提供程序 'MSDAORA' 为列提供的元数据不一致。执行时更改了元数据信息。
OLE DB 错误跟踪［Non-interface error:& Column 'STOTAL' (compile-time ordinal 6) of object '"USR01"."T_SALE"' was reported to have a DBTYPE of 131 at compile time and 130 at run time］。
考虑到Oracle数据库使用的是11g版本,可能使用微软MSDAORA驱动会不兼容,所以,改为使用Oracle公司的驱动再进行测试.
SQL Server数据库任务中错误提示如下:
OLE DB 提供程序 'OraOLEDB.Oracle' 为列提供的元数据不一致。执行时更改了元数据信息。 [SQLSTATE 42000]（错误 7356）& OLE DB 错误跟踪［Non-interface error:& Column 'STOTAL' (compile-time ordinal 6) of object '"USR01"."T_SALE"' was reported to have a DBTYPE of 131 at compile time and 130 at run time］。
查询分析器执行语句select * from ORCL..USR01.T_SALE测试,错误提示如下:
服务器: 消息 7356，级别 16，状态 1，行 1
OLE DB 提供程序 'OraOLEDB.Oracle' 为列提供的元数据不一致。执行时更改了元数据信息。
OLE DB 错误跟踪［Non-interface error:& Column 'STOTAL' (compile-time ordinal 6) of object '"USR01"."T_SALE"' was reported to have a DBTYPE of 131 at compile time and 130 at run time］。
最终查明初步的原因是:
Oracle11g数据库中,T_SALE表的STOTAL字段不是初始建表即有的,而是后来新增的字段.
联想到Oracle11g能够增强性能的一个新特性:在添加一个包含DEFAULT值的NOT NULL字段，Oracle不会去更新现有的数据，Oracle是将默认值以及对应的表信息、列信息一起存储在一个新增数据字典表ecol$中。
估计跨库取数据字段的数据类型受此新特性影响了.
根据本问题所做的测试来看,无论是使用Microsoft的驱动连接oracle库,还是使用Oracle的驱动来创建链接服务器连接oracle库,都存在此问题.
看来正如同《基督山伯爵》中伯爵所说的：永远不要忘记,直到上帝揭露人的未来图景的那一天以前,人类的一切智慧就包含在这四个字里面!"等待""希望".
目前临时解决此问题所用的方法是:
一是将oracle新增字段的表数据导出,重建表,让其建表即包含所有字段,避免新增字段,导入数据；
二是在oracle库中中建一个视图，不包含新增的字段，然后过程取视图。
&&&回复&&&：
北京盛拓优讯信息技术有限公司. 版权所有 京ICP备号 北京市公安局海淀分局网监中心备案编号：10
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员From Wikipedia, the free encyclopedia
SQL ( ( ) S-Q-L,
"sequel"; Structured Query Language) is a
used in programming and designed for managing data held in a
(RDBMS), or for stream processing in a
(RDSMS). It is particularly useful in handling
where there are relations between different entities/variables of the data. SQL offers two main advantages over older read/write
or : first, it introduced the concept of accessing many records wit and second, it eliminates the need to specify how to reach a record, e.g. with or without an .
Originally based upon
and , SQL consists of many types of statements, which may be informally classed as , commonly: a
(DCL), and a
(DML). The scope of SQL includes data query, data manipulation (insert, update and delete), data definition ( creation and modification), and data access control. Although SQL is often described as, and to a great extent is, a
(), it also includes
SQL was one of the first commercial languages for 's , as described in his influential 1970 paper, "A Relational Model of Data for Large Shared Data Banks".
Despite not entirely adhering to , it became the most widely used database language.
SQL became a
(ANSI) in 1986, and of the
(ISO) in 1987. Since then, the standard has been revised to include a larger set of features. Despite the existence of such standards, most SQL code is not completely portable among different database systems without adjustments.
SQL was initially developed at
after learning about the relational model from Ted Codd in the early 1970s. This version, initially called SEQUEL (Structured English Query Language), was designed to manipulate and retrieve data stored in IBM's original quasi-relational database management system, , which a group at
had developed during the 1970s.
Chamberlin and Boyce's first attempt of a relational database language was Square, but it was difficult to use due to subscript notation. After moving to the San Jose Research Laboratory in 1973, they began work on SEQUEL. The acronym SEQUEL was later changed to SQL because "SEQUEL" was a
aircraft company.
In the late 1970s, Relational Software, Inc. (now ) saw the potential of the concepts described by Codd, Chamberlin, and Boyce, and developed their own SQL-based
with aspirations of selling it to the , , and other
agencies. In June 1979, Relational Software, Inc. introduced the first commercially available implementation of SQL,
V2 (Version2) for
computers. By 1986,
standard groups officially adopted the standard "Database Language SQL" language definition. New versions of the standard were published in , , , , and most recently, 2016.
After testing SQL at customer test sites to determine the usefulness and practicality of the system, IBM began developing commercial products based on their System R prototype including , , and , which were commercially available in , and 1983, respectively.
SQL deviates in several ways from its theoretical foundation, the
In that model, a table is a
of tuples, while in SQL, tables and query results are
of rows: the same row may occur multiple times, and the order of rows can be employed in queries (e.g. in the LIMIT clause).
Critics argue that SQL should be replaced with a language that strictly returns to the original foundation: for example, see .
{\displaystyle \left.{\begin{array}{rl}\textstyle {\mathtt {UPDATE~clause}}&\{{\mathtt {UPDATE\ country}}\\\textstyle {\mathtt {SET~clause}}&\{{\mathtt {SET\ population=~}}\overbrace {\mathtt {population+1}} ^{\mathtt {expression}}\\\textstyle {\mathtt {WHERE~clause}}&\{{\mathtt {WHERE\ \underbrace {{name=}\overbrace {'USA'} ^{expression}} _{predicate};}}\end{array}}\right\}{\textstyle {\texttt {statement}}}}
A chart showing several of the SQL language elements that compose a single statement
The SQL language is subdivided into several language elements, including:
Clauses, which are constituent components of statements and queries. (In some cases, these are optional.)
Expressions, which can produce either
values, or
consisting of
Predicates, which specify conditions that can be evaluated to SQL
(true/false/unknown) or
and are used to limit the effects of statements and queries, or to change program flow.
Queries, which retrieve the data based on specific criteria. This is an important element of SQL.
Statements, which may have a persistent effect on schemata and data, or may control , program flow, connections, sessions, or diagnostics.
SQL statements also include the
(";") statement terminator. Though not required on every platform, it is defined as a standard part of the SQL grammar.
is generally ignored in SQL statements and queries, making it easier to format SQL code for readability.
SQL is designed for a specific purpose: to query
contained in a . SQL is a -based, , not an
or . However, extensions to Standard SQL add
functionality, such as control-of-flow constructs. These include:
Common name
ANSI/ISO Standard
SQL/Persistent Stored Modules
Procedural SQL
SQL Procedural Language (implements SQL/PSM)
Stored Procedural Language
(based on Postgres PL/pgSQL)
Invantive Procedural SQL (implements
Transact-SQL
SQL/Persistent Stored Module (implements SQL/PSM)
SQL/Persistent Stored Module (implements SQL/PSM)
SQL/Persistent Stored Module (implements SQL/PSM)
Starkey Stored Procedures
Procedural Language/SQL (based on )
Procedural Language/PostgreSQL Structured Query Language (implements SQL/PSM)
Advanced Business Application Programming
Watcom-SQL
SQL Anywhere Watcom-SQL Dialect
Stored Procedural Language
In addition to the standard SQL/PSM extensions and proprietary SQL extensions, procedural and
programmability is available on many SQL platforms via DBMS integration with other languages. The SQL standard defines
extensions (SQL Routines and Types for the Java Programming Language) to support
code in SQL databases.
(SQL Server Common Language Runtime) to host managed
assemblies in the database, while prior versions of SQL Server were restricted to unmanaged extended stored procedures primarily written in C.
lets users write functions in a wide variety of languages—including , , ,
(PL/V8) and C.
SQL implementations are incompatible between vendors and do not necessarily completely follow standards. In particular date and time syntax, string concatenation, NULLs, and comparison
vary from vendor to vendor. Particular exceptions are
who strive for standards compliance.
Popular implementations of SQL commonly omit support for basic features of Standard SQL, such as the DATE or TIME data types.
The most obvious such examples, and incidentally the most popular commercial and proprietary SQL DBMSs, are Oracle (whose DATE behaves as DATETIME, and lacks a TIME type) and MS SQL Server (before the 2008 version). As a result, SQL code can rarely be ported between database systems without modifications.
There are several reasons for this lack of portability between database systems:
The complexity and size of the SQL standard means that most implementors do not support the entire standard.
The standard does not specify database behavior in several important areas (e.g. , file storage...), leaving implementations to decide how to behave.
The SQL standard precisely specifies the syntax that a conforming database system must implement. However, the standard's specification of the semantics of language constructs is less well-defined, leading to ambiguity.
Many database vendors have large exi where the newer version of the SQL standard conflicts with the prior behavior of the vendor's database, the vendor may be unwilling to break .
There is little commercial incentive for vendors to make it easier for users to change database suppliers (see ).
Users evaluating database software tend to place other factors such as performance higher in their priorities than standards conformance.
SQL was adopted as a standard by the
(ANSI) in 1986 as SQL-86 and the
(ISO) in 1987. It is maintained by . The standard is commonly denoted by the pattern: ISO/IEC 9075-n:yyyy Part n: title, or, as a shortcut, ISO/IEC 9075.
ISO/IEC 9075 is complemented by ISO/IEC 13249: SQL Multimedia and Application Packages (SQL/MM), which defines SQL based interfaces and packages to widely spread applications like video, audio and .
Until 1996, the
(NIST) data management standards program certified SQL DBMS compliance with the SQL standard. Vendors now self-certify the compliance of their products.
The original standard declared that the official pronunciation for "SQL" was an :
("ess cue el"). Regardless, many English-speaking database professionals (including
himself) use the -like pronunciation of
("sequel"), mirroring the language's pre-release development name of "SEQUEL". The SQL standard has gone through a number of revisions:
First formalized by ANSI.
Minor revision that added integrity constraints, adopted as FIPS 127-1.
SQL2, FIPS 127-2
Major revision (ISO 9075), Entry Level SQL-92 adopted as FIPS 127-2.
Added regular expression matching,
(e.g. ), , support for procedural and control-of-flow statements, non-scalar types (arrays), and some object-oriented features (e.g. ). Support for embedding SQL in Java () and vice versa ().
Introduced -related features (), window functions, standardized sequences, and columns with auto-generated values (including identity-columns).
ISO/IEC 6 defines ways that SQL can be used with XML. It defines ways of importing and storing XML data in an SQL database, manipulating it within the database, and publishing both XML and conventional SQL-data in XML form. In addition, it lets applications integrate queries into their SQL code with , the XML Query Language published by the World Wide Web Consortium (), to concurrently access ordinary SQL-data and XML documents.
Legalizes ORDER BY outside cursor definitions. Adds INSTEAD OF triggers, TRUNCATE statement, FETCH clause.
Adds temporal data (PERIOD FOR) (more information at: ). Enhancements for window functions and FETCH clause.
Adds row pattern matching, polymorphic table functions, JSON.
Interested parties may purchase SQL standards documents from ISO, IEC or ANSI. A draft of SQL:2008 is freely available as a
The SQL standard is divided into nine parts.
ISO/IEC 6 Part 1: Framework (SQL/Framework). It provides logical concepts.
ISO/IEC 6 Part 2: Foundation (SQL/Foundation). It contains the most central elements of the language and consists of both mandatory and optional features.
ISO/IEC 6 Part 3: Call-Level Interface (). It defines interfacing components (structures, procedures, variable bindings) that can be used to execute SQL statements from applications written in Ada, C respectively C++, COBOL, Fortran, MUMPS, Pascal or PL/I. (For Java see part 10.) SQL/CLI is defined in such a way that SQL statements and SQL/CLI procedure calls are treated as separate from the calling application's source code.
is a well-known superset of SQL/CLI. This part of the standard consists solely of mandatory features.
ISO/IEC 6 Part 4: Persistent stored modules (). It standardizes procedural extensions for SQL, including flow of control, condition handling, statement condition signals and resignals, cursors and local variables, and assignment of expressions to variables and parameters. In addition, SQL/PSM formalizes declaration and maintenance of persistent database language routines (e.g., "stored procedures"). This part of the standard consists solely of optional features.
: Support for JavaScript Object Notation (JSON). In 2017 ISO/IEC published a first technical report about the effort to integrate the data type JSON into the SQL standard. Please consider that technical reports reflects the current state of the discussion and are not part of the standard.
ISO/IEC 6 Part 9: Management of External Data (). It provides extensions to SQL that define foreign-data wrappers and datalink types to allow SQL to manage external data. External data is data that is accessible to, but not managed by, an SQL-based DBMS. This part of the standard consists solely of optional features.
ISO/IEC 6 Part 10: Object language bindings (). It defines the syntax and semantics of , which is SQL embedded in Java (see also part 3). The standard also describes mechanisms to ensure binary portability of SQLJ applications, and specifies various Java packages and their contained classes. This part of the standard consists solely of optional features. Unlike SQL/OLB
defines an
and is not part of the SQL standard.[]
ISO/IEC 6 Part 11: Information and definition schemas (). It defines the Information Schema and Definition Schema, providing a common set of tools to make SQL databases and objects self-describing. These tools include the SQL object identifier, structure and integrity constraints, security and authorization specifications, features and packages of ISO/IEC 9075, support of features provided by SQL-based DBMS implementations, SQL-based DBMS implementation information and sizing items, and the values supported by the DBMS implementations. This part of the standard contains both mandatory and optional features.
ISO/IEC 6 Part 13: SQL Routines and types using the Java TM programming language (). It specifies the ability to invoke static Java methods as routines from within SQL applications ('Java-in-the-database'). It also calls for the ability to use Java classes as SQL structured user-defined types. This part of the standard consists solely of optional features.
ISO/IEC 6 Part 14: XML-Related Specifications (). It specifies SQL-based extensions for using XML in conjunction with SQL. The XML data type is introduced, as well as several routines, functions, and XML-to-SQL data type mappings to support manipulation and storage of XML in an SQL database. This part of the standard consists solely of optional features.[]
ISO/IEC 9075 is complemented by ISO/IEC 13249 SQL Multimedia and Application Packages. This closely related but separate standard is developed by the same committee. It defines interfaces and packages based on SQL. The aim is a unified access to typical database applications like text, pictures, data mining or .
ISO/IEC 6 Part 1: Framework
ISO/IEC 3 Part 2: Full-Text
ISO/IEC 6 Part 3: Spatial
ISO/IEC 3 Part 5: Still image
ISO/IEC 6 Part 6: Data mining
ISO/IEC 3 Part 7: History
ISO/IEC 13249-8:xxxx Part 8: Metadata Registry Access
(work in progress)
A distinction should be made between alternatives to SQL as a language, and alternatives to the relational model itself.
Below are proposed relational alternatives to the SQL language.
for alternatives to the relational model.
: object-oriented Datalog
: a superset that compiles down to SQL
: critics suggest that
has two advantages over SQL: it has cleaner semantics, which facilitates program understanding and maintenance, and it is more expressive, in particular for recursive queries.
: URL based query method
(IBM BS12): one of the first fully relational database management systems, introduced in 1982
: SQL implemented in Java as an
(JPQL): The query language used by the Java Persistence API and
persistence library
implements its query language is a JavaScript API.
: Runs SQL statements written like language constructs to query collections directly from inside
QBE () created by Moshè Zloof, IBM 1977
introduced in 1974 by the U.C. Berkeley Ingres project.
(DRDA) was designed by a work group within IBM in the period 1988 to 1994. DRDA enables network connected relational databases to cooperate to fulfill SQL requests.
An interactive user or program can issue SQL statements to a local RDB and receive tables of data and status indicators in reply from remote RDBs. SQL statements can also be compiled and stored in remote RDBs as packages and then invoked by package name. This is important for the efficient operation of application programs that issue complex, high-frequency queries. It is especially important when the tables to be accessed are located in remote systems.
The messages, protocols, and structural components of DRDA are defined by the .
Chamberlin's critiques of SQL include:
Early specifications did not support major features, such as primary keys. Result sets could not be named, and subqueries had not been defined. These were added in 1992.
SQL's controversial "NULL" value is neither true nor false (predicates with terms that return a null value return null rather than true or false). Features such as outer-join depend on null values.
Other popular critiques are that it allows duplicate rows, making integration with languages such as , whose data types might make it difficult to accurately represent the data, difficult in terms of parsing and by the absence of modularity.
Formally, "SQL-data" statements excluding "SQL-data change" this is primarily the
statement.
Formally, "SQL-schema" statements.
Formally, "SQL-data change" statements
. . 10 April .
. . April 2013. p. 3 2013.
Paul, Ryan. . Ars Technica 2011.
Beaulieu, Alan (April 2009).
Mary E Treseler, ed. Learning SQL (2nd ed.). Sebastapol, CA, USA: O'Reilly.  .
. Britannica.com.
. Oxforddictionaries.com.
. Publib.boulder.ibm.com.
. Msdn.microsoft.com.
, 4.22 SQL-statements, 4.22.1
Classes of SQL-statements "There are at least five ways of classifying SQL-statements:", 4.22.2, SQL statements classified by function "The following are the main classes of SQL-statements:";
4.11 SQL-statements, and later revisions.
Chatham, Mark (2012). Structured Query Language By Example - Volume I: Data Query Language. p. .  .
Codd, Edgar F. (June 1970). "A Relational Model of Data for Large Shared Data Banks". Communications of the ACM. Association for Computing Machinery. 13 (6): 377–87.   . :.
Chapple, Mike. . Databases. About.com.
. International Business Machines. October 27, 2006.
Chamberlin, Donald (2012). . IEEE Annals of the History of Computing. 34 (4): 78–82 2018.
Chamberlin, Donald D; Boyce, Raymond F (1974).
(PDF). Proceedings of the 1974 ACM SIGFIDET Workshop on Data Description, Access and Control. Association for Computing Machinery: 249–64.
Oppel, Andy (February 27, 2004). . : McGraw-Hill Osborne Media. pp. 90–1.  .
. IBM Archives. IBM.
ANSI/ISO/IEC International Standard (IS). Database Language SQL—Part 2: Foundation (SQL/Foundation). 1999.
PostgreSQL contributors (2011). . PostgreSQL 9.1 official documentation. postgresql.org.
PostgreSQL contributors (2012). . PostgreSQL 9.1 official website. PostgreSQL Global Development Group 2012. PostgreSQL prides itself in standards compliance. Its SQL implementation strongly conforms to the ANSI-SQL:2008 standard
. Mimer SQL official website. Mimer Information Technology. 2009.
"Finding Aid". . American National Standards Institute.
Doll, Shelley (June 19, 2002). . TechRepublic's Builder.com. TechRepublic. Archived from
Gillespie, Patrick. . Pronouncing SQL: S-Q-L or Sequel? 2012.
Melton, J Alan R Simon (1993). "1.2. What is SQL?". Understanding the New SQL: A Complete Guide. Morgan Kaufmann. p. 536.  . SQL (correctly pronounced "ess cue ell," instead of the somewhat common "sequel")...
Wagner, Michael (2010). SQL/XML:2006 - Evaluierung der Standardkonformit?t ausgew?hlter Datenbanksysteme. Diplomica Verlag. p. 100.  .
. Sybase. July 2008. Archived from
Krishna Kulkarni, Jan-Eike Michels (2012).
Fred Zemke (2012).
. December 2016.
(Zip). Whitemarsh Information Systems Corporation.
"ISO/IEC 8: Information and Definition Schemas (SQL/Schemata)". 2008: 1.
Fernando Saenz-Perez.
(PDF). Lbd.udc.es.
Reinsch, R. (1988). "Distributed database for SAA". IBM Systems Journal. 27 (3): 362–389. :.
Distributed Relational Database Architecture Reference. IBM Corp. SC26-90.
Schauder, Jen. . Schauderhaft 2018.
Codd, Edgar F (June 1970). . Communications of the ACM. 13 (6): 377–87. :. Archived from
with : A Guide to the SQL standard : a users guide to the standard database language SQL, 4th ed., Addison Wesley, USA 1997,  
including SQL. Technical Corrigenda (corrections) and Technical Reports (discussion documents) are published there.
Formal SQL standards are available from
for a fee. For informative use, as opposed to strict standards compliance, late drafts often suffice.
Find more aboutSQLat Wikipedia's
from Wiktionary
from Wikimedia Commons
from Wikibooks
from Wikiversity
: transcript of a reunion meeting devoted to the personal history of relational databases and SQL.
Collection documents the H2 committee's development of the NDL and SQL standards.
In this oral history Chamberlin recounts his early life, his education at
and , and his work on relational database technology. Chamberlin was a member of the System R research team and, with , developed the SQL database language. Chamberlin also briefly discusses his more recent research on XML query languages.
This comparison of various SQL implementations is intended to serve as a guide to those interested in porting SQL code between various RDBMS products, and includes comparisons between SQL:2008, PostgreSQL, DB2, MS SQL Server, MySQL, Oracle, and Informix.
- An introduction to real-time processing of streaming data with continuous SQL queries
standards by standard number
: Hidden categories: