嗨，我這周正在寫一些關于家庭作業的查詢，但我一直堅持這個。我需要在不使用連接的情況下從多個表中收集資訊
- 顯示每個客戶庫存請求中請求的每個產品的串列。顯示客戶名稱、產品編號和請求數量，按客戶名稱排序，然后是產品編號。
我當前的代碼顯示了輸出，但給出了正確的結果。它根據問題列印表的內容 5 次而不是一次。
SELECT CLIENT.clientName, REQUEST_LIST.productNum, REQUEST_LIST.qtyRequested
FROM CLIENT, REQUEST_LIST
WHERE CLIENT.clientNum IN (
SELECT clientNum
FROM STOCK_REQUEST)
AND REQUEST_LIST.requestNum IN (
SELECT requestNum
FROM STOCK_REQUEST)
ORDER BY CLIENT.clientName , REQUEST_LIST.productNum
以下是表格：
(stock request)
INSERT INTO Stock_Request (requestDate, clientNum)
VALUES ('2020-12-10',1);
INSERT INTO Stock_Request (requestDate, clientNum)
VALUES ('2020-05-04',2);
INSERT INTO Stock_Request (requestDate, clientNum)
VALUES ('2021-07-06',3);
INSERT INTO Stock_Request (requestDate, clientNum)
VALUES ('2021-07-08',4);
INSERT INTO Stock_Request (requestDate, clientNum)
VALUES ('2022-02-07',5);
(request list)
INSERT INTO Request_List (requestNum, productNum, qtyRequested)
VALUES (1,1,1);
INSERT INTO Request_List (requestNum, productNum, qtyRequested)
VALUES (2,2,2);
INSERT INTO Request_List (requestNum, productNum, qtyRequested)
VALUES (3,3,3);
INSERT INTO Request_List (requestNum, productNum, qtyRequested)
VALUES (4,4,4);
INSERT INTO Request_List (requestNum, productNum, qtyRequested)
VALUES (5,5,5);
(client)
INSERT INTO Client (clientName)
VALUES ('Mike');
INSERT INTO Client (clientName)
VALUES ('John');
INSERT INTO Client (clientName)
VALUES ('Sally');
INSERT INTO Client (clientName)
VALUES ('Margret');
INSERT INTO Client (clientName)
VALUES ('Max');
任何幫助，將不勝感激。
預期輸出：（不是實際數字）
client number | product number | quantity requested
John Smith. 1. 3
Margret Thatcher. 3. 7
Max Payne. 6. 1
Mike Costello. 2. 1
Sally Barbarella. 4. 4
uj5u.com熱心網友回復：
首先，您不需要使用子查詢從表中獲取clien_num和request_num值。stock_request只需在where查詢子句中使用條件。行集受stock_request記錄限制，行值允許從表client中獲取適當的值。request_list
其次，我建議您不要使用區分大小寫的表名和列名，這可能會導致拼寫錯誤。
最后，您的查詢將是這樣的
SELECT
client.client_name,
request_list.product_num,
request_list.qty_requested
FROM client, request_list, stock_request
WHERE client.client_num = stock_request.client_num AND stock_request.request_num = request_list.request_num
ORDER BY client.client_name , request_list.product_num
檢查小提琴https://www.db-fiddle.com/f/nFQ3s1GGEELCMoPBvPYwyk/1
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/482508.html