duanpo1498
duanpo1498
2011-10-24 15:43

如何在一个表中显示多行,另一个表中的一行作为单行

已采纳

I have two tables. Table "user" contains user_id, user_name Table "Transactions" contains user_id, transactions

Eg:

TABLE USER:
user_id, user_name
1, Ram
2, John
3, Rahim

TABLE Transactions
user_id, transactions
1,  500
1, 300
2, 250
1, 450
3, 100
1, 250
3, 50

I want to display the result as:

user_name, Total
Ram, 1500*
John, 250
Rahim, 150
  • it is the sum of all transactions of that particular user

How can I do this?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

3条回答

  • duangou1868 duangou1868 10年前
    SELECT 
        u.user_name, 
        SUM(t.transactions) Total
    FROM users u
    LEFT JOIN transactions t ON t.user_id = u.user_id
    GROUP BY t.user_id
    

    I think you should use LEFT JOIN because it should select user's total (NULL) in any case, even if it's 0

    点赞 评论 复制链接分享
  • douzhenqun1271 douzhenqun1271 10年前

    Try

    SELECT
        u.user_id,
        sum(t.transactions) as total
    FROM
        user u
        JOIN transactions t ON u.user_id = t.user_id
    GROUP BY
        u.user_id
    
    点赞 评论 复制链接分享
  • doufocheng6233 doufocheng6233 10年前
    SELECT u.user_id , SUM(t.transactions) AS total
    FROM user AS u JOIN transactions AS t
    GROUP BY u.user_id
    
    点赞 评论 复制链接分享

相关推荐