[code=SQL]
表A(title是用户user的产品名,产品名有可能重复)
id title user
11 aaa one
22 bbb tow
33 ccc three
44 aaa four
....
表B(用户销售title产品的数量,因为表A的title可能相同,所以表B用也加入user进行区分)
id title user tongji
1 aaa one 1
2 ccc three 1
3 aaa one 1
4 bbb tow 3
5 ccc three 3
6 aaa one 2
7 aaa four 3
8 aaa four 2
....希望结果(1、产品A在表B中的销售总数量排序;2、如果销售数量重复按表A的ID逆序)
id title tj
44 aaa 5
33 ccc 4
11 aaa 4
22 bbb 3
不好意思啊,再请教个很低给的问题这样查询后怎么输出对应的id,title,tj呢,我是ACCESS数据库的?
[/code]
按产品名:select sum(tongji) as tj,title from b group by title 按人名
select sum(tongji) as tj,user from b group by user
FROM 表A a
INNER JOIN 表B b
ON a.user = b.user AND a.title = b.title
GROUP BY a.id, a.title
ORDER BY cnt DESC, a.id DESC
select a.id,a.title,sum(b.tongji) as snt
from A a join B b on a.[user] = b.[user] and a.title = b.title
order by snt desc,a.id desc--orselect a.id,a.title,sum(b.tongji) as snt
from A a,B b
where a.[user] = b.[user] and a.title = b.title
order by snt desc,a.id desc
SELECT a.id, a.title, SUM(b.tongji) as cnt
FROM 表A a
left join 表B b
ON a.user = b.user AND a.title = b.title
GROUP BY a.id, a.title
ORDER BY SUM(b.tongji) DESC, a.id DESC
select * from
(
select a.id,b.title,b.tj from a a
inner join
(
select title,user,sum(tongji) as tj from B group by title,user
) on a.title=b.title where a.user=b.user
) c order by c.tj desc
(
select a.id,b.title,b.tj
from a a
inner join
(
select title,sum(tongji)
from b
where exists (select 1 from a where a.title=b.title and a.user=b.user)) on a.title=b.title where a.user=b.user
) c
order by c.tj desc