嵌套的MySql Select语句带有“where in”子句(Nested MySql Select statement with “where in” clause)

我会尽力详细说明这一点。我有一个带有where子句的嵌套select语句，但是select的嵌套部分应该被解释为文字字符串（我相信这是正确的术语）。但是mysql的默认行为导致了我不想要的结果。

即

select class from cs_item where code="007" +-------+ | class | +-------+ | 1,3 | +-------+

如果我明确键入“in（1,3）”作为select查询的一部分，则下面是一个查询：

select alpha,description from cs_quality where class in (1,3); +-------+-------------+ | alpha | description | +-------+-------------+ | STD | STD | | XS | XS | | 5 | Sch 5 | | 10 | Sch 10 | | 20 | Sch 20 | | 40 | Sch 40 | | 60 | Sch 60 | | 80 | Sch 80 | | 100 | Sch 100 | | 120 | Sch 120 | | 140 | Sch 140 | | 160 | Sch 160 | | XXS | XXS | | 15L | 150# | | 30L | 300# | | 40L | 400# | | 60L | 600# | | 90L | 900# | | 150L | 1500# | | 200L | 2000# | | 250L | 2500# | | 300L | 3000# | | 400L | 4000# | | 600L | 6000# | | 900L | 9000# | +-------+-------------+

但是当我去筑巢时，我得到了同样的结果......

select alpha,description from cs_quality where class in (select class from cs_item where code = "007") +-------+-------------+ | alpha | description | +-------+-------------+ | STD | STD | | XS | XS | | 5 | Sch 5 | | 10 | Sch 10 | | 20 | Sch 20 | | 40 | Sch 40 | | 60 | Sch 60 | | 80 | Sch 80 | | 100 | Sch 100 | | 120 | Sch 120 | | 140 | Sch 140 | | 160 | Sch 160 | | XXS | XXS | +-------+-------------+

这只是“1级”的一部分......它在“，3”组件上徘徊。有没有办法将嵌套选择解释为文字文本？

谢谢大家，非常感谢。我在编写这个问题时遇到了一些麻烦，但会根据需要进行编辑。

I'll try to detail this the best I can. I have a nested select statement with a where in clause, but the nested part of the select should be interpreted as a literal string (I believe this is the right terminology). However the default behavior of mysql leads to a result I do not want.

I.e.

select class from cs_item where code="007" +-------+ | class | +-------+ | 1,3 | +-------+

And the below is a query if I explicitly type "in (1,3)" as part of a select query:

select alpha,description from cs_quality where class in (1,3); +-------+-------------+ | alpha | description | +-------+-------------+ | STD | STD | | XS | XS | | 5 | Sch 5 | | 10 | Sch 10 | | 20 | Sch 20 | | 40 | Sch 40 | | 60 | Sch 60 | | 80 | Sch 80 | | 100 | Sch 100 | | 120 | Sch 120 | | 140 | Sch 140 | | 160 | Sch 160 | | XXS | XXS | | 15L | 150# | | 30L | 300# | | 40L | 400# | | 60L | 600# | | 90L | 900# | | 150L | 1500# | | 200L | 2000# | | 250L | 2500# | | 300L | 3000# | | 400L | 4000# | | 600L | 6000# | | 900L | 9000# | +-------+-------------+

But when I go to nest this to get the same result I have...

select alpha,description from cs_quality where class in (select class from cs_item where code = "007") +-------+-------------+ | alpha | description | +-------+-------------+ | STD | STD | | XS | XS | | 5 | Sch 5 | | 10 | Sch 10 | | 20 | Sch 20 | | 40 | Sch 40 | | 60 | Sch 60 | | 80 | Sch 80 | | 100 | Sch 100 | | 120 | Sch 120 | | 140 | Sch 140 | | 160 | Sch 160 | | XXS | XXS | +-------+-------------+

Which is just the part of "class in 1"... it balks on the ",3" component. Is there a way for the nested select to be interpreted as literal text?

Thanks all, much appreciated. I had a bit of trouble wording this question but will edit as needed.

最满意答案

规范化，规范化，规范化表格，在本例中为表格cs_item 。您不应在一个字段中存储多个（逗号分隔）值。

在你这样做之前，你可以使用：

select alpha, description from cs_quality where FIND_IN_SET( class , (select class from cs_item where code = '007'))

要么

select q.alpha, q.description from cs_quality AS q join cs_item AS i on FIND_IN_SET( q.class , i.class ) where i.code = '007'

但是这种使用特殊函数而不是JOIN的相等性会导致查询速度非常慢。存储逗号分隔列表会导致大量其他问题。看这里：

在数据库列中存储逗号分隔列表真的那么糟糕吗？

简短的回答是： 是的，这很糟糕 。

Normalize, normalize, normalize your tables, in this case table cs_item. You should NOT store multiple (comma separated) values in one field.

Until you do that, you can use:

select alpha, description from cs_quality where FIND_IN_SET( class , (select class from cs_item where code = '007'))

or

select q.alpha, q.description from cs_quality AS q join cs_item AS i on FIND_IN_SET( q.class , i.class ) where i.code = '007'

But this kind of using special functions instead of equality for JOINs, leads to very slow queries. Storing comma separated lists leads to a ton of other problems. See here:

Is storing a comma separated list in a database column really that bad?

Short answer is: Yeah, it's that bad.

更多推荐