我有一张桌子,桌子上有带字母和数字的代码,尤其是aa00到​​ZZ99。我在找出在此列中搜索dd01-GG99等范围的最佳方法时遇到了麻烦。最好的方法是什么? (我在RStudio中使用sqldf)

我尝试使用诸如之间的语句,但是结果不是我想要的。实际上,它显示的是相反的字母,没有小写字母:

SELECT prodcode
  FROM data
 WHERE prodcat BETWEEN 'GG99' AND 'dd01';


编辑时间太长,无法发表评论:

library(ggvis)
library(readr)
library(dplyr)
library(knitr)
library(sqldf)
library(tidyr)
data <- read_csv("C:/Users/name/Documents/test1.csv")
compn <-read_csv("C:/Users/name/Documents/test2.csv")


prodcode <- expand.grid(x1 = LETTERS,
                        x2 = letters,
                        x3 = 0:9,
                        x4 = 0:9)
prodcode$prodcat <- apply(data, 1, paste0, collapse = "")




test <- sqldf("SELECT prod
               FROM data, compn
               WHERE data.cono = compn.cono
               AND (SELECT * FROM prodcode
                        WHERE (SUBSTR(UPPER(prodcat), 1, 2) >= 'DD' AND
                        CAST(SUBSTR(prodcat, 3, 2) AS INT) >= 00 ) AND
                        (SUBSTR(UPPER(prodcat), 1, 2) <= 'GG' AND
                        CAST(SUBSTR(prodcat, 3, 2) AS INT) <= 99);
              GROUP BY prod
              ORDER BY prod ASC;")


test

最佳答案

这是将获得您建议的SQL代码。在SQL中,您需要将字母和数字分开以进行比较。由于数字的宽度是固定的,因此您可以不必转换为INT。如果具有非固定宽度的数值,则必须确定适当的排序行为。

prodcode <- expand.grid(x1 = LETTERS,
                        x2 = letters,
                        x3 = 0:9,
                        x4 = 0:9)
prodcode$prodcat <- apply(prodcode, 1, paste0, collapse = "")

library(sqldf)

sqldf(
  "SELECT * FROM prodcode
   WHERE (SUBSTR(UPPER(prodcat), 1, 2) >= 'DD' AND
            CAST(SUBSTR(prodcat, 3, 2) AS INT) >= 00 ) AND
         (SUBSTR(UPPER(prodcat), 1, 2) <= 'GG' AND
            CAST(SUBSTR(prodcat, 3, 2) AS INT) <= 99)"
)


在子查询中使用

proddata <- data.frame(prodcode = c("DD15", "BB08", "FQ17", "NN11"),
                       value = rnorm(4, 100, 15))

prodcode <- expand.grid(x1 = LETTERS,
                        x2 = letters,
                        x3 = 0:9,
                        x4 = 0:9)
prodcode$prodcat <- apply(prodcode, 1, paste0, collapse = "")

library(sqldf)

sqldf(
  "SELECT *
   FROM proddata
   WHERE prodcode IN (SELECT UPPER(prodcat) FROM prodcode
                      WHERE (SUBSTR(UPPER(prodcat), 1, 2) >= 'DD' AND
                      CAST(SUBSTR(prodcat, 3, 2) AS INT) >= 00 ) AND
                      (SUBSTR(UPPER(prodcat), 1, 2) <= 'GG' AND
                      CAST(SUBSTR(prodcat, 3, 2) AS INT) <= 99))"
)

关于mysql - SQL-在带有数字和字母的列中搜索特定范围的最佳方法是什么?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/44505339/

10-12 17:36
查看更多