Table1:
ID Name Amount
1 test 14
2 ttt 23
Table2:
SSID ID
1 1
2 1
3 1
4 2
5 2现在要查询Table1中的数量
SQL该怎么写呀?
各位帮看看,小弟先谢了
sql用的不是太好,各位帮帮忙
ID Name Amount
1 test 14
2 ttt 23
Table2:
SSID ID
1 1
2 1
3 1
4 2
5 2现在要查询Table1中的数量
SQL该怎么写呀?
各位帮看看,小弟先谢了
sql用的不是太好,各位帮帮忙
ID Name Amount
1 test 11
2 ttt 21
要减去Table2中的记录数
if object_id('table1') >0
drop table table1
create table table1
(
id int,
name varchar(20),
amount int
)
if object_id('table2') >0
drop table table2
create table table2
(
ssid int,
id int
)insert into table1 values(1,'test',14)
insert into table1 values(2,'ttt',23)insert into table2 values(1,1)
insert into table2 values(2,1)
insert into table2 values(3,1)
insert into table2 values(4,2)
insert into table2 values(5,2)select * from table1
select * from table2select a.id,a.name,a.amount - (select count(*) from table2 as b where a.id =b.id )
from table1 as a
结果1 test 11
2 ttt 21
(
select ID, count(*) as Amount from Table2 group by ID
)select Table1.ID,Table1.Name,Table1.Amount-Table3.Amount from Table1,Table3 where Table1.ID=Table3.ID