我正在使用具有以下表格的 Firebird 資料庫：
關節
| 產品編號 | 長SKU |
|---|---|
| 1 | A22121000125 |
| 2 | A22121000138 |
| 3 | A22123001508 |
| 4 | A22124002001 |
高大的運動鞋
| 產品編號 | 位置 | 尺寸 |
|---|---|---|
| 1 | 1 | 小的 |
| 1 | 2 | 中等的 |
| 1 | 3 | 大的 |
| 1 | 4 | 超大 |
| 1 | 5 | 超大號 |
| 2 | 1 | 小的 |
| 2 | 2 | 中等的 |
| 2 | 3 | 大的 |
| 2 | 4 | 超大 |
| 2 | 5 | 超大號 |
| 3 | 1 | 02 |
| 3 | 2 | 04 |
| 3 | 3 | 06 |
| 3 | 4 | 08 |
和蘭格斯泰爾
| 產品編號 | 從位置 | 到位置 | 價錢 |
|---|---|---|---|
| 1 | 1 | 3 | 500 |
| 1 | 4 | 5 | 600 |
| 2 | 1 | 3 | 500 |
| 2 | 4 | 5 | 600 |
| 3 | 1 | 4 | 200 |
我希望能夠按 longSKU 的子字串 (shortSKU) 進行分組，并能夠為每個 shortSKU 獲取相應的范圍和價格。
像這個例子：
| 短SKU | 大小從 | 大小為 | 價錢 |
|---|---|---|---|
| A221210001 | 小的 | 大的 | 500 |
| A221210001 | 超大 | 超大 | 600 |
| A221230015 | 02 | 08 | 200 |
我正在使用以下 cobe，但出現錯誤：
動態 SQL 錯誤。
SQL 錯誤代碼 = -104。選擇串列中的無效運算式（未包含在聚合函式或 >GROUP BY 子句中）。
CREATE OR ALTER VIEW RANGOSPARACOSTOSYPRECIOS(
SHORTSKU,
SIZEFROM,
SIZETO,
PRICE ) AS select substring(ar.codigoparticular from 1 for 10) AS SHORTSKU,
( Select TAL.SIZE
From tallesporarticulos TAL
Where TAL.productid=Ar.productid
and TAL.position= RT.FromPosition) as SIZEFROM,
( Select TAL.SIZE
From tallesporarticulos TAL
Where TAL.productid=Ar.productid
and TAL.position= RT.ToPosition) as SIZETO,
max(RT.PRICE)
from Articulos Ar
Inner Join tallesporarticulos TA On Ar.productId = TA.productId
Inner Join rangostalle RT On AR.productId = RT.productId
GROUP BY SHORTSKU, SIZEFROM, SIZETO ;
The following code works, but I need to replace the "fromposition" and "ToPosition" values with the size value like the code above, and that's when I get the error message.
CREATE OR ALTER VIEW RANGOSPARACOSTOSYPRECIOS(
SHORTSKU,
SIZEFROM,
SIZETO,
PRICE ) AS select substring(ar.codigoparticular from 1 for 10) AS SHORTSKU,
RT.FromPosition as SIZEFROM,
RT.ToPosition as SIZETO,
max(RT.PRICE)
from Articulos Ar
Inner Join tallesporarticulos TA On Ar.productId = TA.productId
Inner Join rangostalle RT On AR.productId = RT.productId
GROUP BY SHORTSKU, SIZEFROM, SIZETO ;
For anyone interested in helping, here you have the insert data from the tables above.
CREATE TABLE articulos (
ProductId INTEGER PRIMARY KEY,
LongSKU varchar(12) NOT NULL
);
INSERT INTO articulos VALUES (1, 'A22121000125');
INSERT INTO articulos VALUES (2, 'A22121000138');
INSERT INTO articulos VALUES (3, 'A22123001508');
INSERT INTO articulos VALUES (4, 'A22124002001');
CREATE TABLE TALLESPORARTICULOS (
ProductId INTEGER NOT NULL,
Position INTEGER NOT NULL,
Sizes varchar(12) NOT NULL
);
INSERT INTO TALLESPORARTICULOS (ProductId, position, Sizes) VALUES
(1, 1, 'SMALL'),
(1, 2, 'MEDIUM'),
(1, 3, 'LARGE'),
(1, 4, 'XTRALARGE'),
(1, 1, 'XXTRALARGE'),
(2, 2, 'SMALL'),
(2, 3, 'MEDIUM'),
(2, 4, 'LARGE'),
(2, 5, 'XTRALARGE'),
(2, 5, 'XXTRALARGE'),
(3, 1, '02'),
(3, 2, '03'),
(3, 3, '04'),
(3, 4, '05');
CREATE TABLE RANGOSTALLE (
ProductId INTEGER NOT NULL,
FromPosition INTEGER NOT NULL,
ToPosition INTEGER NOT NULL,
Price double not null
);
INSERT INTO RANGOSTALLE (ProductId,FromPosition,ToPosition,Price) VALUES
(1, 1,3,500),
(1, 4,5,600),
(2, 1,3,500),
(2, 4,5,600),
(3, 1,4,200);
uj5u.com熱心網友回復：
您的腳本包含不少錯誤。修復它們后，查詢相當簡單：
select substring(LongSKU from 1 for 10), low.sizes, high.sizes, avg(price)
from articulos join RANGOSTALLE on articulos.ProductId = RANGOSTALLE.ProductId
join TALLESPORARTICULOS low on RANGOSTALLE.ProductId = low.ProductId and RANGOSTALLE.FromPosition = low.Prodposition
join TALLESPORARTICULOS high on RANGOSTALLE.ProductId = high.ProductId and RANGOSTALLE.ToPosition = high.Prodposition
group by 1,2,3
https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=ae54a7d897da4604396775e3ddc4b764
可以通過將分組移動到派生表中來優化此查詢，但這種優化高度依賴于真實的表結構和查詢要求。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/367514.html
下一篇：創建SQL查詢時遇到問題-初學者