create table product ( p_id char(50) not null primary key, p_name char(50), p_price char(50) ) create table [order] ( o_pid char(50), o_user char(50) ) DELETE product insert into product select '001','www','123' union all select '002','AAA','103' union all select '003','bbbbb','1113' union all select '004','cccc','1123'DELETE [order] select * from productinsert into [order] select '001','sDDDs' union all select '002','ss' union all select '002','TTT' union all select '001','FFFF' union all select '001','sEEEEs' union all select '001','ss' union all select '004','WWW' union all select '004','W' union all select '004','sWW' union all select '004','sWWWs' union all select '004','sFFFs' union all select '004','sAAAAs' union all select '001','sCCCCs' SELECT A.*, ISNULL(B.CNT,0) AS CNT FROM product A LEFT JOIN (SELECT o_pid ,COUNT(1)AS CNT FROM [order] GROUP BY o_pid) B ON A.p_id = B.o_pid ORDER BY CNT DESC-------------------------------- p_id,p_name,p_price,o_usersume 004 cccc 1123 6 001 www 123 5 002 AAA 103 2 003 bbbbb 1113 0
create table product
(
p_id char(50) not null primary key,
p_name char(50),
p_price char(50)
)
create table [order]
(
o_pid char(50),
o_user char(50)
)
DELETE product
insert into product
select '001','www','123' union all
select '002','AAA','103' union all
select '003','bbbbb','1113' union all
select '004','cccc','1123'DELETE [order]
select * from productinsert into [order]
select '001','sDDDs' union all
select '002','ss' union all
select '002','TTT' union all select '001','FFFF' union all
select '001','sEEEEs' union all select '001','ss' union all
select '004','WWW' union all
select '004','W' union all
select '004','sWW' union all
select '004','sWWWs' union all
select '004','sFFFs' union all
select '004','sAAAAs' union all
select '001','sCCCCs'
SELECT A.*, ISNULL(B.CNT,0) AS CNT
FROM product A LEFT JOIN (SELECT o_pid ,COUNT(1)AS CNT FROM [order] GROUP BY o_pid) B
ON A.p_id = B.o_pid ORDER BY CNT DESC--------------------------------
p_id,p_name,p_price,o_usersume
004 cccc 1123 6
001 www 123 5
002 AAA 103 2
003 bbbbb 1113 0
--------------------------------
p_id p_name p_price o_usersume
004 cccc 1123 6
001 www 123 5
002 AAA 103 2
003 bbbbb 1113 0