group – PlusMagi's Blog By Pitt Phunsanit

mySql: แสดงข้อมูลหลายแถวไว้ในชุดเดียวคอลัมน์เดียว

ตอนกำลังทำข้อมูลทะเบียนลูกบ้านเพื่อที่จะได้ทำรายชื่อคนที่มีสิทธิที่จะออกเสียงในที่ประชุมนิติของหมู่บ้าน ดูๆ น่าจะไม่ยากแค่เขียน query ง่ายๆ มีเลขที่ห้อง ชื่อเจ้าของ จำนวนพื้นที่ แบบ[code language=”sql” title=”รายชื่อผู้ถือกรรมสิทธิ์รายบุคล”]SELECT
CONCAT(h.house_no, ‘/’, h.house_no_sub) AS houseNo,
CONCAT(p.title, ‘ ‘, p.name, ‘ ‘, p.surname) AS owner,
h.squareMeter
FROM
v1_house AS h
LEFT JOIN
v1_house_owner AS ho ON h.house_id = ho.house_id
LEFT JOIN
v1_peoples AS p ON ho.people_id = p.people_id
ORDER BY h.house_id ASC[/code]ไม่นานก็ได้รายชื่อออกมาทั้ง 843 รายการ แต่จำได้ว่ามีห้องทั้งหมด 730 ห้องแล้วที่เกินมาคืออัลลัย ไล่ดูผลที่ออกมาบางห้องมีเจ้าของมากกว่า 1 คน เป็นเรื่องแล้วเพราะถึงมีเจ้าของกี่คนแต่จะใช้สิทธิได้แค่ห้องละ 1 เท่านั้น ดังนั้นสมมุติว่าห้องไหนมีเจ้าของ 2 คน (เจอว่าบางห้องมี 3 เจ้าของ) จะต้องเขียนให้แสดงรายชื่อในแถวเดียวกัน เช่น ‘1xx/xx’, ‘คุณ พิชญ์ พันธุ์สนิท, คุณ xxx พันธุ์สนิท, คุณ xxx พันธุ์สนิท’, ‘69.58’ แทนที่จะอยู่คนและแถว คนและ row

ตอนแรกจะเขียนแบบใช้ sub query แต่รู้สึกว่าซับซ้อนเกินไปหน่อย ดีที่ไปเห็น GROUP_CONCAT() ลองเขียนดูง่ายกว่าใช้ซับคิวรี่เยอะ[code language=”sql” title=”รายชื่อผู้ถือกรรมสิทธิ์รายห้อง”]SELECT
CONCAT(h.house_no, ‘/’, h.house_no_sub) AS houseNo,
GROUP_CONCAT(CONCAT(p.title, ‘ ‘, p.name, ‘ ‘, p.surname),
‘ ‘) AS owner,
h.squareMeter
FROM
v1_house AS h
LEFT JOIN
v1_house_owner AS ho ON h.house_id = ho.house_id
LEFT JOIN
v1_peoples AS p ON ho.people_id = p.people_id
GROUP BY h.house_id
ORDER BY h.house_id ASC , ho.people_id ASC[/code]ข้อมูลออกมาถูกต้องแล้ว แบบเขียนไม่ยากด้วย ^_^

เลือกช่วงข้อมูล

ระบบที่ทำจะมีการตรวจสอบข้อมูลที่ป้อนเข้ามา โดยบังคับให้ป้อนข้อมูลเข้ามาตามค่าข้อมูลที่มีอยู่ แต่ถ้าจะต้องมา loop เช็กว่าข้อมูลตัวนี้มีในฐานข้อมูลรึเปล่า ก็เสียเวลาและเปลืองทรัพยากร จากการสังเกตุข้อมูลจะอยู่เป็นช่วงๆ เช่น 1 – 5, 8 – 12, 1982 – 2016

ถ้ารู้ช่วงข้อมูล ก็จะตรวจได้ว่าข้อมูลที่ป้อนเข้ามาอยู่ในช่วงที่กำหนดหรือไม่

ไปเจอ Select a range of values ลองแก้ตามได้[code language=”sql”]
WITH Grouped AS ( — Identify groups

SELECT D.bank_id,
grp = D.bank_id – ROW_NUMBER() OVER (
ORDER BY D.bank_id)
FROM banks AS D)
SELECT STUFF ( ( — Concatenate items in the current group

SELECT [text()] = ‘,’ + CONVERT(varchar(11), G2.bank_id)
FROM Grouped AS G2
WHERE G2.grp = Grouped.grp
ORDER BY G2.bank_id
FOR XML PATH (”) ) — Remove initial comma
, 1, 1, ” )
FROM Grouped
GROUP BY Grouped.grp;
[/code]
ผลลัพธ์
1,2,3,4,5,6
8,9,10,11,12,13,14
16,17,18,19,20,21,22,23,24,25,26,27,28,29
31,32,33,34,35,36,37,38,39,40
43,44

ได้เป็นกลุ่มก็จริงแต่ อยากได้ id เริ่มต้นถึง id สุดท้ายของแต่ละกลุ่มเท่านั้น ลองแก้มั่วๆดู[code language=”sql”]
WITH Grouped AS
( SELECT D.bank_id,
grp = D.bank_id – ROW_NUMBER() OVER (
ORDER BY D.bank_id)
FROM banks AS D)
SELECT MIN(Grouped.bank_id) AS start_id,
MAX(Grouped.bank_id) AS end_id
FROM Grouped
GROUP BY Grouped.grp
[/code]
ผลลัพธ์
start_id end_id
———– ———–
1 6
8 14
16 29
31 40
43 44

ได้ตามที่ต้องการเลย

PlusMagi's Blog By Pitt Phunsanit

Tag Archive group

mySql: แสดงข้อมูลหลายแถวไว้ในชุดเดียวคอลัมน์เดียว

เลือกช่วงข้อมูล