Select A.ID,Company,Contract,Fee,Status from A Inner Join B On A.ID=B.ID Inner Join C On A.ID=C.ID Where C.Status=0
select a.Company,b.Contract,c.fee from A a,B b,C c where a.id=b.id and a.id=c.id and c.Status=0
不对不对,各位大哥都理解错我的意思了啊!b表中的Contract存入的是合同的信息,不是记录的数量。 c表中的Fee存入的是金额,不是记录的数量。数据是这样的存: A: B: C: ID Company ID Contract ID Fee Status 01 comp1 01 contract1 01 100 0 01 contract2 01 50 1 01 150 1应该是要用到Count函数才对的啊!这样应该比较清楚了吧,请高手在看看!学习,关注……
select company, (select count(contract) from B where id=a.id) as contractC, (select count(Status) from C where id=a.id and Status=0) as StatusC from A a
--创建表 create table A ( ID varchar(20), Company varchar(100) )create table B ( ID varchar(20), Contract varchar(100) )create table C ( ID varchar(20), Fee float(8), Status bit default 1 )--添加测试数据 insert A select '01','comp1' insert B select '01','contract1' union select '01','contract2' insert C select '01',100,0 union select '01',50,1 union select '01',150,1--查询 select A.ID,A.Company, (select count(*) from B where [ID]=A.ID) as '合同数量', (select count(*) from C where [ID]=A.[ID] and Status=0) as '费用数量' from A --删除表 drop table A drop table B drop table C
select company, (select count(contract) from B where id=a.id) as contractcount, (select count(Status) from C where id=a.id and Status=0) as Statuscount from A a
Select A.ID,Company,Contract,Fee,Status from A
Inner Join B On A.ID=B.ID
Inner Join C On A.ID=C.ID
Where C.Status=0
from A a,B b,C c
where a.id=b.id and a.id=c.id and c.Status=0
c表中的Fee存入的是金额,不是记录的数量。数据是这样的存:
A: B: C:
ID Company ID Contract ID Fee Status
01 comp1 01 contract1 01 100 0
01 contract2 01 50 1
01 150 1应该是要用到Count函数才对的啊!这样应该比较清楚了吧,请高手在看看!学习,关注……
(select count(contract) from B where id=a.id) as contractC,
(select count(Status) from C where id=a.id and Status=0) as StatusC
from A a
create table A
(
ID varchar(20),
Company varchar(100)
)create table B
(
ID varchar(20),
Contract varchar(100)
)create table C
(
ID varchar(20),
Fee float(8),
Status bit default 1
)--添加测试数据
insert A select '01','comp1'
insert B select '01','contract1' union select '01','contract2'
insert C select '01',100,0 union select '01',50,1 union select '01',150,1--查询
select A.ID,A.Company,
(select count(*) from B where [ID]=A.ID) as '合同数量',
(select count(*) from C where [ID]=A.[ID] and Status=0) as '费用数量'
from A --删除表
drop table A
drop table B
drop table C
(select count(contract) from B where id=a.id) as contractcount,
(select count(Status) from C where id=a.id and Status=0) as Statuscount
from A a