学校管理系统中的统计报表,
表1,奖励表
时间 说明 次数 金额
09-05-01 奖励张三见义勇为 1 100
表2,处罚表
时间 说明 次数 金额
09-07-01 李四迟到 1 50要求实现如下样式
时间 说明 奖励次数 奖励金额 处罚次数 处罚金额
09-05-01 奖励张三见义勇为 1 100
09-07-01 李四迟到 1 50我用如下语句,但总是两条记录合到一块,不知为什么
SELECT iif(isnull(a.时间),b.时间,a.时间) as 时间,
iif(isnull(a.说明),b.说明,a.说明) as 说明,
a.次数 as 奖励次数,a.金额 as 奖励金额,
b.次数 as 处罚次数,b.金额 as 处罚金额
FROM 奖励表 as a LEFT JOIN 处罚表 as b ON a.时间=b.时间
UNION ALL
SELECT iif(isnull(a.时间),b.时间,a.时间) as 时间,
iif(isnull(a.说明),b.说明,a.说明) as 说明,
a.次数 as 奖励次数,a.金额 as 奖励金额,
b.次数 as 处罚次数,b.金额 as 处罚金额
FROM 奖励表 as a RIGHT JOIN 处罚表 as b ON a.时间=b.时间
表1,奖励表
时间 说明 次数 金额
09-05-01 奖励张三见义勇为 1 100
表2,处罚表
时间 说明 次数 金额
09-07-01 李四迟到 1 50要求实现如下样式
时间 说明 奖励次数 奖励金额 处罚次数 处罚金额
09-05-01 奖励张三见义勇为 1 100
09-07-01 李四迟到 1 50我用如下语句,但总是两条记录合到一块,不知为什么
SELECT iif(isnull(a.时间),b.时间,a.时间) as 时间,
iif(isnull(a.说明),b.说明,a.说明) as 说明,
a.次数 as 奖励次数,a.金额 as 奖励金额,
b.次数 as 处罚次数,b.金额 as 处罚金额
FROM 奖励表 as a LEFT JOIN 处罚表 as b ON a.时间=b.时间
UNION ALL
SELECT iif(isnull(a.时间),b.时间,a.时间) as 时间,
iif(isnull(a.说明),b.说明,a.说明) as 说明,
a.次数 as 奖励次数,a.金额 as 奖励金额,
b.次数 as 处罚次数,b.金额 as 处罚金额
FROM 奖励表 as a RIGHT JOIN 处罚表 as b ON a.时间=b.时间
from 奖励表
union all
select 时间,说明,' ',' ' ,次数,金额
from 处罚表
-------------------------------------------> 测试时间:2009-07-19
--> 我的淘宝:http://shop36766744.taobao.com/--------------------------------------------------if object_id('[奖励表]') is not null drop table [奖励表]
create table [奖励表]([时间] datetime,[说明] varchar(16),[次数] int,[金额] int)
insert [奖励表]
select '09-05-01','奖励张三见义勇为',1,100
if object_id('[处罚表]') is not null drop table [处罚表]
create table [处罚表]([时间] datetime,[说明] varchar(8),[次数] int,[金额] int)
insert [处罚表]
select '09-07-01','李四迟到',1,50select 时间,说明,奖励次数=isnull(次数,0),奖励金额=isnull(金额,0),处罚次数='',处罚金额='' from [奖励表] union all
select 时间,说明,奖励次数='',奖励金额='',处罚次数=isnull(次数,0),处罚金额=isnull(金额,0) from [处罚表]/*
时间 说明 奖励次数 奖励金额 处罚次数 处罚金额
------------------------------------------------------ ---------------- ----------- ----------- ----------- -----------
2009-05-01 00:00:00.000 奖励张三见义勇为 1 100 0 0
2009-07-01 00:00:00.000 李四迟到 0 0 1 50(所影响的行数为 2 行)
*/drop table 处罚表,奖励表
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb1') IS NOT NULL
DROP TABLE tb1
GO
CREATE TABLE tb1( 时间 varchar(10),说明 varchar(100),次数 int , 金额 int)
go
insert tb1 SELECT '09-05-01' , '奖励张三见义勇为' , 1 , 100
go
IF OBJECT_ID('tb2') IS NOT NULL
DROP TABLE tb2
GO
CREATE TABLE tb2( 时间 varchar(10),说明 varchar(100),次数 int , 金额 int)
go
insert tb2 SELECT '09-07-01' , '李四迟到' , 1 , 50
go
select 时间,说明,奖励次数=次数 ,奖励金额=金额,' 'as 处罚次数,' ' as 处罚金额
from tb1
union all
select 时间,说明,' ',' ' ,次数,金额
from tb2
go
--方法2
select 时间=ISNULL(tb1.时间,tb2.时间),
说明=ISNULL(tb1.说明,tb2.时间),
奖励次数=tb1.次数,
奖励金额=tb1.金额,
处罚次数=tb2.次数,
处罚金额=tb2.金额
from tb1 full join tb2 on tb1.时间=tb2.时间
go
/*------------
时间 说明 奖励次数 奖励金额 处罚次数 处罚金额
---------- ---------------------------------------------------------------------------------------------------- ----------- ----------- ----------- -----------
09-05-01 奖励张三见义勇为 1 100 0 0
09-07-01 李四迟到 0 0 1 50(2 行受影响)
-------*/
from 奖励表
union all
select 时间,说明,' ',' ' ,次数,金额
from 处罚表
好人做到底吧,再加两个条件,一个是加上时间段查询在哪里加呢,再一个财务上统计奖罚,我想在后面加上一项如下:
---------------------------------------------------------------
时间 说明 奖励次数 奖励金额 处罚次数 处罚金额 奖罚余额
09-05-01 奖励张三见义勇为 1 100 100
09-07-01 李四迟到 1 50 50
有办法实现吗?
from 奖励表
union all
select 时间,说明,' ',' ' ,次数,金额
from 处罚表) WHERE 时间 BETWEEN '2009-5-5' AND '2009-7-9'GROUP BY
时间, 说明 , 奖励次数,处罚次数
from 奖励表
union all
select 时间,说明,' ',' ' ,次数,金额
from 处罚表) WHERE 时间 BETWEEN '2009-5-5' AND '2009-7-9'GROUP BY
时间, 说明 , 奖励次数,处罚次数 试试
说明=ISNULL(tb1.说明,tb2.时间),
奖励次数=tb1.次数,
奖励金额=tb1.金额,
处罚次数=tb2.次数,
处罚金额=tb2.金额 into #
from tb1 full join tb2 on tb1.时间=tb2.时间
select *,奖金余额=(select SUM(isnull(奖励金额,0)-isnull(处罚金额,0)) from # where 时间<=t.时间) from # t
go
/*------------
id 时间 说明 奖励次数 奖励金额 处罚次数 处罚金额 奖金余额
----------- ---------- ---------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ----------- -----------
1 09-05-01 奖励张三见义勇为 1 100 NULL NULL 100
2 09-07-01 09-07-01 NULL NULL 1 50 50(2 行受影响)(2 行受影响)
-------*/
create table [奖励表]([时间] datetime,[说明] varchar(16),[次数] int,[金额] int)
insert [奖励表]
select '09-05-01','奖励张三见义勇为',1,100
if object_id('[处罚表]') is not null drop table [处罚表]
create table [处罚表]([时间] datetime,[说明] varchar(8),[次数] int,[金额] int)
insert [处罚表]
select '09-07-01','李四迟到',1,50SELECT *,奖罚余额=SUM(ISNULL(奖励金额,0))+SUM(ISNULL(处罚金额,0)) FROM (select 时间,说明,奖励次数=次数 ,奖励金额=金额,' 'as 处罚次数,' ' as 处罚金额
from 奖励表
union all
select 时间,说明,' ',' ' ,次数,金额
from 处罚表) AS TWHERE 时间 BETWEEN '2009-5-1' AND '2009-7-1'GROUP BY
时间,说明 , 奖励次数,处罚次数 ,奖励金额,处罚金额时间 说明 奖励次数 奖励金额 处罚次数 处罚金额 奖罚余额
------------------------------------------------------ ---------------- ----------- ----------- ----------- ----------- -----------
2009-05-01 00:00:00.000 奖励张三见义勇为 1 100 0 0 100
2009-07-01 00:00:00.000 李四迟到 0 0 1 50 50(所影响的行数为 2 行)借迪哥数据用
select * ,奖罚余额=SUM(ISNULL(奖励金额,0))+SUM(ISNULL(处罚金额,0))
from (
select 时间,说明,奖励次数=次数 ,奖励金额=金额,' 'as 处罚次数,' ' as 处罚金额
from tb1
union all
select 时间,说明,' ',' ' ,次数,金额
from tb2) t
group by 时间,说明,奖励次数,奖励金额,处罚次数, 处罚金额
这个行~
,奖罚余额=SUM(ISNULL(奖励金额,0))+SUM(ISNULL(处罚金额,0))
from (
select 时间,说明,奖励次数=次数 ,奖励金额=金额,' 'as 处罚次数,' ' as 处罚金额
from tb1
union all
select 时间,说明,' ',' ' ,次数,金额
from tb2) t
group by 时间,说明,奖励次数,奖励金额,处罚次数, 处罚金额
这个试试。。
我上面本来写的方法不可以么?
,SUM(ISNULL(奖励金额,0))+SUM(ISNULL(处罚金额,0))AS'奖罚余额'
from (
select 时间,说明,奖励次数=次数 ,奖励金额=金额,' 'as 处罚次数,' ' as 处罚金额
from tb1
union all
select 时间,说明,' ',' ' ,次数,金额
from tb2) t
group by 时间,说明,奖励次数,奖励金额,处罚次数, 处罚金额
这样试试,
ACCESS不懂