select type,currrent_num=count(*),
absolute_change_num=count(*)-(select count(*) from 表1 where 表1.type=表2.type),
raise_num=(select count(*) from 表2 a
where not exists (select * from 表1 where type=a.type and name=a.name)),
fall_num=(select count(*) from 表1 a
where not exists (select * from 表2 where type=a.type and name=a.name))
from 表2 group by type
absolute_change_num=count(*)-(select count(*) from 表1 where 表1.type=表2.type),
raise_num=(select count(*) from 表2 a
where not exists (select * from 表1 where type=a.type and name=a.name)),
fall_num=(select count(*) from 表1 a
where not exists (select * from 表2 where type=a.type and name=a.name))
from 表2 group by type
解决方案 »
- 帮我看看如何实现这个需求
- 存储过程中数据表的缩写方法有哪些?
- 请教高手解答
- 仓库管理系统中,如何建立数据库,数据库的结构要如何建立?要做到可以看到物料存在那个仓位.做过的介绍一下经验啊!谢了!
- 一个Insert的问题!
- SQL打了SP3补丁之后SQl依然是8.0.139,不过在客户端实用工具里的网络库中看到是8.0.341
- 需要什么權限才可以在sql server service manager中啟動遠程機器上的服務。
- Group统计问题
- 我在SQL SERVER中存放 了一些tiff图片,可是怎么才能将其显示在客户端呢,我用的是delphi
- 关于SQL SEVER 字段的数据类型datetime
- 请问连接oracle出错
- 谁江湖救急:求一sql语句。。。。。。。。
select
type = (case when a.type is null then b.type else a.type end),
currrent_num = isnull(b.num,0),
absolute_change_num = abs(isnull(b.num) - isnull(a.num)),
raise_num = (case when (isnull(b.num) - isnull(a.num)) > 0 then (isnull(b.num) - isnull(a.num)) else 0 end),
fall_num = (case when (isnull(a.num) - isnull(b.num)) > 0 then (isnull(a.num) - isnull(b.num)) else 0 end)
from
(select type,count(*) as num from 表1 group by type) a
full outer join
(select type,count(*) as num from 表2 group by type) b
on
a.type = b.type
select tb_all.type,
curentnum=(select count(*) from tb2 where type =tb_all.type),
absnum= abs(isnull(tb_add.addnum,0)-isnull(tb_sub.subnum,0)),
addnum= isnull(tb_add.addnum,0),
subnum=isnull(tb_sub.subnum,0)
from
(
select type from tb1
union
select type from tb2
) tb_all left join (
select type,count(type) as addnum from tb2
where [id] not in (select distinct tb2.[id] from tb1,tb2
where tb1.type=tb2.type and tb1.[name]=tb2.[name] ) group by type
) tb_add
on tb_all.type=tb_add.typeleft join
(
select type,count(type) as subnum from tb1
where [id] not in (select distinct tb1.[id] from tb1,tb2
where tb1.type=tb2.type and tb1.[name]=tb2.[name] ) group by type
) tb_sub
on tb_all.type=tb_sub.type
select type,count(*),
count(*)-(select count(*) from test1 where test1.type=test_bak.type),
(select count(*) from test_bak
where not exists (select * from test1
where test1.type=test_bak.type and test1.name=test_bak.name)) as raise_num,
(select count(*) from test1
where not exists (select * from test_bak
where test1.type=test_bak.type and test1.name=test_bak.name)) as fall_num
from test_bak
group by type
order by type
后两列查询出的数据不正确