where in(子查询)不起作用的查询

nlejzf6q 于 2021-06-15 发布在 Mysql

关注(0)|答案(1)|浏览(325)

使用子查询时我的查询不起作用
我有商品表

"auto_id"   "category"  "name"  "last_update"
"1"     "1"     "BOOK"  "2018-12-06 17:35:19"
"2"     "1"     "BOOK"  "2018-09-26 10:45:08"
"7"     "1"     "PENCIL"    "2018-10-09 08:50:40"
"8"     "1"     "BOOK"  "2018-12-04 14:39:32"
"9"     "4"     "RULER" "2018-10-11 06:45:50"
"10"        "2"     "PAPER" "2018-11-13 08:35:34"
"11"        "1"     "Pen"   "2018-11-28 11:54:18"
"12"        "2"     "ERASER"    "2018-11-28   12:06:36"
"13"        "2"     "PAPER F4"  "2018-12-10 16:43:08"
"14"        "2"     "PAPER F4"  "2018-12-05 05:53:30"
"15"        "1"     "BOOK"  "2018-12-10 16:42:37"

SELECT GROUP_CONCAT(DISTINCT(CONCAT('"',z.name,'"')))  AS n
FROM shop_product z 
WHERE z.category = 2 AND DATE(z.last_update) BETWEEN '2018-07-01' AND '2018-12-31'

和结果

"PAPER F4","PAPER","ERASER"

以及使用运行完整查询时没有结果

SELECT a.id FROM shop_product AS a WHERE a.name IN (
SELECT GROUP_CONCAT(DISTINCT(CONCAT('"',z.name,'"')))  AS n
FROM shop_product z 
WHERE z.category = 2 AND DATE(z.last_update) BETWEEN '2018-07-01'AND '2018-12-31')

如果我在之前使用结果字符串更改子查询，它将显示结果

SELECT a.id FROM shop_product AS a WHERE a.name IN ("PAPER F4","PAPER","ERASER")

如何使用子查询获取数据

mysql

来源：https://stackoverflow.com/questions/53872608/query-with-where-in-subquery-not-working

1条答案

按热度按时间

2o7dmzc51#

您不需要concat（）和group\u concat（）函数。只需使用一个简单的子选择。

SELECT a.`auto_id`, a.`category`, a.`name`, a.`last_update`
FROM shop_product a 
WHERE name IN(
    SELECT z.`name`
    FROM shop_product z
    WHERE z.category = 2 
    AND DATE(z.last_update) 
    BETWEEN '2018-07-01' AND '2018-12-31'
)

赞(0）回复(0）举报 2021-06-15

我来回答

where in(子查询)不起作用的查询

1条答案

相关问题

热门标签

最新问答