我有一個包含“客戶”資訊的表格，另一個包含每個客戶的“門票”資訊。
int-------| varchar -------| varchar
client_id | client_name | client_tickets
---------- ---------------- --------------
1 | Title one | 1,2
2 | Title two | 2,3
簡化票表
int--------| varchar -------| varchar
ticket_id | ticket_name | ticket_price
----------- ------------- --------------
1 | ticketone | 30
2 | tickettwo | 40
3 | ticketthree | 50
4 | ticketfour | 60
5 | ticketfive | 70
使用上述兩個表，我想生成一個帶有單個查詢的單個表，其中包含所有相關資訊以生成一個搜索網格，以便給出以下輸出：
client_id | client_name | client_tickets | ticket_names | ticket_prices
---------- ---------------- ---------------- ----------------------- --
1 | Title one | 1,2 | ticketone,tickettwo | 30,40
2 | Title two | 2,3 | tickettwo,ticketthree | 40,50
ticket_names,ticket_ids,client_name 是 varchar
我想通過一個請求接收最后 5 列，例如：
SELECT s.*,
(SELECT GROUP_CONCAT(ticket_name SEPARATOR ',') FROM tickets_table WHERE ticket_id IN(s.client_tickets)) AS ticket_names,
(SELECT GROUP_CONCAT(ticket_price SEPARATOR ',') FROM tickets_table WHERE ticket_id IN(s.client_tickets)) AS ticket_prices
FROM client_table s where s.client_id=1
哪個好像有問題 有更好的建議嗎？
請提出您的建議
更新： 清理我想要的結果下面的代碼有兩個查詢，我希望這段代碼用一個查詢來完成
$client_result = $conn->query("SELECT * FROM client_table where client_id=1");
while($client_row = $client_result->fetch_assoc()) {
$ticket_result = $conn->query("SELECT * FROM tickets_table where ticket_id IN ($client_row['client_tickets'])");
while($ticket_row = ticket_result->fetch_assoc()) {
echo $ticket_row['ticket_name']."<br>";
}
}
更新 2
我使用建議 @raxi ，但我的 mariadb 是 10.4.17-MariaDB 并且不支持 JSON_ARRAYAGG ，根據參考創建聚合函式 ，使用 SQL解決它
DELIMITER //
DROP FUNCTION IF EXISTS JSON_ARRAYAGG//
CREATE AGGREGATE FUNCTION IF NOT EXISTS JSON_ARRAYAGG(next_value TEXT) RETURNS TEXT
BEGIN
DECLARE json TEXT DEFAULT '[""]';
DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN json_remove(json, '$[0]');
LOOP
FETCH GROUP NEXT ROW;
SET json = json_array_append(json, '$', next_value);
END LOOP;
END //
DELIMITER ;
uj5u.com熱心網友回復：
你想要一個相當簡單的SELECT查詢與一些LEFT/INNER JOIN（s）。
這個網站有一些很好的例子/解釋，看起來非常接近你的需要：https : //www.mysqltutorial.org/mysql-inner-join.aspx
我會給你一個快速的作業示例，但我不太清楚相關列是什么資料型別。兩個表的_id列都可能是 INTEGER 的某種變體，它們是否也是主鍵（或至少索引？），client_name/ticket_name可能是 VARCHAR/TEXT/STRING 型別，但剩余的列究竟是如何存盤的？作為 json 或陣列或 ? ( 詳情)
您還用 標記了您的帖子PHP，您是在 SQL 查詢之后嗎？或尋找其中包含 SQL 的 PHP 代碼。
更新
架構的改進版本
CREATE TABLE clients (
client_id SERIAL,
client_name VARCHAR(255) NOT NULL,
PRIMARY KEY (client_id)
);
CREATE TABLE tickets (
ticket_id SERIAL,
ticket_name VARCHAR(255) NOT NULL,
ticket_price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (ticket_id)
);
-- A junction table to glue those 2 tables together (N to N relationship)
CREATE TABLE client_tickets (
client_id BIGINT UNSIGNED NOT NULL,
ticket_id BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (client_id, ticket_id)
);
我改變了資料型別。
client_name并且ticket_name仍然是 VARCHARS。我已將它們標記為NOT NULL（例如：必填欄位），但如果您不喜歡那樣，可以洗掉該部分。
client_id/ ticket_id/ticket_price也NOT NULL不過改變有負面的副作用。
ticket_price現在是一個 DECIMAL 欄位，它可以存盤諸如1299.50或 之類50.00 的數字。(10,2)位意味著它涵蓋了最多 8 位整數（美元/歐元/任何數字）和 2 位小數（美分）的所有可能數字。所以你可以存盤從 $ - 99.999.999,99到 $ 99.999.999,99 的任何東西。在 SQL 中總是用這種表示法寫數字（比如 70k）：（70000.00例如：一個點，而不是一個逗號；沒有千位分隔符）。
client_id并且ticket_id現在都是SERIALs，這是 s 的簡寫，BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE而且它們都PRIMARY KEY在此之上。這可能聽起來很復雜，但它們仍然只是普通的INTEGERs，具有諸如4或之類的值12。
該UNIQUE位阻止您擁有 2 個具有相同 ID 號的客戶端，這AUTO_INCREMENT意味著當您添加新客戶端時，您不必指定 ID（盡管您可以這樣做）；你可以這樣做：
INSERT INTO clients (client_name) values ('Fantastic Mr Fox');
并且client_id將自動設定（隨時間增加）。而且同樣適用ticket_id于其他表。
.
我已將您的原始client_tickets列替換為單獨的連接表。那里的記錄存盤client_id客戶的和ticket_id屬于他們的。一個客戶可以在聯結表中有多個記錄（他們擁有的每張票有一個記錄）。同樣，可以在任意數量的行中提及票證。某個人可能client_id在聯結表中沒有任何記錄。同樣，某個人可能ticket_id在聯結表中沒有任何記錄。此表中不能存在相同的記錄（由 強制執行PRIMARY KEY）。
測驗資料
接下來，我們可以在那里放一些資料來測驗它：
-- Create some tickets
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (1, 'ticketone', '30' );
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (2, 'tickettwo', '40' );
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (3, 'ticketthree', '50' );
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (4, 'ticketfour', '60' );
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (5, 'ticketfive', '70' );
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (6, 'ticketsix', '4' );
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (7, 'ticketseven', '9' );
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (8, 'ticketeight', '500' );
-- Create some users, and link them to some of these tickets
INSERT INTO clients (client_id, client_name) values (1, 'John');
INSERT INTO client_tickets (client_id, ticket_id) values (1, 3);
INSERT INTO client_tickets (client_id, ticket_id) values (1, 7);
INSERT INTO client_tickets (client_id, ticket_id) values (1, 1);
INSERT INTO clients (client_id, client_name) values (2, 'Peter');
INSERT INTO client_tickets (client_id, ticket_id) values (2, 5);
INSERT INTO client_tickets (client_id, ticket_id) values (2, 2);
INSERT INTO client_tickets (client_id, ticket_id) values (2, 3);
INSERT INTO clients (client_id, client_name) values (3, 'Eddie');
INSERT INTO client_tickets (client_id, ticket_id) values (3, 8);
INSERT INTO clients (client_id, client_name) values (9, 'Fred');
-- Note: ticket #3 is owned by both client #1/#2;
-- Note: ticket #4 and #6 are unused;
-- Note: client #9 (Fred) has no tickets;
查詢
獲取所有現有關系（無票客戶被排除在外，無所有者票被排除在外）
SELECT clients.*
, tickets.*
FROM client_tickets AS ct
INNER JOIN clients ON ct.client_id = clients.client_id
INNER JOIN tickets ON ct.ticket_id = tickets.ticket_id
ORDER BY clients.client_id ASC
, tickets.ticket_id ASC ;
獲取所有仍然免費的門票（無所有者）
SELECT tickets.*
FROM tickets
WHERE tickets.ticket_id NOT IN (
SELECT ct.ticket_id
FROM client_tickets AS ct
)
ORDER BY tickets.ticket_id ASC ;
獲取所有客戶（甚至無票客戶）的串列，并包括每個客戶的門票數量和門票總價。
SELECT clients.*
, COALESCE(COUNT(tickets.ticket_id), 0) AS amount_of_tickets
, COALESCE(SUM(tickets.ticket_price), 0.00) AS total_price
FROM clients
LEFT JOIN client_tickets AS ct ON ct.client_id = clients.client_id
LEFT JOIN tickets ON ct.ticket_id = tickets.ticket_id
GROUP BY clients.client_id
ORDER BY clients.client_id ASC ;
把所有有趣的資訊放在一起（沒有主人的票被遺漏了）
SELECT clients.*
, COALESCE(COUNT(sub.ticket_id), 0) AS amount_of_tickets
, COALESCE(SUM(sub.ticket_price), 0.00) AS total_price
, JSON_ARRAYAGG(sub.js_tickets_row) AS js_tickets_rows
FROM clients
LEFT JOIN client_tickets AS ct ON ct.client_id = clients.client_id
LEFT JOIN (
SELECT tickets.*
, JSON_OBJECT( 'ticket_id', tickets.ticket_id
, 'ticket_name', tickets.ticket_name
, 'ticket_price', tickets.ticket_price
) AS js_tickets_row
FROM tickets
) AS sub ON ct.ticket_id = sub.ticket_id
GROUP BY clients.client_id
ORDER BY clients.client_id ASC ;
-- sidenote: output column `js_tickets_rows` (a json array) may contain NULL values
帶有一些匯總資料的所有工單的串列
SELECT tickets.*
, IF(COALESCE(COUNT(clients.client_id), 0) > 0
, TRUE, FALSE) AS active
, COALESCE( COUNT(clients.client_id), 0) AS amount_of_clients
, IF(COALESCE( COUNT(clients.client_id), 0) > 0
, GROUP_CONCAT(clients.client_name SEPARATOR ', ')
, NULL) AS client_names
FROM tickets
LEFT JOIN client_tickets AS ct ON ct.ticket_id = tickets.ticket_id
LEFT JOIN clients ON ct.client_id = clients.client_id
GROUP BY tickets.ticket_id
ORDER BY tickets.ticket_id ASC
, clients.client_id ASC ;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/382367.html
上一篇：MySQL子字串自連接
下一篇：全聯盟候補