sql

use EMDBAY
go

if exists (select * from sysobjects where name='TTest_User')
drop table TTest_User
go
create table TTest_User
(
    UserId int  identity(1,1) not null primary key,
    UserName varchar(20) not null
)
go

use EMDBAY
go

if exists (select * from sysobjects where name='TTest_Order')
drop table TTest_Order
go
create table TTest_Order
(
    OrderId int identity(1,1) not null,
    UserId int references TTest_User(UserId),
    OrderAmount int not null
)
go
insert into TTest_User (UserName) values('admin');
insert into TTest_User (UserName) values('张三');
insert into TTest_User (UserName) values('李四');
insert into TTest_User (UserName) values('0126');

select * from TTest_User;

insert into TTest_Order (UserId,OrderAmount) values(1,5);
insert into TTest_Order (UserId,OrderAmount) values(2,6);
insert into TTest_Order (UserId,OrderAmount) values(3,7);
insert into TTest_Order (UserId,OrderAmount) values(1,4);

select * from TTest_User;
select * from TTest_Order;
select t1.UserName, t2.* from TTest_User t1,(select UserId ,COUNT(OrderAmount) as 总订单数 from TTest_Order group by (UserId)) t2 where t1.UserId =t2.UserId;