我有一张桌子,桌子上有带字母和数字的代码,尤其是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/