mysql选择2个不同的子串，单个WHERE来自1列

i am trying to pull data from sql with several conditions from a single column
how can i get this to work?

我试图从单个列中的几个条件从sql中提取数据如何才能使其工作?

SELECT RIGHT(productID, 2) AS 'a', SUBSTRING(productID,1,2) AS 'b', productID 
FROM products 
WHERE `group`='$one' 
  AND `a` LIKE 'AA%' 
  AND `b` LIKE '$two'

i am trying to get the first 2 letters of the row and last 2 letters from the same row as well as checking if group=$one but get this error

我想获取行的前2个字母和同一行的最后2个字母以及检查group = $ one但是得到此错误

    Unknown column 'b' in 'where clause'

4 个解决方案

#1

SELECT RIGHT(productID, 2) AS 'a', SUBSTRING(productID,1,2) AS 'b', productID 
FROM products 
WHERE `group`='$one' 
GROUP BY productID
HAVING
  a = 'AA' 
  and b LIKE '$two'

No need for the like it's two positions % increases execution.

不需要它的两个位置%增加执行。

#2

I created a simple table (sotest for * Test table) and added a VARCHAR column to it called col1. The code below worked for me.

我创建了一个简单的表(sosst for * Test table)并为其添加了一个名为col1的VARCHAR列。下面的代码对我有用。

SELECT RIGHT(col1, 2) AS a, SUBSTRING(col1,1,2) AS b, col1 as col FROM sotest  Having a like 'in%' and b like 'te%'

The return is as bellow

回报如下:

|  a |  b  |     col      |
|'in'| 'te'| 'test_jermin'|

#3

SELECT RIGHT(productID), 2)
                      ^---extra bracket

you're terminating the function before you specify how many characters to extract.

在指定要提取的字符数之前,您将终止该函数。

#4

That is because of your error in RIGHT function call, you have a misplaced parenthesis. Change the statement of SELECT to this:

那是因为你在RIGHT函数调用中的错误,你有一个错位的括号。将SELECT语句更改为:

SELECT RIGHT(productID, 2)

#1

SELECT RIGHT(productID, 2) AS 'a', SUBSTRING(productID,1,2) AS 'b', productID 
FROM products 
WHERE `group`='$one' 
GROUP BY productID
HAVING
  a = 'AA' 
  and b LIKE '$two'