创建三个表
create table _user ( ID int CONSTRAINT pk_user primary key, Name varchar(10) ) create table product ( ID int CONSTRAINT pk_product primary key, Name varchar(10), price int, comment varchar(10) ) create table subscribe ( ID int primary key, _user_id int CONSTRAINT fk_subscribe_user FOREIGN KEY references _user(ID) ON UPDATE CASCADE ON DELETE CASCADE , product_id int CONSTRAINT fk_subscribe_product FOREIGN KEY references product(ID) ON UPDATE CASCADE ON DELETE CASCADE , _Time int ) insert into _user select 1,'刘德华' union all select 2,'周润发' union all select 3,'张惠妹' ; insert into _user select 1,'刘德华' union all select 2,'周润发' union all select 3,'张惠妹' ; insert into product select 1,'首映专区',30,'包月' union all select 2,'看大片',20,'包月' union all select 3,'全能包',199,'包年' ; insert into subscribe select 1,1,1,20155 union all select 2,1,2,20156 union all select 3,2,3,20158 ;对应的答案
select u.name,p.name,s._time from (_user as u right join subscribe as s on u.id = s._user_id) left join product as p on s.id = p.id; select p.name,count(s.product_id)as 次数, p.price*count(s.product_id) as preice from subscribe as s right join product as p on s.product_id = p.id group by p.name,s.product_id,p.price; select ID,Name from (select u.ID,u.Name,s._user_id from _user as u left join subscribe as s on u.ID =s._user_id)as A where _user_id IS NULL; delete from subscribe where _user_id = 1
