SQL中进行交差表查询的方法和代码
这里指的交叉表,就是象Access的交叉表查询一样的效果,比如Employees表中City字段代表了城市的名称,TitleOfCourtesy代表称呼,我们希望按照City和TitleOfCourtesy的情况来统计ReportsTo字段的合计数(本统计没有任何实际意义,只是挑选一些记录包含重复内容的字段来说明情况),并显示成以下格式:(TitleOfCourtesy作为行,City作为列),�0�2�0�2SUM(CASE�0�2City�0�2WHEN�0�2'Redmond'�0�2THEN�0�2ReportsTo�0�2ELSE�0�2NULL�0�2END)�0�2AS�0�2[Redmond�0�2City],�0�2�0�2SUM(CASE�0�2City�0�2WHEN�0�2'Seattle'�0�2THEN�0�2ReportsTo�0�2ELSE�0�2NULL�0�2END)�0�2AS�0�2[Seattle�0�2City]FROM�0�2Employees�0�2GROUP�0�2BY�0�2TitleOfCourtesy其中利用了CASE语句判断,如果是相应的列,则取需要统计的ReportsTo数值,否则取NULL,然后再合计其中有两个常见问题说明一下:a、用NULL而不用0是有道理的,假如用0,虽然求和函数SUM可以取到正确的数,但类似COUNT函数(取记录个数),结果就不对了,因为Null不算一条记录,而0要算,同理空字串("")也是这样,总之在这里应该用NULL,这样任何函数都没问题。b、假如在视图的设计界面保存以上的查询,则会报错“没有输出列”,从而无法保存,其实只要在查询前面加上一段:Create View ViewName AS ...,ViewName是你准备给查询起的名称,...就是我们的查询,然后运行一下,就可以生成视图了,对于其他一些设计器不支持的语法,也可以这样保存。总体思路其实很简单,首先检索列头信息,形成一个游标,然后遍历游标,将上面查询语句里Case判断的内容用游标里的值替代,形成一条新的Sql查询,然后执行,返回结果,就可以了,以下是我写的一个存储过程,供大家参考:CREATE�0�2procedure�0�2CorssTab�0�2@strTabName�0�2as�0�2varchar(50)�0�2=�0�2'Employees',�0�2--此处放表名@strCol�0�2as�0�2varchar(50)�0�2=�0�2'City',�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2--表头分组依据字段@strGroup�0�2as�0�2varchar(50)�0�2=�0�2'TitleOfCourtesy',--分组字段@strNumber�0�2as�0�2varchar(50)�0�2=�0�2'ReportsTo',�0�2�0�2�0�2�0�2--被统计的字段@strSum�0�2as�0�2varchar(10)�0�2=�0�2'Sum'�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2--运算方式ASDECLARE�0�2@strSql�0�2as�0�2varchar(1000),�0�2@strTmpCol�0�2as�0�2varchar(100)EXECUTE�0�2('DECLARE�0�2corss_cursor�0�2CURSOR�0�2FOR�0�2SELECT�0�2DISTINCT�0�2'�0�2+�0�2@strCol�0�2+�0�2'�0�2from�0�2'�0�2+�0�2@strTabName�0�2+�0�2'�0�2for�0�2read�0�2only�0�2')�0�2--生成游标begin�0�2�0�2SET�0�2nocount�0�2ON�0�2�0�2�0�2SET�0�2@strsql�0�2='select�0�2'�0�2+�0�2@strGroup�0�2+�0�2',�0�2'�0�2+�0�2@strSum�0�2+�0�2'('�0�2+�0�2@strNumber�0�2+�0�2')�0�2AS�0�2['�0�2+�0�2@strSum�0�2+�0�2'�0�2of�0�2'�0�2+�0�2@strNumber�0�2+�0�2']'�0�2--查询的前半段�0�2�0�2OPEN�0�2corss_cursor�0�2�0�2while�0�2(0=0)�0�2�0�2BEGIN�0�2�0�2�0�2�0�2FETCH�0�2NEXT�0�2FROM�0�2corss_cursor�0�2--遍历游标,将列头信息放入变量@strTmpCol�0�2�0�2�0�2�0�2INTO�0�2@strTmpCol�0�2�0�2�0�2�0�2if�0�2(@@fetch_status<0)�0�2break�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2SET�0�2@strsql�0�2=�0�2@strsql�0�2+�0�2',�0�2'�0�2+�0�2@strSum�0�2+�0�2'(CASE�0�2'�0�2+�0�2@strCol�0�2+�0�2'�0�2WHEN�0�2'''�0�2+�0�2@strTmpCol�0�2+�0�2'''�0�2THEN�0�2'�0�2+�0�2@strNumber�0�2+�0�2'�0�2ELSE�0�2Null�0�2END)�0�2AS�0�2['�0�2+�0�2@strTmpCol�0�2+�0�2'�0�2'�0�2+�0�2@strCol�0�2+�0�2']'�0�2--构造查询�0�2�0�2END�0�2�0�2�0�2�0�2�0�2�0�2�0�2�0�2SET�0�2@strsql�0�2=�0�2@strsql�0�2+�0�2'�0�2from�0�2'�0�2+�0�2@strTabname�0�2+�0�2'�0�2group�0�2by�0�2'�0�2+�0�2@strGroup�0�2--查询结尾�0�2�0�2EXECUTE(@strsql)�0�2--执行�0�2�0�2IF�0�2@@error�0�2<0�0�2RETURN�0�2@@error�0�2--如果出错,返回错误代码�0�2�0�2CLOSE�0�2corss_cursor�0�2�0�2�0�2DEALLOCATE�0�2corss_cursor�0�2RETURN�0�20�0�2--释放游标,返回0表示成功endGO几点说明:a、这是一个通用存储过程,使用时@strTabName、@strCol、@strGroup、@strNumber、@strSum几个变量设置一下就可以用到其他表上,其中结果集的第二列我加了个合计列b、为了测试方便,我在存储过程中设置了默认值,就是前面提到的Employees表,这样直接运行时就可以出来我上面提到的结果。c、使用时,可以把上面的代码复制到企业管理器的查询设计界面Sql窗格,或者查询分析器里运行一下(注意正确选择NorthWind数据库),就可以生成一个存储过程:CorssTab,然后直接运行CorssTab,如果出现本文前面类似的窗格,就表示运行成功了。d、假如用于其它表,首先需要在你的用户数据库里生成此存储过程(当然也可以放到Master里,然后再加个变量:@DataBase,赋值为数据库名称,然后在上面代码打开指定数据库,这样所有的数据库都可以调用它),当你调用时,采取以下格式:
select c.CustomerName,o.ProductName,SUM(o.SaleNum)
from Customer as c
left join OrderList as o
on o.CustomerNo=c.CustomerNo
group by c.CustomerName,o.ProductName
建表语句:注意数据库为sqlserver
create table A(aID int,aNum nvarchar(10))
insert into A (aID,aNum) values (1,'a1')
insert into A (aID,aNum) values (2,'a2')
create table B(bID int,bName nvarchar(10))
insert into B (bID,bName) values (1,'b1')
insert into B (bID,bName) values (2,'b2')
insert into B (bID,bName) values (3,'b3')
create table C(ca int,cb int)
insert into C (ca,cb) values (1,2)
insert into C (ca,cb) values (1,3)
insert into C (ca,cb) values (2,1)
insert into C (ca,cb) values (2,3)
第一:通过full join
select aa.*,cc.*,bb.* from A aa
full join B bb on 1=1
full join C cc on cc.ca=aa.aID and cc.cb=bb.bID
得到的结果是
aID aNum ca cb bID bName
----------- ---------- ----------- ----------- ----------- ----------
1 a1 NULL NULL 1 b1
1 a1 1 2 2 b2
1 a1 1 3 3 b3
2 a2 2 1 1 b1
2 a2 NULL NULL 2 b2
2 a2 2 3 3 b3
(6 件処理されました)
第二种方法是 cross join
select N.aid,N.anum,N.ca,T.CB,T.BID,T.BNAME
from
(select a.aid,a.anum,c.ca
from a
right join
c
ON
a.aid=c.ca
) N
CROSS JOIN
(select B.Bid,B.BNAME,c.ca,c.cb
from B
right join
c
ON
B.aid=c.cB) T
结果如上
SELECT column1, column2, ... FROM table1, table2 WHERE table1.key = table2.key AND ...
Hibernate对多表关联查询
三、 Hibernate 进行多表关联查询Hibernate对多个表进行查询时,查询结果是多个表的笛卡尔积,或者称为“交叉”连接。 例如:from Student, Book from Student as stu, Book as boo from Student stu, Book boo注意:让查询中的Student和Book均是表student和book对应的类名,它的名字一定要和类的名...
电脑里常用的的英语有那些(汉语意思)?
Voronoi Diagrams Voronoi图 Nearest Neighbor Search 最近点对查询 Range Search 范围查询 Point Location 位置查询 Intersection Detection 碰撞测试 Bin Packing 装箱问题 Medial-Axis Transformation 中轴变换 Polygon Partitioning 多边形分割 Simplifying Polygons 多边形化简 Shape Similarity 相似多边形 Motion Pla...
DMI指标有几种?
当ADX值降至20以下,且呈横向窄幅移动时,可以判断行情为牛皮盘整,上升或下跌趋势不明朗,投资者应以观望为主,不可依据+DI和-DI的交叉信号来买卖股票。 3、判断行情是否转势 当ADX值在高点由升转跌时,预示行情即将反转。在涨势中的ADX在高点由升转跌,预示涨势即将告一段落;在跌势中的ADX值从高位回落,预示跌...
股市常用的技术指标有哪些?
24、成交量可说是股市中最重要的因素之一,而且它是一个相对比较客观的量数。股市有谚:什么都可以做假,成交量做不了假。但成交量对股市而言又奥妙无穷,只要稍微懂得它一点,那么,很可能对它人人都有一套自己的理解;25、主力进出ABV指标起源于OBV指标。虽然许多专著中对OBV技法多作了详尽的总结,...
中国的化妆品含铅汞砷的标准是多少?国际标准又是多少?
化妆品生产应符合化妆品生产规范的要求。化妆品的生产过程应科学合理,保证产品安全。化妆品上市前应进行必要的检验,检验方法包括相关理化检验方法、微生物检验方法、毒理学试验方法和人体安全试验方法等。化妆品应符合产品质量安全有关要求,经检验合格后方可出厂。参考资料:百度百科-化妆品安全技术规范 ...
诚求大智慧软件和广发证券的至强板软件的详细操作
激活功能: 1、从开机菜单中选择"大盘分析",单击鼠标左键或按Enter键进入子菜单,确定选项后按Enter键确认并执行操作,按Esc键退出当前菜单。 2、从下拉菜单上选择"大盘走势",确定选项后,单击鼠标左键或按Enter键确认并执行操作。 操作: 1、Enter键切换到大盘K线图画面。 2、PageUp查看上一个类别指数,PageDown查看...
金胜祖师: 一种是在Where条件中少写条件 mysql有一个cross join
合川市13312683798: 这个SQL Server 交叉表查询 怎么实现? - ?
金胜祖师: select c.CustomerName,o.ProductName,SUM(o.SaleNum) from Customer as c left join OrderList as o on o.CustomerNo=c.CustomerNo group by c.CustomerName,o.ProductName
合川市13312683798: 用sql语句怎么查一个表的信息 - ?
金胜祖师: 1、 查询所有列. sql语句:SELECT * FROM user. 2、查询指定列 sql语句:SELECT nickname, mobile, sex FROM user 3、将查询结果按字段的值进行排序 sql语句:SELECT * FROM emp ORDER BY deptno, sal DESC; (按序号升序...
合川市13312683798: 如何用一个SQL查询语句查询两个表的数据是否一致并且把不一致的数据显示出来? - ?
金胜祖师: 代码如下: select * from a a where not exists(select * from b b where a.name=b.name)1. 查看表结构 SQL>DESC emp; 2. 查询所有列 SQL>SELECT * FROM emp; 3. 查询指定列 SQL>SELECT empmo, ename, mgr FROM emp; SQL>SELECT ...
合川市13312683798: SQL中的交叉表查询,来个稍微复杂点的例子吧 - ?
金胜祖师: select d.name as clientname,c.prono,sum(a.ck_kg) as ck_kg,sum(a.ck_pcs) as ck_pcs,0 as fd_kg,0 as fd_pcs,trunc(b.docdate) as docdate from pl_cpch_line a join pl_cpch b on A.CPCH_ID=b.cpch_id join v_pl_scrk_line c on a.scrk_line_id=c.scrk...
合川市13312683798: 怎样在SQL SERVER中实现交叉表查询? - ?
金胜祖师: 交叉连接 cross join on
合川市13312683798: 怎么用SQL语句写交叉表啊? - ?
金胜祖师: select a.学号,a.姓名,a.成绩 flash成绩,b.成绩 dw成绩,c.成绩 asp成绩 from 表 a left join 表 b on a.学号=b.学号 and a.课程名>b.课程名 left join 表 c on a.学号=c.学号 and a.课程名>c.课程名 and b.课程名>c.课程名 where c.成绩 is not null
合川市13312683798: 如何用一个sql分别从两个表中查询数据 - ?
金胜祖师: sql语句从一张表中查询数据插入到另一张表中的方法如下: 1、select * into destTbl from srcTbl. 2、insert into destTbl(fld1, fld2) select fld1, 5 from srcTbl.以上两句都是将 srcTbl 的数据插入到 destTbl,但两句又有区别的:第一句(select into from)要求目标表(destTbl)不存在,因为在插入时会自动创建.第二句(insert into select from)要求目标表(destTbl)存在,由于目标表已经存在,所以我们除了插入源表(srcTbl)的字段外,还可以插入常量.
合川市13312683798: 在Delphi中如何用SQL实现交叉表查询??
金胜祖师: 就只要应用sql语句的嵌套相干萌芽语句即可.select a.学生姓名,a.班级,a.性别,b.课程名称,c.成就 from 学生情况表 a,课程情况 b,学生成就表 c where c.学生id=a.学生id and c.课程id=b.课程id
合川市13312683798: 求教一个sql交叉查询的语句或思路,感谢各位关心?
金胜祖师: 这个你要使用动态SQL.使用透视图方式.如果是别的数据库 需要使用大量的 case when 方式 组合,不如使用程序控制. --- 如下是sql server 2005 create table RawData ( A int, B int ) insert into RawData select 1,2 union all select 2,3 union all ...