帮我重写这个查询

Question

我有这样的查询：

SELECT diamond_id, carat, clarity, color, cut,
    CASE clarity
        WHEN "FL" THEN 0
        WHEN "IF" THEN 1
        WHEN "VVS1" THEN 2
        WHEN "VVS2" THEN 3
        WHEN "VS1" THEN 4
        WHEN "VS2" THEN 5
        WHEN "SI1" THEN 6
        WHEN "SI2" THEN 7
        WHEN "I1" THEN 8
        WHEN "I2" THEN 9
        WHEN "I3" THEN 10
        ELSE -1
    END AS clarity_score,
    CASE cut
        WHEN "ideal" THEN 0
        WHEN "excellent" THEN 1
        WHEN "very good" THEN 2
        WHEN "good" THEN 3
        WHEN "fair" THEN 4
        WHEN "poor" THEN 5
        ELSE -1
    END AS cut_score    
    FROM rapnet_diamonds
 WHERE 
    shape IN ("round","princess","oval") 
 AND (carat BETWEEN ".1" AND "5") 
 AND (color BETWEEN "D" AND "Z") 
 AND (clarity_score BETWEEN "0" AND "10") 
 AND (cut_score BETWEEN "0" AND "5")

如您所见，我动态生成 clarity_score 和 cut_score 列，以便我可以在 WHERE 子句中使用它们。

首先，这给了我一个错误，告诉我 clarity_score 不存在。所以我想也许这是我需要使用 HAVING 的情况之一？

所以我将 where 子句更改为：

WHERE 
    shape IN ("round","princess","oval") 
 AND (carat BETWEEN ".1" AND "5") 
 AND (color BETWEEN "D" AND "Z") 
HAVING 
    (clarity_score BETWEEN "0" AND "10") 
 AND (cut_score BETWEEN "0" AND "5")

我没有错误，但结果为 0...这应该返回超过 100K。所以我在某个地方写错了？

然而，在开始重写之前，我不想选择这两个生成的字段，有没有办法可以编写它，这样我就不会检索这两个字段，而只是在 where 子句中使用它们？

Answer 1

您可以执行以下操作。

创建两个查找表 向

create table clarity_lookup 
    (clarity_score int,
    clarity char(5))

create table cut_lookup 
    (cut_score int,
    cut char(10))

查找表加载数据

INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (0, 'FL')
INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (1, 'IF')
INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (2, 'VVS1')
INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (3, 'VVS2')
INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (4, 'VS1')
INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (5, 'VS2')
INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (6, 'SI1')
INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (7, 'SI2')
INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (8, 'I1')
INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (9, 'I2')
INSERT INTO clarity_lookup (clarity_score, clarity) VALUES (10, 'I3')

INSERT INTO cut_lookup (cut_score, cut) VALUES (0, 'ideal')
INSERT INTO cut_lookup (cut_score, cut) VALUES (1, 'excellent')
INSERT INTO cut_lookup (cut_score, cut) VALUES (2, 'very good')
INSERT INTO cut_lookup (cut_score, cut) VALUES (3, 'good')
INSERT INTO cut_lookup (cut_score, cut) VALUES (4, 'fair')
INSERT INTO cut_lookup (cut_score, cut) VALUES (5, 'poor')

您的查询将如下所示。总之，您将根据分数将表示原始 CASE 子句中的数据的新表连接到 Diamonds 表，但在 SELECT 子句中显示说明。

SELECT rapnet_diamonds.diamond_id, rapnet_diamonds.carat,
    clarity_lookup.clarity, rapnet_diamonds.color, cut_lookup.cut
FROM rapnet_diamonds
LEFT OUTER JOIN clarity_lookup ON rapnet_diamonds.clarity_score = clarity_lookup.clarity_score
LEFT OUTER JOIN cut_lookup ON rapnet_diamonds.cut_score = cut_lookup.cut_score
WHERE rapnet_diamonds.shape IN ('round','princess','oval') 
    AND (rapnet_diamonds.carat BETWEEN .1 AND 5) 
    AND (rapnet_diamonds.color BETWEEN 'D' AND 'Z') 
    AND (rapnet_diamonds.clarity_score BETWEEN 0 AND 10) 
    AND (rapnet_diamonds.cut_score BETWEEN 0 AND 5)

Answer 2

这行不通吗？ （我对 MySQL 的经验有限。）

SELECT diamond_id, carat, clarity, color, cut
  FROM rapnet_diamonds
 WHERE 
    shape IN ("round","princess","oval") 
 AND (carat BETWEEN ".1" AND "5") 
 AND (color BETWEEN "D" AND "Z") 
 AND (  CASE clarity
        WHEN "FL" THEN 0
        WHEN "IF" THEN 1
        WHEN "VVS1" THEN 2
        WHEN "VVS2" THEN 3
        WHEN "VS1" THEN 4
        WHEN "VS2" THEN 5
        WHEN "SI1" THEN 6
        WHEN "SI2" THEN 7
        WHEN "I1" THEN 8
        WHEN "I2" THEN 9
        WHEN "I3" THEN 10
        ELSE -1
    END BETWEEN "0" AND "10") 
 AND (  CASE cut
        WHEN "ideal" THEN 0
        WHEN "excellent" THEN 1
        WHEN "very good" THEN 2
        WHEN "good" THEN 3
        WHEN "fair" THEN 4
        WHEN "poor" THEN 5
        ELSE -1
    END BETWEEN "0" AND "5")

Answer 3

我会将 case 语句逻辑放入查找解码器表中，然后加入其中。
然后你可以使用where子句中的列，它简化了SQL。

类似的东西

create table ClarityCode
(
   Clarity char(x) not null
,  CodeID int not null
)

Answer 4

为什么不使用IN？

SELECT diamond_id, carat, clarity, color, cut
FROM rapnet_diamonds
WHERE
shape IN ("round","princess","oval") AND
(carat BETWEEN ".1" AND "5") AND
(color BETWEEN "D" AND "Z") AND
clarity IN ('FL', 'IF', 'VVS1', 'VVS2', 'VS1', 'VS2', 'SI1', 'SI2', 'I1', 'I2', 'I3') AND
cut IN ('ideal', 'excellent', 'very good', 'good', 'fair', 'poor')

Answer 5

我会接受您的查询并稍微修改您的代码以使其工作

SELECT * FROM 
  (
    SELECT diamond_id, carat, clarity, color, cut, 
        CASE clarity 
            WHEN "FL" THEN 0 
            WHEN "IF" THEN 1 
            WHEN "VVS1" THEN 2 
            WHEN "VVS2" THEN 3 
            WHEN "VS1" THEN 4 
            WHEN "VS2" THEN 5 
            WHEN "SI1" THEN 6 
            WHEN "SI2" THEN 7 
            WHEN "I1" THEN 8 
            WHEN "I2" THEN 9 
            WHEN "I3" THEN 10 
            ELSE -1 
        END AS clarity_score, 
        CASE cut 
            WHEN "ideal" THEN 0 
            WHEN "excellent" THEN 1 
            WHEN "very good" THEN 2 
            WHEN "good" THEN 3 
            WHEN "fair" THEN 4 
            WHEN "poor" THEN 5 
            ELSE -1 
        END AS cut_score     
        FROM rapnet_diamonds 
     WHERE  
        shape IN ("round","princess","oval")  
     AND (carat BETWEEN ".1" AND "5")  
     AND (color BETWEEN "D" AND "Z")
)t 
WHERE   
     (clarity_score BETWEEN "0" AND "10")  
     AND (cut_score BETWEEN "0" AND "5") 

您不能在 where 子句中使用 Case 变量

Answer 6

为了满足对拆分表想法进行更多解释的愿望：

您无需将所有内容都放在 CASE 语句中，而是将清晰度信息存储在第二个表中。它可能看起来像这样：

Clarity_Table
-----------------------
Clarity_score | clarity
-----------------------
0             | FL
1             | IF
2             | VVS1
3             | VVS2
4             | VS1
5             | VS2
...
10            | I3

Cut_Table 也是如此，希望您从上面得到了这个想法。

然后，将查询更改为：

SELECT diamond_id, carat, clarity, color, cut, clarity_score, cut_score    
    FROM rapnet_diamonds JOIN clarity_table USING(clarity)
                         JOIN cut_table USING(cut)
 WHERE 
    shape IN ("round","princess","oval") 
 AND (carat BETWEEN ".1" AND "5") 
 AND (color BETWEEN "D" AND "Z") 
 AND (clarity_score BETWEEN "0" AND "10") 
 AND (cut_score BETWEEN "0" AND "5")

然后，它不会对分数进行硬编码，而是在表之间进行联接。我怀疑它会经常更新，但它会让以后的更改变得更容易，并且使整体代码维护和维护变得容易。可读性更容易。