Create Table A
(Plancode Nvarchar(20),
Clientname Nvarchar(20),
Worktype Varchar(20),
shipdate datetime,
)
Create Table B
(plancode Nvarchar(20),
quantity int
)
Create Table C
(
plancode Nvarchar(20),
Q1 Int,
Q2 Int,
Q3 Int
)
Insert A Select 10001 , N'clientA', 'AA', '2010-4-1'
UNION ALL Select 10002, N'clientA', 'AB', '2010-4-1'
Insert B Select 10001 , 20
Union All Select 10001 , 20
Union All Select 10001 , 20
Union All Select 10001 , 20
Union All Select 10001 , 20
Union All Select 10002, 20
Insert C Select 10001, 1,10,20
Union All Select 10002, 20,1,1
Union All Select 10001, 3,3,0
GO
想得到如下结果plancode clientname worktype quaintity q1 q2 q3
--------------------------------------------------------------
100001 clientA aa 100 4 13 20select a.plancode,a.clientname,a.worktype
from a
left join(select sum(quantity) from b where b.plancode=a.plancode)
b on b.plancode=a.plancode
left join(select sum(q1) q1,sum(q2)q2,sum(q3) q3 from c where c.plancode=a.plancode)
c on c.plancode=a.plancode
where a.plancode='100001'我用上面这条总是提示什么
列前缀 'a' 与查询中所用的表名或别名不匹配。
是怎么回事?
(Plancode Nvarchar(20),
Clientname Nvarchar(20),
Worktype Varchar(20),
shipdate datetime,
)
Create Table B
(plancode Nvarchar(20),
quantity int
)
Create Table C
(
plancode Nvarchar(20),
Q1 Int,
Q2 Int,
Q3 Int
)
Insert A Select 10001 , N'clientA', 'AA', '2010-4-1'
UNION ALL Select 10002, N'clientA', 'AB', '2010-4-1'
Insert B Select 10001 , 20
Union All Select 10001 , 20
Union All Select 10001 , 20
Union All Select 10001 , 20
Union All Select 10001 , 20
Union All Select 10002, 20
Insert C Select 10001, 1,10,20
Union All Select 10002, 20,1,1
Union All Select 10001, 3,3,0
GO
想得到如下结果plancode clientname worktype quaintity q1 q2 q3
--------------------------------------------------------------
100001 clientA aa 100 4 13 20select a.plancode,a.clientname,a.worktype
from a
left join(select sum(quantity) from b where b.plancode=a.plancode)
b on b.plancode=a.plancode
left join(select sum(q1) q1,sum(q2)q2,sum(q3) q3 from c where c.plancode=a.plancode)
c on c.plancode=a.plancode
where a.plancode='100001'我用上面这条总是提示什么
列前缀 'a' 与查询中所用的表名或别名不匹配。
是怎么回事?
,(select sum(q1) q1,sum(q2)q2,sum(q3) q3 from c where c.plancode=a.plancode)from a
where a.plancode='100001'
from a
left join(select sum(quantity) from b ,a where b.plancode=a.plancode)
b on b.plancode=a.plancode
left join(select sum(q1) q1,sum(q2)q2,sum(q3) q3 from c,a where c.plancode=a.plancode)
c on c.plancode=a.plancode
where a.plancode='100001'
就是这个意思。看看select列表中是否引用错误
from a
outer apply(select sum(quantity) quantity from b where b.plancode=a.plancode) b
outer apply(select sum(q1) q1,sum(q2)q2,sum(q3) q3 from c where c.plancode=a.plancode) c
where a.plancode='100001'2005以上版本使用
from a
left join
(select B.plancode,sum(quantity) from b ,a where b.plancode=a.plancode GROUP BY B.plancode)b on b.plancode=a.plancode
left join(select B.plancode,sum(q1) q1,sum(q2)q2,sum(q3) q3 from c,a where c.plancode=a.plancode GROUP BY B.plancode)c on c.plancode=a.plancode where a.plancode='100001'具体要这样修改才对,楼主试试
Server: Msg 116, Level 16, State 1, Line 1
当没有用 EXISTS 引入子查询时,在选择列表中只能指定一个表达式。第二提示:
Server: Msg 8155, Level 16, State 2, Line 1
没有为第 1 列(属于 'b')指定列。我用的是SQL2000
from a
left join
b on b.plancode=a.plancode
left join
c on c.plancode=a.plancode
where a.plancode='10001'是不是这样啊,你怎么写得乱七八糟的啊?
(select sum(quantity) from b where b.plancode=a.plancode) as quaintity ,
(select sum(q1) from c where c.plancode=a.plancode) as q1,
(select sum(q2) q2 from c where c.plancode=a.plancode) as q2,
(select sum(q3) q3 from c where c.plancode=a.plancode) as q3
from a
where a.plancode='10001'
plancode clientname worktype quaintity q1 q2 q3
-------------------- -------------------- -------------------- ----------- ----------- ----------- -----------
10001 clientA AA 100 4 13 20(所影响的行数为 1 行)
select a.plancode,a.clientname,a.worktype,b.*,c.*
from a
left join(select plancode, sum(quantity)quantity from b
group by plancode)
b on b.plancode=a.plancode
left join(select sum(q1) q1,sum(q2)q2,sum(q3) q3 from c
group by plancode )
c on c.plancode=a.plancode
where a.plancode='100001'