学费统计

--学费减免 一个学期一个学生有多个减免
CREATE TABLE [jianmian] (
[id] [int]  IDENTITY (1, 1)  NOT NULL,
[pid] [int]  NOT NULL,--学期编号
[nid] [int]  NOT NULL,--年级编号
[cid] [int]  NOT NULL,--班级编号
[uid] [int]  NOT NULL, --用户编号
[username] [varchar]  (50) NULL,
[kemuid] [int]  NULL,
[kemu] [varchar]  (50) NULL,
[jine] [decimal]  (18,2) NULL,  --金额
[addtime] [datetime]  NULL DEFAULT (getdate()),
[Del] [int]  NULL,
[beizhu] [varchar]  (580) NULL)ALTER TABLE [jianmian] WITH NOCHECK ADD  CONSTRAINT [PK_jianmian] PRIMARY KEY  NONCLUSTERED ( [id] )
SET IDENTITY_INSERT [jianmian] ONINSERT [jianmian] ([id],[pid],[nid],[cid],[uid],[username],[kemuid],[kemu],[jine],[addtime],[Del]) VALUES ( 1,10,4,48,3448,N'王玲',4,N'工资扣除',1000.00,N'2015/1/15 9:01:23',0)
INSERT [jianmian] ([id],[pid],[nid],[cid],[uid],[username],[kemuid],[kemu],[jine],[addtime],[Del]) VALUES ( 2,10,4,43,3448,N'王玲',5,N'半托减免',1000.00,N'2015/1/15 9:25:17',0)SET IDENTITY_INSERT [jianmian] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[jiaofeijine]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [jiaofeijine]--每学期每年级的学费金额 
--年级金额-已交-减免=欠费金额
CREATE TABLE [jiaofeijine] (
[id] [int]  IDENTITY (1, 1)  NOT NULL,
[nid] [int]  NULL,--年级编号
[jine] [decimal]  (18,2) NULL, --金额
[kemu] [int]  NULL, 
[pid] [int]  NULL) --学期编号ALTER TABLE [jiaofeijine] WITH NOCHECK ADD  CONSTRAINT [PK_jiaofeijine] PRIMARY KEY  NONCLUSTERED ( [id] )
SET IDENTITY_INSERT [jiaofeijine] ONINSERT [jiaofeijine] ([id],[nid],[jine],[kemu],[pid]) VALUES ( 16,2,7000.00,1,10)
INSERT [jiaofeijine] ([id],[nid],[jine],[kemu],[pid]) VALUES ( 17,3,6000.00,1,10)
INSERT [jiaofeijine] ([id],[nid],[jine],[kemu],[pid]) VALUES ( 18,4,5000.00,1,10)
INSERT [jiaofeijine] ([id],[nid],[jine],[kemu],[pid]) VALUES ( 19,2,0.00,1,11)
INSERT [jiaofeijine] ([id],[nid],[jine],[kemu],[pid]) VALUES ( 20,3,0.00,1,11)
INSERT [jiaofeijine] ([id],[nid],[jine],[kemu],[pid]) VALUES ( 21,4,0.00,1,11)
--缴费记录
SET IDENTITY_INSERT [jiaofeijine] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[recordInfo]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [recordInfo]CREATE TABLE [recordInfo] (
[id] [int]  IDENTITY (1, 1)  NOT NULL,
[uid] [int]  NOT NULL, --用户编号
[pid] [int]  NOT NULL, --学期编号
[Amount] [decimal]  (18,2) NOT NULL,  --金额
[gid] [int]  NULL, --年级编号
[cid] [int]  NULL --班级编号
)ALTER TABLE [recordInfo] WITH NOCHECK ADD  CONSTRAINT [PK_recordInfo] PRIMARY KEY  NONCLUSTERED ( [id] )
SET IDENTITY_INSERT [recordInfo] ONINSERT [recordInfo] ([id],[uid],[pid],[Amount],[gid],[cid]) VALUES ( 149,3448,10,1000,2,46)SET IDENTITY_INSERT [recordInfo] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[SchoolUser]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [SchoolUser]--学生记录表
CREATE TABLE [SchoolUser] (
[id] [int]  IDENTITY (1, 1)  NOT NULL,
[username] [varchar]  (50) NOT NULL,
[cid] [int]  NOT NULL, --班级编号
ALTER TABLE [SchoolUser] WITH NOCHECK ADD  CONSTRAINT [PK_SchoolUser] PRIMARY KEY  NONCLUSTERED ( [id] )
SET IDENTITY_INSERT [SchoolUser] ONINSERT [SchoolUser] ([id],[username],[cid]) VALUES ( 3448,N'王玲',41)
INSERT [SchoolUser] ([id],[username],[cid]) VALUES ( 3447,N'王玲',42)
INSERT [SchoolUser] ([id],[username],[cid]) VALUES ( 3446,N'王玲',43)
INSERT [SchoolUser] ([id],[username],[cid]) VALUES ( 3445,N'王玲',44)
INSERT [SchoolUser] ([id],[username],[cid]) VALUES ( 3444,N'王玲',45)
INSERT [SchoolUser] ([id],[username],[cid]) VALUES ( 3443,N'王玲',46)
INSERT [SchoolUser] ([id],[username],[cid]) VALUES ( 3442,N'王玲',47)
INSERT [SchoolUser] ([id],[username],[cid]) VALUES ( 3441,N'王玲',48)
SET IDENTITY_INSERT [SchoolUser] OFF

解决方案 »

  1.   

    学生表只有班级编号cid，怎么和学费金额表的nid或pid关联？
      

  2.   

    --学费减免 一个学期一个学生有多个减免
    CREATE TABLE [jianmian] (
    [id] [int]  IDENTITY (1, 1)  NOT NULL,
    [pid] [int]  NOT NULL,--学期编号
    [nid] [int]  NOT NULL,--年级编号
    [cid] [int]  NOT NULL,--班级编号
    [uid] [int]  NOT NULL, --用户编号
    [username] [varchar]  (50) NULL,
    [kemuid] [int]  NULL,
    [kemu] [varchar]  (50) NULL,
    [jine] [decimal]  (18,2) NULL,  --金额
    [addtime] [datetime]  NULL DEFAULT (getdate()),
    [Del] [int]  NULL,
    [beizhu] [varchar]  (580) NULL)ALTER TABLE [jianmian] WITH NOCHECK ADD  CONSTRAINT [PK_jianmian] PRIMARY KEY  NONCLUSTERED ( [id] )
    SET IDENTITY_INSERT [jianmian] ONINSERT [jianmian] ([id],[pid],[nid],[cid],[uid],[username],[kemuid],[kemu],[jine],[addtime],[Del]) VALUES ( 1,10,4,48,3448,N'王玲',4,N'工资扣除',1000.00,N'2015/1/15 9:01:23',0)
    INSERT [jianmian] ([id],[pid],[nid],[cid],[uid],[username],[kemuid],[kemu],[jine],[addtime],[Del]) VALUES ( 2,10,4,43,3448,N'王玲',5,N'半托减免',1000.00,N'2015/1/15 9:25:17',0)SET IDENTITY_INSERT [jianmian] OFF
    if exists (select * from sysobjects where id = OBJECT_ID('[jiaofeijine]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
    DROP TABLE [jiaofeijine]--每学期每年级的学费金额 
    --年级金额-已交-减免=欠费金额
    CREATE TABLE [jiaofeijine] (
    [id] [int]  IDENTITY (1, 1)  NOT NULL,
    [nid] [int]  NULL,--年级编号
    [jine] [decimal]  (18,2) NULL, --金额
    [kemu] [int]  NULL, 
    [pid] [int]  NULL) --学期编号ALTER TABLE [jiaofeijine] WITH NOCHECK ADD  CONSTRAINT [PK_jiaofeijine] PRIMARY KEY  NONCLUSTERED ( [id] )
    SET IDENTITY_INSERT [jiaofeijine] ONINSERT [jiaofeijine] ([id],[nid],[jine],[kemu],[pid]) VALUES ( 16,2,7000.00,1,10)
    INSERT [jiaofeijine] ([id],[nid],[jine],[kemu],[pid]) VALUES ( 17,3,6000.00,1,10)
    INSERT [jiaofeijine] ([id],[nid],[jine],[kemu],[pid]) VALUES ( 18,4,5000.00,1,10)
    INSERT [jiaofeijine] ([id],[nid],[jine],[kemu],[pid]) VALUES ( 19,2,0.00,1,11)
    INSERT [jiaofeijine] ([id],[nid],[jine],[kemu],[pid]) VALUES ( 20,3,0.00,1,11)
    INSERT [jiaofeijine] ([id],[nid],[jine],[kemu],[pid]) VALUES ( 21,4,0.00,1,11)
    --缴费记录
    SET IDENTITY_INSERT [jiaofeijine] OFF
    if exists (select * from sysobjects where id = OBJECT_ID('[recordInfo]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
    DROP TABLE [recordInfo]CREATE TABLE [recordInfo] (
    [id] [int]  IDENTITY (1, 1)  NOT NULL,
    [uid] [int]  NOT NULL, --用户编号
    [pid] [int]  NOT NULL, --学期编号
    [Amount] [decimal]  (18,2) NOT NULL,  --金额
    [gid] [int]  NULL, --年级编号
    [cid] [int]  NULL --班级编号
    )ALTER TABLE [recordInfo] WITH NOCHECK ADD  CONSTRAINT [PK_recordInfo] PRIMARY KEY  NONCLUSTERED ( [id] )
    SET IDENTITY_INSERT [recordInfo] ONINSERT [recordInfo] ([id],[uid],[pid],[Amount],[gid],[cid]) VALUES ( 149,3448,10,1000,2,46)SET IDENTITY_INSERT [recordInfo] OFF
    if exists (select * from sysobjects where id = OBJECT_ID('[SchoolUser]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
    DROP TABLE [SchoolUser]--学生记录表
    CREATE TABLE [SchoolUser] (
    [id] [int]  IDENTITY (1, 1)  NOT NULL,
    [username] [varchar]  (50) NOT NULL,
    [cid] [int]  NOT NULL, --班级编号
    ALTER TABLE [SchoolUser] WITH NOCHECK ADD  CONSTRAINT [PK_SchoolUser] PRIMARY KEY  NONCLUSTERED ( [id] )
    SET IDENTITY_INSERT [SchoolUser] ONINSERT [SchoolUser] ([id],[username],[cid]) VALUES ( 3448,N'王玲',41)
    INSERT [SchoolUser] ([id],[username],[cid]) VALUES ( 3447,N'王玲',42)
    INSERT [SchoolUser] ([id],[username],[cid]) VALUES ( 3446,N'王玲',43)
    INSERT [SchoolUser] ([id],[username],[cid]) VALUES ( 3445,N'王玲',44)
    INSERT [SchoolUser] ([id],[username],[cid]) VALUES ( 3444,N'王玲',45)
    INSERT [SchoolUser] ([id],[username],[cid]) VALUES ( 3443,N'王玲',46)
    INSERT [SchoolUser] ([id],[username],[cid]) VALUES ( 3442,N'王玲',47)
    INSERT [SchoolUser] ([id],[username],[cid]) VALUES ( 3441,N'王玲',48)
    SET IDENTITY_INSERT [SchoolUser] OFFCREATE TABLE [SchoolClass] (
    [id] [int]  IDENTITY (1, 1)  NOT NULL,
    [nid] [int]  NOT NULL,
    [cName] [varchar]  (20) NOT NULL,
    [addtime] [datetime]  NULL DEFAULT (getdate()))ALTER TABLE [SchoolClass] WITH NOCHECK ADD  CONSTRAINT [PK_SchoolClass] PRIMARY KEY  NONCLUSTERED ( [id] )
    SET IDENTITY_INSERT [SchoolClass] ONINSERT [SchoolClass] ([id],[nid],[cName],[addtime]) VALUES ( 1,2,N'2012级1班',N'2014/10/7 15:40:57')
    INSERT [SchoolClass] ([id],[nid],[cName],[addtime]) VALUES ( 2,2,N'2012级2班',N'2014/10/7 15:41:04')
    INSERT [SchoolClass] ([id],[nid],[cName],[addtime]) VALUES ( 3,2,N'2013级1班',N'2014/10/7 15:41:11')
    INSERT [SchoolClass] ([id],[nid],[cName],[addtime]) VALUES ( 4,2,N'2014级1班',N'2014/10/7 15:41:17')
    INSERT [SchoolClass] ([id],[nid],[cName],[addtime]) VALUES ( 5,2,N'98',N'2014/10/20 10:41:35')
    INSERT [SchoolClass] ([id],[nid],[cName],[addtime]) VALUES ( 6,2,N'104',N'2014/10/20 10:41:42')
    INSERT [SchoolClass] ([id],[nid],[cName],[addtime]) VALUES ( 7,2,N'107',N'2014/10/20 10:41:50')
    INSERT [SchoolClass] ([id],[nid],[cName],[addtime]) VALUES ( 8,2,N'108',N'2014/10/20 10:42:00')
    INSERT [SchoolClass] ([id],[nid],[cName],[addtime]) VALUES ( 9,2,N'109',N'2014/10/20 10:42:18')
    INSERT [SchoolClass] ([id],[nid],[cName],[addtime]) VALUES ( 10,2,N'111',N'2014/10/20 10:42:21')
    SET IDENTITY_INSERT [SchoolClass] OFF
    这是班级表。是和年级挂钩的
      

  3.   

    SchoolUser.cid 对 SchoolClass.id？
    测试数据看不出关联。
      

  4.   

        SELECT u.username,
               c.cName,
               f.pid,
               f.jine - ISNULL(r.Amount,0) - ISNULL(d.jine,0) --年级金额-已交-减免=欠费金额
          FROM SchoolUser u
          JOIN SchoolClass c
            ON c.id = u.cid
          JOIN jiaofeijine f
            ON f.nid = c.nid
     LEFT JOIN recordInfo r
            ON r.uid = u.id
           AND r.pid = f.pid
           AND r.gid = c.nid
           AND r.cid = c.id
     LEFT JOIN (
                    SELECT pid,nid,cid,uid,SUM(jine) jine
                      FROM jianmian
                  GROUP BY pid,nid,cid,uid
               ) d
            ON d.uid = u.id
           AND d.pid = f.pid
           AND d.nid = c.nid
           AND d.cid = c.id
    应该就是这样了。你的测试数据连接后为空，不能贴结果了。