日期:2014-05-18  浏览次数:20416 次

应该算是sql嵌套查询,求帮忙!
SQL code

[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]

------解决方案--------------------
SQL code

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

--or

select 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

------解决方案--------------------
SQL code

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
------解决方案--------------------
SQL code
select * from  
(
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