SQL从项目到所有者表中选择所有者的所有项目

Question

我有一个表bike_to_owner。我想选择特定用户拥有的当前项目。

表结构

CREATE TABLE IF NOT EXISTS `bike_to_owner` (
    `bike_id` int(10) unsigned NOT NULL, 
    `user_id` int(10) unsigned NOT NULL, 
    `last_change_date` date NOT NULL, 
    PRIMARY KEY (`bike_id`,`user_id`,`last_change_date`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

在用户的个人资料页，我想显示所有他/她当前的财产。

我写了这样一句话：

SELECT `bike_id`,`user_id`,max(last_change_date) FROM `bike_to_owner` 
WHERE `user_id` = 3 group by `last_change_date` 

，但我不能肯定它正常工作在所有情况下。 你可以请确认这是正确的，如果不是建议我更好的东西。 使用php/mysql。

在此先感谢！

Answer 1

在您的意见光，我将只给出新的答案在这里。

既然你可以有用户采取的自行车的所有权，然后放弃它，然后再次得到它，你希望能够追踪历史，我建议你的方案略有变化：

CREATE TABLE IF NOT EXISTS `bike_to_owner` (
    `bike_id` int(10) unsigned NOT NULL, 
    `user_id` int(10) unsigned NOT NULL, 
    `acquired_date` date NOT NULL, 
    `sold_date` date NULL, 
    PRIMARY KEY (`bike_id`,`user_id`,`acquired_date`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

每当自行车转手，你会更新sold_date前拥有当前的日期和为NULL sold_date和当前日期的acquired_date新东家创造一个新的记录。

然后你的查询，以确定目前的所有权应该是这样的：

SELECT `bike_id`,`user_id` FROM `bike_to_owner` 
WHERE `user_id` = 3 AND sold_date IS NULL 

Answer 2

快到了，但是你应该bike_id和USER_ID被分组，而不是上次更改日期：

SELECT `bike_id`,`user_id`,max(last_change_date) FROM `bike_to_owner` 
WHERE `user_id` = 3 group by `bike_id`, `user_id` 

ETA：

顺便说一句，我很好奇，为什么你有最后的更改日期在该表中作为关键的一部分。一辆自行车可以在不同的更改日期多次与用户关联吗？如果是这样，为什么？我认为自行车既可以与用户相关联，也可以不与用户相关联 - 除了添加或删除关系之外，没有任何东西可以“改变”。除非在桌子上还有其他的字段没有显示？

Answer 3

这里是工作的声明：

SELECT t1.* 
FROM bike_to_owner t1 
LEFT JOIN bike_to_owner AS t2 ON t1.bike_id = t2.bike_id 
AND t1.last_change_date < t2.last_change_date 
WHERE t2.last_change_date IS NULL 
AND t1.user_id = 3 

Answer 4

我想了很多取决于你试图存储，并能够从数据库中检索的信息。试图第二猜测这一点，我想提出以下组表（请注意，只有在这些表中的一列允许空值）：

-- User ----- 
user_id (primary key) 
name 

-- Bike ------ 
bike_id (primary key) 
description 
current_owner nullable (foreign key to User) 

这假定“自行车所有权”是一个临界指数以及经常需要的信息，并且值得向该结构添加非规范化数据以使得找到自行车的当前用户或用户当前的自行车无关紧要。 （添加非规范化数据的一个关键原因是简化数据检索和/或提高性能。在您制作的每个查询中跳过制定销售历史记录都可以。）如果所有权持续时间很重要，则可以添加“purchased_on”列，或者深入查看事务表。

要跟踪当自行车被购买：

-- Bike_Purchase ------ 
user_id (foreign key to User) 
bike_id (foreign key to Bike) 
purchased_on 
(primary key on all three columns) 

将跟踪每一个用户购买了自行车的时间。相反，如果当自行车卖给你宁愿跟踪，你可以有

-- Bike_Sale ------ 
user_id (foreign key to User) 
bike_id (foreign key to Bike) 
sold_on 
(primary key on all three columns) 

同时跟踪购买和销售，包括无论是两个表或一个简单的企业集团：

-- Bike_Transaction ------ 
user_id (foreign key to User) 
bike_id (foreign key to Bike) 
transaction_type ** 
transaction_date 
(primary key on all four columns) 

这可以让你准确地跟踪您的用户的购买和销售，并忽视非用户的交易。卖了一辆自行车？输入一个条目。不知道或关心他们卖给谁？不要输入。

无论您使用哪个时间跟踪表，确定持续时间与加入（user_id，bike_id）和获取max（日期）一样简单。把给定的自行车的“所有权链”，或者自己拥有的自行车和什么时间/多长时间组合在一起将是棘手的，但是完全可行。

**对于transaction_type，您可能需要设置一个Transaction_Type表来跟踪不同的交易（出售，购买，交易，发现，丢失/被盗等等）。或者，您可以使其成为一个varchar一个描述性的字符串，或者放一个CHECK约束来限制它到选定的值。