mysql - 使用联接比较数据的最佳方法是什么？

Question

我正在我的网站上创建一个名为“我的理想冲浪板”的部分，用户输入他们的数据(体重、高度等)，在数据库中进行比较(join)并根据冲浪板用户的个人资料返回其理想类型。

我有一张表格，根据高度、体重和用户体验提供了所有尺寸和类型的冲浪板引用。

我正在执行以下操作:

分为两个表:- 表USER显然存储了用户数据(经验、高度和体重)；- 表SURFBOARD根据用户的经验、体重、高度拥有每个冲浪板的引用值(类型、尺寸、重量和升)。

-> 我将表 USER 比作表 SURFBOARD 并返回给用户理想的模型。如何做到这一点？

起初，我想将表USER中的相同字段与表SURFBOARD中的字段进行内连接并获取数据你想要的。

但是，两个表都会重复值。

`dados_usuario`      `prancha` 
    height2           weight2
    height2           weight2
    height2           weight2
    height1           weight2
    height1           weight2
    height1           weight2
       I compare and I display...

我认为这不是一个好的做法，也不是最好的方法。我知道还有其他方法可以做到这一点。

问题是，如何最好地比较这些数据？
如何识别哪一行与用户将输入的数据兼容？

我的数据库:

CREATE TABLE USER(
     usuario INT NOT NULL AUTO_INCREMENT,
     nome VARCHAR(150) not null,
     email VARCHAR(50) not null,
     estilo VARCHAR(14) not null,        
     exp VARCHAR(13) not null, 
     altura VARCHAR(12) not null,
     peso VARCHAR(9) not null,
     PRIMARY KEY(usuario)
);

CREATE TABLE SUFBOARD(
    prancha_pri INT NOT NULL AUTO_INCREMENT,        
    tipo_prancha VARCHAR(9) NOT NULL,
    tamanho_prancha VARCHAR(9) not null,
    meio_prancha VARCHAR(12) not null,
    litragem_prancha VARCHAR(8) not null,
    PRIMARY KEY (prancha_pri)
);

在表“用户”中插入数据:

INSERT INTO EXPERIENCIA VALUES (NULL, 'joao', 'a@a.com', 'Surf', 'INICIANTE', '<1,60m', '>90kg');
INSERT INTO EXPERIENCIA VALUES (NULL, 'john', 'b@b.com', 'StandUP Paddle', 'INTERMEDIARIO', '1,81 - 1,90m', '81 - 90kg');
INSERT INTO EXPERIENCIA VALUES (NULL, 'carl', 'c@c.com', 'Surf', 'AVANÇADO', '>1,90m', '71 - 80kg');

在表中插入数据SURFBOARD:

INSERT INTO PRANCHA VALUES (1, 'FUN', '8', '21 polegadas', '43L');
INSERT INTO PRANCHA VALUES (2, 'FUN', '8.8', '21 polegadas', '43L');
INSERT INTO PRANCHA VALUES (3, 'LONGBOARD', '9.2', '21 polegadas', '55L');
INSERT INTO PRANCHA VALUES (4,  'PRANCHA', '5.5 a 5.8', '20 polegadas', '30L');
INSERT INTO PRANCHA VALUES (5,  'PRANCHA', '5.5 a 5.10', '20 polegadas', '30L');
INSERT INTO PRANCHA VALUES (6,  'PRANCHA', '5.9 a 6.0', '21 polegadas', '32L');
INSERT INTO PRANCHA VALUES (7,  'PRANCHA', '6.0 a 6.4', '21 polegadas', '34L');
INSERT INTO PRANCHA VALUES (8,  'PRANCHA', '5.10 a 6.4', '20 polegadas', '30L');
INSERT INTO PRANCHA VALUES (9,  'PRANCHA', '5.10 a 6.4', '20 polegadas', '32L');
INSERT INTO PRANCHA VALUES (10,  'PRANCHA', '6.2 a 6.6', '21 polegadas', '32L');
INSERT INTO PRANCHA VALUES (11,  'PRANCHA', '6.4 a 6.8', '21 polegadas', '34L');
INSERT INTO PRANCHA VALUES (12,  'PRANCHA', '6.2 a 6.6', '20 polegadas', '30L');
INSERT INTO PRANCHA VALUES (13,  'PRANCHA', '6.2 a 6.6', '21 polegadas', '30L');
INSERT INTO PRANCHA VALUES (14,  'PRANCHA', '6.2 a 6.6', '21 polegadas', '34L');
INSERT INTO PRANCHA VALUES (15,  'PRANCHA', '6.2 a 6.6', '21 polegadas', '36L');
INSERT INTO PRANCHA VALUES (16,  'PRANCHA', '6.2 a 6.6', '21 polegadas', '38L');
INSERT INTO PRANCHA VALUES (17,  'PRANCHA', '6.2 a 7.0', '21 polegadas', '34L');
INSERT INTO PRANCHA VALUES (18,  'PRANCHA', '6.2 a 7.0', '21 polegadas', '38L');
INSERT INTO PRANCHA VALUES (19,  'PRANCHA', '5.5 a 5.8', '18 polegadas', '23L');
INSERT INTO PRANCHA VALUES (20,  'PRANCHA', '5.8 a 5.10', '18 polegadas', '24L');
INSERT INTO PRANCHA VALUES (21,  'PRANCHA', '5.10', '18 polegadas', '27L');
INSERT INTO PRANCHA VALUES (22,  'PRANCHA', '6.0 a 6.2', '19 polegadas', '28L');
INSERT INTO PRANCHA VALUES (23,  'PRANCHA', '6.0 a 6.2', '19 polegadas', '29 a 31L');
INSERT INTO PRANCHA VALUES (24,  'PRANCHA', '5.10 a 6.0', '19 polegadas', '24L');
INSERT INTO PRANCHA VALUES (25,  'PRANCHA', '5.10', '19 polegadas', '26L');
INSERT INTO PRANCHA VALUES (26,  'PRANCHA', '6.0', '19 polegadas', '27L');
INSERT INTO PRANCHA VALUES (27,  'PRANCHA', '6.0', '19 polegadas', '29L');
INSERT INTO PRANCHA VALUES (28,  'PRANCHA', '6.2', '20 polegadas', '30 a 31L');
INSERT INTO PRANCHA VALUES (29,  'PRANCHA', '6.0', '19 polegadas', '25L');
INSERT INTO PRANCHA VALUES (30,  'PRANCHA', '6.0', '19 polegadas', '28L');
INSERT INTO PRANCHA VALUES (31,  'PRANCHA', '6.0', '19 polegadas', '30L');
INSERT INTO PRANCHA VALUES (32,  'PRANCHA', '6.0 a 6.2', '20 polegadas', '30 a 31L');
INSERT INTO PRANCHA VALUES (33,  'PRANCHA', '5.11', '19 polegadas', '26L');
INSERT INTO PRANCHA VALUES (34,  'PRANCHA', '5.11', '19 polegadas', '28L');
INSERT INTO PRANCHA VALUES (35,  'PRANCHA', '6.0', '20 polegadas', '29L');
INSERT INTO PRANCHA VALUES (36,  'PRANCHA', '6.1', '20 polegadas', '30L');
INSERT INTO PRANCHA VALUES (37,  'PRANCHA', '6.1 a 6.6', '20 polegadas', '30 a 31L');
INSERT INTO PRANCHA VALUES (38,  'PRANCHA', '6.1', '19 polegadas', '27L');
INSERT INTO PRANCHA VALUES (39,  'PRANCHA', '6.1', '19 polegadas', '28L');
INSERT INTO PRANCHA VALUES (40,  'PRANCHA', '6.1 a 6.3', '20 polegadas', '29L');
INSERT INTO PRANCHA VALUES (41,  'PRANCHA', '6.1 a 6.4', '20 polegadas', '31L');
INSERT INTO PRANCHA VALUES (42,  'PRANCHA', '6.2 a 6.6', '20 polegadas', '31L');

在我的表单中，从视觉上看它们都是高度和体重的精确值。但是，字段的值是我引用表的值:

高度:

<option value="1,71 - 1,80m">1.71m</option>
<option value="1,71 - 1,80m">1.72m</option>
<option value="1,71 - 1,80m">1.73m</option>
<option value="1,71 - 1,80m">1.74m</option>
<option value="1,71 - 1,80m">1.75m</option>
<option value="1,71 - 1,80m">1.76m</option>

重量:

<option value="81 - 90kg">88Kg</option>
<option value="81 - 90kg">89Kg</option>
<option value="81 - 90kg">90Kg</option>
<option value=">90kg">91Kg</option>
<option value=">90kg">92Kg</option>
<option value=">90kg">93Kg</option>
<option value=">90kg">94Kg</option>

Answer 1

首先您必须告诉我们，给定一个用户和一些板 block ，什么才是“理想”的板 block 。然后将其转换为 SQL。

PS如果您希望查询使用比较或算术来讨论这些值，则必须从值中删除单位。 (然后您可以使用数字类型存储数字。)如果您想轻松提及它们的端点，则必须将范围存储为列对。否则你将不得不写这样的东西

U.HEIGHT >= get_min_from_range_as_number(B.HEIGHT)
U.WEIGHT <= get_weight_as_number_without_units(B.WEIGHT)

功能复杂的地方。

<小时/>

假设您想要的行位于:

user USERID has ideal board(s) BOARDID

您必须用更简单的方式告诉我们这意味着什么。例如，也许这意味着:

(read U.ID as USERID, B.ID as BOARDID)
there exist values for U.NAME, U.HEIGHT, ..., B.WEIGHT where
        user U.ID with name U.NAME ... has height U.HEIGHT...
    AND board B.ID suits height between B.MINHEIGHT and B.MAXHEIGHT ...
    AND U.HEIGHT >= B.MINHEIGHT AND U.HEIGHT <= B.MAXHEIGHT
    AND (B.MINHEIGHT + B.MAXHEIGHT)/2 <= U.WEIGHT * 100
    AND ...
    OR ...

<小时/>

现在我们需要一个查询来返回使该语句模板成为真实语句的行。

已经 User保存行，其中:

user ID with name NAME ... has height HEIGHT ...

和Board保存行，其中:

board ID suits height between MINHEIGHT and MAXHEIGHT ...

但是 SQL 的本质 JOIN是 table1 t1 JOIN table2 t2 保存满足第一个表的语句模板 AND 的行编辑到第二个，参数/列以别名和点为前缀。所以User U JOIN Board B保存行，其中:

    user U.ID with name U.NAME ... has height U.HEIGHT ...
AND board B.ID suits height between B.MINHEIGHT and B.MAXHEIGHT ...

以及 WHERE 的性质是 table WHERE condition 保存满足表的语句模板的行 AND编辑为 condition 。所以

User U JOIN Board B
WHERE U.HEIGHT >= B.MINHEIGHT AND U.HEIGHT <= B.MAXHEIGHT
...

保存行，其中:

    user U.ID with name U.NAME ... has height U.HEIGHT ...
AND board B.ID suits height between B.MINHEIGHT and B.MAXHEIGHT ...
AND U.HEIGHT >= B.MINHEIGHT AND U.HEIGHT <= B.MAXHEIGHT
...

然后SELECT删除任何不需要的参数/列。所以user U.ID has ideal oard(s) B.ID保存行，其中:

SELECT U.ID, B.ID
FROM User U JOIN Board B
WHERE U.HEIGHT >= B.MINHEIGHT AND U.HEIGHT <= B.MAXHEIGHT
...

SELECT还重命名列。因此，为了获得 user USERID has ideal board(s) BOARDID 行的整体查询我们需要:

SELECT U.ID AS USERID, B.ID AS BOARDID
FROM User U JOIN Board B
WHERE U.HEIGHT >= B.MINHEIGHT AND U.HEIGHT <= B.MAXHEIGHT
...