if object_id('member2') is not null drop table member2
create table member2(
m_id char(1) PRIMARY KEY,
m_name char(10))
if object_id('income2') is not null drop table income2
create table income2 (
i_id char(2) PRIMARY KEY,
i_mid char(1),
i_qtt int
)
if object_id('outcome2') is not null drop table outcome2
create table outcome2(
o_id char(3) PRIMARY KEY,
o_iid char(2),
o_qtt int
)
insert into member2 (m_id, m_name) Values('1','张三')
insert into member2 (m_id, m_name) Values('2','李四') insert into income2 (i_id, i_mid, i_qtt) Values('a1','1', 30)
insert into income2 (i_id, i_mid, i_qtt) Values('a2','1', 70) insert into outcome2 (o_id, o_iid, o_qtt) Values('oo1','1', 10) select m.m_id,isnull(i.n1,0) n1,isnull(o.n2,0) n2
from member2 m
left outer join
(select i_mid, sum(i_qtt) n1 from income2 group by i_mid) i
on m.m_id=i.i_mid
left outer join
(select o_iid,sum(o_qtt) n2 from outcome2 group by o_iid) o
on m.m_id=o.o_iid /*
m_id n1 n2
---- ----------- -----------
1 100 10
2 0 0(所影响的行数为 2 行)
*/
create table member2(
m_id char(1) PRIMARY KEY,
m_name char(10))
if object_id('income2') is not null drop table income2
create table income2 (
i_id char(2) PRIMARY KEY,
i_mid char(1),
i_qtt int
)
if object_id('outcome2') is not null drop table outcome2
create table outcome2(
o_id char(3) PRIMARY KEY,
o_iid char(2),
o_qtt int
)
insert into member2 (m_id, m_name) Values('1','张三')
insert into member2 (m_id, m_name) Values('2','李四') insert into income2 (i_id, i_mid, i_qtt) Values('a1','1', 30)
insert into income2 (i_id, i_mid, i_qtt) Values('a2','1', 70) insert into outcome2 (o_id, o_iid, o_qtt) Values('oo1','1', 10) select m.m_id,isnull(i.n1,0) n1,isnull(o.n2,0) n2
from member2 m
left outer join
(select i_mid, sum(i_qtt) n1 from income2 group by i_mid) i
on m.m_id=i.i_mid
left outer join
(select o_iid,sum(o_qtt) n2 from outcome2 group by o_iid) o
on m.m_id=o.o_iid /*
m_id n1 n2
---- ----------- -----------
1 100 10
2 0 0(所影响的行数为 2 行)
*/
解决方案 »
- 测试sql语句
- xml文档要用什么工具浏览呀?我用ie打开看到的只是代码呀?
- sql报表语句咨询
- sql文件。
- sql server 2008 使用导入导出向导 , 从oracle 中导入数据(表) ????
- 数据导入问题!急!
- (给100分!!!)大家有没有遇到这样的问题,大数据库查询时如果在查询过程中得到当前的数据库操作进度?
- 急救,导入数据时候利用OpenDatasource时产生的问题,请大家帮忙一下。
- 求救!!!vfp ,控件 optiongroup1, 属性 ButtonCount=N,如何给第N个OPtionN.Caption赋值
- sql 语句
- 急,昨天把数据库导出之后,然后重新导入,就出现很多表的id字段是不允许空的,可是原本的数据库就是不允许空的,为什么恢复了之后出问题???
- 请教复杂的行转列问题
create table member2(
m_id char(1) PRIMARY KEY,
m_name char(10))
if object_id('income2') is not null drop table income2
create table income2 (
i_id char(2) PRIMARY KEY,
i_mid char(1),
i_qtt int
)
if object_id('outcome2') is not null drop table outcome2
create table outcome2(
o_id char(3) PRIMARY KEY,
o_iid char(2),
o_qtt int
)
insert into member2 (m_id, m_name) Values('1','张三')
insert into member2 (m_id, m_name) Values('2','李四') insert into income2 (i_id, i_mid, i_qtt) Values('a1','1', 30)
insert into income2 (i_id, i_mid, i_qtt) Values('a2','1', 70) insert into outcome2 (o_id, o_iid, o_qtt) Values('oo1','1', 10)
select t.m_id ,
n1 = isnull((select sum(i_qtt) from income2 where i_mid = t.m_id),0) ,
n1 = isnull((select sum(o_qtt) from outcome2 where o_iid = t.m_id),0)
from member2 t
order by t.m_iddrop table member2 , income2 , outcome2 /*
m_id n1 n1
---- ----------- -----------
1 100 10
2 0 0(所影响的行数为 2 行)*/
create table member2(
m_id char(1) PRIMARY KEY,
m_name char(10))
if object_id('income2') is not null drop table income2
create table income2 (
i_id char(2) PRIMARY KEY,
i_mid char(1),
i_qtt int
)
if object_id('outcome2') is not null drop table outcome2
create table outcome2(
o_id char(3) PRIMARY KEY,
o_iid char(2),
o_qtt int
)
insert into member2 (m_id, m_name) Values('1','张三')
insert into member2 (m_id, m_name) Values('2','李四') insert into income2 (i_id, i_mid, i_qtt) Values('a1','1', 30)
insert into income2 (i_id, i_mid, i_qtt) Values('a2','1', 70) insert into outcome2 (o_id, o_iid, o_qtt) Values('oo1','1', 10)
select t.m_id ,
n1 = isnull((select sum(i_qtt) from income2 where i_mid = t.m_id),0) ,
n1 = isnull((select sum(o_qtt) from outcome2 where o_iid = t.m_id),0)
from member2 t
order by t.m_iddrop table member2 , income2 , outcome2 /*
m_id n1 n2
---- ----------- -----------
1 100 10
2 0 0(所影响的行数为 2 行)*/
n1 = isnull((select sum(i_qtt) from income2 where i_mid = t.m_id),0) ,
n2 = isnull((select sum(o_qtt) from outcome2 where o_iid = t.m_id),0)
from member2 t
order by t.m_id/*
m_id n1 n2
---- ----------- -----------
1 100 10
2 0 0(所影响的行数为 2 行)*/
这可不行!!!member 对应多个 income
income 对应多个 outcome说好了是按照每笔收入支出,您这样改不成了搞不清支出在那笔收入上了。您这样做只得到了每个人的总支出。可问题没解决。
create table member2(
m_id char(1) PRIMARY KEY,
m_name char(10))
if object_id('income2') is not null drop table income2
create table income2 (
i_id char(2) PRIMARY KEY,
i_mid char(1),
i_qtt int
)
if object_id('outcome2') is not null drop table outcome2
create table outcome2(
o_id char(3) PRIMARY KEY,
o_iid char(2),
o_qtt int
)
insert into member2 (m_id, m_name) Values('1','张三')
insert into member2 (m_id, m_name) Values('2','李四') insert into income2 (i_id, i_mid, i_qtt) Values('a1','1', 30)
insert into income2 (i_id, i_mid, i_qtt) Values('a2','1', 70) insert into outcome2 (o_id, o_iid, o_qtt) Values('oo1','a1', 10) select m.m_id,isnull(i.n1,0) n1,isnull(c.n2,0) n2
from member2 m
left outer join
(select i_mid, sum(i_qtt) n1 from income2 group by i_mid) i
on m.m_id=i.i_mid
left outer join
(select a.i_mid, SUM(b.o_qtt) AS n2 from INCOME2 as a inner join OUTCOME2 as b on a.i_id=b.o_iid group by a.i_mid) as c
on m.m_id=c.i_mid/*
m_id n1 n2
---- ----------- -----------
1 100 10
2 0 0(所影响的行数为 2 行)
*/
有三张表:第一张表是人员,第二张表是收入,第三张表是支出。要求支出按照每笔收入记录。三张表都是一对多的关系。
第一张 【Member】:mid(PK), name
第二张 【income】:inid(PK), in_mid(FK), n1
第三张 【outcome】:oid(PK), o_inid(FK), n2编写SQL如下:
SELECT dbo.MEMBER.name, SUM(dbo.INCOME.n1) AS insum,
SUM(dbo.OUTCOME.n2) AS outsum
FROM dbo.MEMBE LEFT OUTER JOIN
dbo.INCOME ON dbo.MEMBE.mid = dbo.INCOME.in_mid LEFT OUTER JOIN
dbo.OUTCOME ON dbo.INCOME.inid = dbo.OUTCOME.o_inid
GROUP BY dbo.MEMBER.name
*//*
这是楼主的提供的语句,从这里我们可以看出三个表之间的关联关系:
MEMBE.mid = INCOME.in_mid
INCOME.inid = OUTCOME.o_inid小梁当时偷梁换柱了:INCOME.in_mid=OUTCOME.o_inid
我回过头看小梁的语句,没问题啊,关联的很好,我没回头看我的句子。晕,我理了半天才理清,呵呵*/if object_id('member') is not null drop table member
create table member(
mid char(1) PRIMARY KEY,
name char(10))
if object_id('income') is not null drop table income
create table income (
inid char(2) PRIMARY KEY,
in_mid char(1),
n1 int
)
if object_id('outcome') is not null drop table outcome
create table outcome(
oid char(3) PRIMARY KEY,
o_inid char(2),
n2 int
)
insert into member (mid, name) Values('1','张三')
insert into member (mid, name) Values('2','李四')insert into income (inid, in_mid, n1) Values('a1','1', 30)
insert into income (inid, in_mid, n1) Values('a2','1', 70)insert into outcome (oid, o_inid, n2) Values('oo1','a1', 10)SELECT
a.name,
b.insum,
c.outsum
from
member as a
left join
(select in_mid, SUM(n1) AS insum from income group by in_mid) as b
on a.mid=b.in_mid
left join
-->可以,这里要兜个弯
(select a.in_mid, SUM(b.n2) AS outsum from income as a inner join OUTCOME as b on a.inid=b.o_inid group by a.in_mid) as c
on a.mid=c.in_mid/*
name insum outsum
---------- ----------- -----------
张三 100 10
李四 NULL NULL
*/
create table member2(
m_id char(1) PRIMARY KEY,
m_name char(10))
if object_id('income2') is not null drop table income2
create table income2 (
i_id char(2) PRIMARY KEY,
i_mid char(1),
i_qtt int
)
if object_id('outcome2') is not null drop table outcome2
create table outcome2(
o_id char(3) PRIMARY KEY,
o_iid char(2),
o_qtt int
)
insert into member2 (m_id, m_name) Values('1','张三')
insert into member2 (m_id, m_name) Values('2','李四') insert into income2 (i_id, i_mid, i_qtt) Values('a1','1', 30)
insert into income2 (i_id, i_mid, i_qtt) Values('a2','1', 70) insert into outcome2 (o_id, o_iid, o_qtt) Values('oo1','a1', 10)
select t.m_id ,
n1 = isnull((select sum(i_qtt) from income2 where i_mid = t.m_id),0) ,
n2 = isnull((select sum(o_qtt) from outcome2 m,income2 n where t.m_id = n.i_mid and n.i_id = m.o_iid),0)
from member2 t
order by t.m_iddrop table member2 , income2 , outcome2 /*
m_id n1 n2
---- ----------- -----------
1 100 10
2 0 0(所影响的行数为 2 行)*/
我回过头看小梁的语句,没问题啊,关联的很好,我没回头看我的句子。晕,我理了半天才理清,呵呵-------------
哈哈..
我猜当时肯定是被我扰乱了思路..
--try:create table member2(
m_id char(1) PRIMARY KEY,
m_name char(10))
if object_id('income2') is not null drop table income2
create table income2 (
i_id char(2) PRIMARY KEY,
i_mid char(1),
i_qtt int
)
if object_id('outcome2') is not null drop table outcome2
create table outcome2(
o_id char(3) PRIMARY KEY,
o_iid char(2),
o_qtt int
)
insert into member2 (m_id, m_name) Values('1','张三')
insert into member2 (m_id, m_name) Values('2','李四') insert into income2 (i_id, i_mid, i_qtt) Values('a1','1', 30)
insert into income2 (i_id, i_mid, i_qtt) Values('a2','1', 70) insert into outcome2 (o_id, o_iid, o_qtt) Values('oo1','a1', 10)
select a.m_id,n1=isnull(b.s,0),n2=isnull(c.n,0)
from member2 a
left join (select i_mid,sum(i_qtt)s from income2 group by i_mid)b
on a.m_id=b.i_mid
left join
(select i_mid,sum(o_qtt)n from outcome2 left join income2
on outcome2.o_iid=income2.i_id group by i_mid)c
on a.m_id=c.i_mid
/*回复:代码20080512007 总:00000000007 */
/*主题:三表汇总 */
/*作者:二等草 */
/******************************************//************例子数据 begin****************/
declare @member table(mid int,name varchar(10))
insert into @member values(1,'张三')
insert into @member values(2,'李四')declare @income table(inid int,in_mid int,n1 int)
insert into @income values(1,1,100)
insert into @income values(2,2,80)
insert into @income values(3,2,90)declare @outcome table(oid int,o_inid int,n2 int)
insert into @outcome values(1,1,50)
insert into @outcome values(2,1,30)
insert into @outcome values(3,2,10)
/************例子数据 end******************//************代码 begin***************/
select a.mid,a.name,sum(n1),sum(n2) from
@member a
,(select in_mid,n1,(select sum(n2) from @outcome where o_inid = a.inid) n2 from @income a) b
where a.mid = b.in_mid
group by a.mid,a.name
order by a.mid/************代码 end*****************//************结果 begin***************
mid name
----------- ---------- ----------- -----------
1 张三 100 80
2 李四 170 10
************结果 end*****************//************清除*************************/
程序员在深圳QQ群,交流产生思想碰撞.部份专业群:
程序员在深圳c++群15195967
程序员在深圳英语学习群:23864353
程序员在深圳c++Ⅱ17409451
程序员在深圳嵌入式开发群37489763
程序员在深圳移动开发群31501597
程序员在深圳创业群33653422部份高级程序员群:
高级群I:17538442
高级群II:7120862部份初、中级程序员群:
第三群:2650485
第五群:29537639
第四群:28702746
第六群:10590618
第七群:10543585
第八群:12006492
第九群:19063074
第十群:2883885
第十一群:25460595
第十二群:9663807深圳程序员QQ群联盟成立三年多,拥有三十个以上的QQ群,人数超二千多人,大量经验丰富的老手,成员从业于大公司(如微软、IBM,SUN,华为)、来自国内著名高校和研究院成员,和有丰富实践经验的高级程序员、系统分析员(包括参加过上亿元的项目的架构师),有很热爱技术的成员(包括自己写操作系统),还有少数女程序员。推荐:深程高级群I:17538442 深程高级群II:7120862 (深程高级群不欢迎新手,如果在深圳,月薪6K以下的别加入) c++:15195967 mobile:31501597嵌入式:37489763
——————————————————————————————————————————
群内交流解决问题的方法,一般难以直接得到答案,问题的解决还是要靠自己。(授之以渔与授之以鱼的关系)。
*****************************************************************************