下記のSQLでウィンドウ関数で指定した行名'king'を
BETWEENで指定したところ、下記のようなエラーになりました。
sql
1Error running query: Invalid column name 'king'.DB-Lib error message 20018, severity 16: General SQL Server error: Check messages from the SQL Server DB-Lib error message 20018, severity 16: General SQL Server error: Check messages from the SQL Server DB-Lib error message 20018, severity 16: General SQL Server error: Check messages from the SQL Server DB-Lib error message 20018, severity 16: General SQL Server error: Check messages from the SQL Server 2 Embed 3Download Dataset
原因をご教授いただけないでしょうか?ちなみにmysqlです。
また質問の大幅な変更申し訳ありません
sql
1WITH bcr AS 2(select mb.brand_id, mb.name_eigo, s.model_id, model.name_eigo model_name_eigo, 3 mc.cate_id, mc.cate_name, s.cnt 4from ( 5SELECT ms.brand_id, ms.model_id, ms.cate_id,count(ms.syo_id) cnt 6FROM M_SYOHIN ms (NOLOCK) 7JOIN M_KAIIN mk (NOLOCK) 8ON ms.buyer_id = mk.kaiin_id 9left outer join M_SYOHIN_MODEL msm 10on ms.model_id=msm.model_id 11WHERE ms.kokaidate >= '2008-06-10' AND (msm.deleted IS NULL or msm.deleted='0') 12AND NOT EXISTS (SELECT 1 FROM D_REQRES res (NOLOCK) WHERE res.syo_id = ms.syo_id) 13AND NOT EXISTS (SELECT 1 FROM D_DELEGATE_RES res (NOLOCK) WHERE res.syo_id = ms.syo_id) 14AND mk.status = '1' AND ISNULL(mk.syupin_limit, '0') = '0' AND mk.blacklist = '0' 15AND ms.thm_id <> '136' -- USED商品のテーマは除く 16group by ms.brand_id, ms.model_id, ms.cate_id 17) s 18left outer join M_BRAND mb 19on s.brand_id = mb.brand_id 20left outer join M_CATEGORY mc 21on s.cate_id = mc.cate_id 22left outer join M_SYOHIN_MODEL model 23on s.model_id = model.model_id 24WHERE mb.yuko_flg='0' 25) 26 27SELECT TOP 10 bcr.brand_id, 28 bcr.model_id, 29 bcr.cate_id, 30 bcr.cnt, 31 ms.syo_id, 32 image.url, 33 RANK() OVER(ORDER BY bcr.cnt DESC) AS king 34FROM M_SYOHIN ms (NOLOCK) 35JOIN M_KAIIN mk (NOLOCK) ON ms.buyer_id = mk.kaiin_id 36JOIN M_SYOHIN_IMAGES image (NOLOCK) ON ms.syo_id = image.syo_id 37INNER JOIN bcr ON ms.brand_id=bcr.brand_id 38AND ms.model_id=bcr.model_id 39AND ms.cate_id=bcr.cate_id 40WHERE king > 100 AND king < 102 41 AND ms.kokaidate >= '2008-06-10' 42 AND NOT EXISTS (SELECT 1 FROM D_REQRES res (NOLOCK) WHERE res.syo_id = ms.syo_id) 43 AND NOT EXISTS (SELECT 1 FROM D_DELEGATE_RES res (NOLOCK) WHERE res.syo_id = ms.syo_id) 44 AND mk.status = '1' AND ISNULL(mk.syupin_limit, '0') = '0' AND mk.blacklist = '0' 45 AND ms.thm_id <> '136' -- USED商品のテーマは除く 46 AND 1.00 <= king AND king >= 3.00 47ORDER BY bcr.cnt desc
回答1件
あなたの回答
tips
プレビュー