从一个表中选择一列，从另一个表中选择其他列

I have the tables :

我有桌子:

1. Users : user_id, user_name    
2. Transaction : user_id, transaction_number, transaction_date

I want to get the user_id, transaction_number, transaction_date for a particular transaction_date. The first table may contain repeated user_id for different dates and may not contain all user_id for a particular date. The result must contain all user_ids and its transaction_number and transaction dates are null if the user_id has no any transaction entries in the transaction table.

我想获取特定transaction_date的user_id,transaction_number,transaction_date。第一个表可能包含不同日期的重复user_id,并且可能不包含特定日期的所有user_id。如果user_id在事务表中没有任何事务条目,则结果必须包含所有user_id,并且其transaction_number和事务日期为null。

For an example :

举个例子:

user_id             transaction_number            transaction_date
    1                    10                           12/04/2013
    2                    0                            12/04/2013
    3                    0                            12/04/2013

It shows there are only 3 users and only user_id 1 have transaction in 12/04/2013.

它显示在12/04/2013中只有3个用户且只有user_id 1有交易。

3 个解决方案

#1

select * from Transaction right outer join Users on Transaction.user_id = Users.user_id and Transaction.transaction_date = 'Apr 12 2013 12:00AM'

select * from Transaction右外连接用户在Transaction.user_id = Users.user_id和Transaction.transaction_date ='2013年4月12日12:00 AM'

#2

List all users with transactions if any.Use left join.

列出具有交易的所有用户(如果有)。使用左连接。

select users.user_id,users.user_name,transaction.transaction_number, transaction.date from users left join transaction on users.user_id = transaction.user_id

#3

You can replace transaction_number with 0 if it is null depending on the type of database you are using.

如果transaction_number为null,则可以将其替换为0,具体取决于您使用的数据库类型。

Select u.User_id, transactions.transaction_number, '12/04/2013' as transaction_date
from Users u
left join ( Select user_id, Sum(transaction_number)
             from Transaction
             where transaction_date = '12/04/2013'
             Group By user_id
            ) transactions
            on transactions.User_id = u.user_id

#1