select XSName,XKName,ZYName,count(*) as 数量
from (
select a.XSName,b.XKName,c.ZYName
from xs a,xk b,zy c,yw d
where d.xsid=a.xsid
and d.xkid=b.xkid
and d.zyid=c.zyid
union all
select a.XSName,b.XKName,c.ZYName
from xs a,xk b,zy c,sx d
where d.xsid=a.xsid
and d.xkid=b.xkid
and d.zyid=c.zyid
union all
select a.XSName,b.XKName,c.ZYName
from xs a,xk b,zy c,yy d
where d.xsid=a.xsid
and d.xkid=b.xkid
and d.zyid=c.zyid
union all
select a.XSName,b.XKName,c.ZYName
from xs a,xk b,zy c,其它科目表 d
where d.xsid=a.xsid
and d.xkid=b.xkid
and d.zyid=c.zyid
) as t
from (
select a.XSName,b.XKName,c.ZYName
from xs a,xk b,zy c,yw d
where d.xsid=a.xsid
and d.xkid=b.xkid
and d.zyid=c.zyid
union all
select a.XSName,b.XKName,c.ZYName
from xs a,xk b,zy c,sx d
where d.xsid=a.xsid
and d.xkid=b.xkid
and d.zyid=c.zyid
union all
select a.XSName,b.XKName,c.ZYName
from xs a,xk b,zy c,yy d
where d.xsid=a.xsid
and d.xkid=b.xkid
and d.zyid=c.zyid
union all
select a.XSName,b.XKName,c.ZYName
from xs a,xk b,zy c,其它科目表 d
where d.xsid=a.xsid
and d.xkid=b.xkid
and d.zyid=c.zyid
) as t
解决方案 »
- 我心中的疑问
- 怎样在sqlserver2005中实现小计、合计
- 关于SQL语句查询多个值的问题 急。。。。在线等
- 如何从一数据库中查找出含有某一字段名的所有表名?并从这些表中判断该字段是否已有某一值?
- SQl SERVER 繁體轉化為簡體問題
- 各位大侠,请教一个触发器的问题,插入记录时根据主键判断记录是否存在,如果存在,则更新记录,如果不存在,则插入记录的触发器怎么写,
- 求救,改变sql server 2000的认证方式.
- 300分求救各种问题,欢迎解答。
- 数据库查询问题
- 求助,别人给我sql server 数据库名字,帐号密码和ip,我是不是要下载sql server?还是有什么客户端工具直接可以登录数据库查看的?
- 为什么我无法安装服务器组建?(菜鸟级)
- 求助ODBC连接出现的问题
------------
我要解解决的并不完全如此,只是简单的列出来
#tempRs(XSID,ZYID,XKID,....) 将所有资源都插入到这个表中.再如何统呢???
from xs a,xk b,zy c,#tempRs d
where d.xsid=a.xsid
and d.xkid=b.xkid
and d.zyid=c.zyid
group by a.XSName,b.XKName,c.ZYName更正2楼错误(少group by):select XSName,XKName,ZYName,count(*) as 数量
from (
select a.XSName,b.XKName,c.ZYName
from xs a,xk b,zy c,yw d
where d.xsid=a.xsid
and d.xkid=b.xkid
and d.zyid=c.zyid
union all
select a.XSName,b.XKName,c.ZYName
from xs a,xk b,zy c,sx d
where d.xsid=a.xsid
and d.xkid=b.xkid
and d.zyid=c.zyid
union all
select a.XSName,b.XKName,c.ZYName
from xs a,xk b,zy c,yy d
where d.xsid=a.xsid
and d.xkid=b.xkid
and d.zyid=c.zyid
union all
select a.XSName,b.XKName,c.ZYName
from xs a,xk b,zy c,其它科目表 d
where d.xsid=a.xsid
and d.xkid=b.xkid
and d.zyid=c.zyid
) as t
group by XSName,XKName,ZYName
还有个问题是:
得到的结果(暂时不用资源定义表)XSName XKName 数量
XS1 XK1 12
XS1 XK2 34
XS1 XK3 56
XS2 XK1 13
XS2 XK2 14
XS2 XK3 15
我可不可以在数据库中实现转换为:
XSName XK1 XK2 XK3
XS1 12 34 56
XS2 13 14 15
有什么方法吗?
没有的话我只好在程序中实现了!我就结贴了
sum(case XKName when 'XK1' then 数量 else 0 end) as XK1,
sum(case XKName when 'XK2' then 数量 else 0 end) as XK2,
sum(case XKName when 'XK3' then 数量 else 0 end) as XK3
from 得到的结果
group by XSName如果你的XKName 未知,就需要动态实现,可以这么写:
declare @sql varchar(8000)
set @sql='select XSName'
select @sql=@sql+',sum(case XKName when '''+rtrim(XKName)+''' then 数量 else 0 end) as ['+rtrim(XKName)+']' from 得到的结果
group by XKName
order by XKNameexec (@sql+' from 得到的结果 group by XSName')