表1 (酒水表)
id , 名称
1, 啤酒
2, 二锅头
3, 茅台
4, 红酒
表2 (酒水消费表),
酒ID, 消费数量
1, 1
2, 1
3, 1
3, 1
1, 1
要查询表1 , 按被点的酒水的总量排序 如果没被点过的酒也要有数量
ID, 名称, 被点次数
1, 啤酒 , 2
2, 二锅头 , 1
3, 茅台 , 2
4, 红酒 , 0
id , 名称
1, 啤酒
2, 二锅头
3, 茅台
4, 红酒
表2 (酒水消费表),
酒ID, 消费数量
1, 1
2, 1
3, 1
3, 1
1, 1
要查询表1 , 按被点的酒水的总量排序 如果没被点过的酒也要有数量
ID, 名称, 被点次数
1, 啤酒 , 2
2, 二锅头 , 1
3, 茅台 , 2
4, 红酒 , 0
by t1.id,t1.name
SELECT T.酒ID,T.名称,ISNULL(LO.消费数量,0) AS 消费数量
FROM T LEFT JOIN
(SELECT 酒ID,sum(消费数量) AS 消费数量 FROM S GROUP BY 酒ID) LO ON T.ID = LO.ID
ORDER BY LO.消费数量
CREATE TABLE TEST1(ID INT,NAME VARCHAR(20))
INSERT INTO TEST1 SELECT 1,'啤酒' UNION ALL
SELECT 2,'二锅头' UNION ALL
SELECT 3,'茅台' UNION ALL
SELECT 4,'红酒'
GOCREATE TABLE TEST2 (ID INT,NUM INT)
INSERT INTO TEST2 SELECT 1,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,1 UNION ALL
SELECT 3,1 UNION ALL
SELECT 1,1
GOSELECT * FROM TEST1;
SELECT * FROM TEST2;
SELECT TEST1.*,ISNULL(SUM(TEST2.NUM),0)[被点击数] FROM TEST1 LEFT JOIN TEST2 ON TEST1.ID=TEST2.ID GROUP BY TEST1.ID,TEST1.NAME
ID NAME
----------- --------------------
1 啤酒
2 二锅头
3 茅台
4 红酒(所影响的行数为 4 行)ID NUM
----------- -----------
1 1
2 1
3 1
3 1
1 1(所影响的行数为 5 行)ID NAME 被点击数
----------- -------------------- -----------
2 二锅头 1
4 红酒 0
3 茅台 2
1 啤酒 2(所影响的行数为 4 行)
select a.id, name, isnull(sum(b.quantity),0)as shuliang
from jiushui a left join jiushuishuliang b
on a.id=b.id
group by a.id,a.name
order by sum(b.quantity)
left join (select 酒id as id,sum(消费数量) as xfsl from 表2 group by 酒id) b
on a.id=b.id
order by isnull(b.xfsl,0) desc
--drop table warname,sale
create table warname(id int,name1 varchar(10))
create table sale(warnameid int ,total int)
insert into warname select 1, '啤酒' union all
select 2,'二锅头' union all
select 3,'茅台' union all
select 4,'红酒 ' insert into sale select 1,1 union all
select 2,1 union all
select 3,1 union all
select 3,1 union all
select 1,1 select id,name1,isnull(sum(total),0) as coun from (warname left join sale on warname.id=sale.warnameid)
group by warname.id,name1
order by coun desc drop table warname,sale
id name1 coun
3 茅台 2
1 啤酒 2
2 二锅头 1
4 红酒 0
CREATE TABLE TEST1(ID INT,NAME VARCHAR(20))
INSERT INTO TEST1 SELECT 1,'啤酒' UNION ALL
SELECT 2,'二锅头' UNION ALL
SELECT 3,'茅台' UNION ALL
SELECT 4,'红酒'
GOCREATE TABLE TEST2 (ID INT,NUM INT)
INSERT INTO TEST2 SELECT 1,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,1 UNION ALL
SELECT 3,1 UNION ALL
SELECT 1,1
GO
--DROP TABLE TEST1,TEST2SELECT T1.*,
ISNULL(T2.消费数量,0) 消费数量
FROM TEST1 T1
LEFT JOIN
(SELECT ID,SUM(NUM)AS '消费数量' FROM TEST2 GROUP BY ID)AS T2
ON T1.ID=T2.ID/*ID NAME 消费数量
----------- -------------------- -----------
1 啤酒 2
2 二锅头 1
3 茅台 2
4 红酒 0(所影响的行数为 4 行)*/
INSERT INTO TEST1 SELECT 1,'啤酒' UNION ALL
SELECT 2,'二锅头' UNION ALL
SELECT 3,'茅台' UNION ALL
SELECT 4,'红酒'
GOCREATE TABLE TEST2 (ID INT,NUM INT)
INSERT INTO TEST2 SELECT 1,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,1 UNION ALL
SELECT 3,1 UNION ALL
SELECT 1,1
GOselect *,
被点次数=isnull((select sum(NUM) from TEST2 where ID = t.ID),0)
from TEST1 t
GO
/*
ID NAME 被点次数
----------- -------------------- -----------
1 啤酒 2
2 二锅头 1
3 茅台 2
4 红酒 0(4 行受影响)
*/