group – PlusMagi's Blog By Pitt Phunsanit

ตอนกำลังทำข้อมูลทะเบียนลูกบ้านเพื่อที่จะได้ทำรายชื่อคนที่มีสิทธิที่จะออกเสียงในที่ประชุมนิติของหมู่บ้าน ดูๆ น่าจะไม่ยากแค่เขียน query ง่ายๆ มีเลขที่ห้อง ชื่อเจ้าของ จำนวนพื้นที่ แบบ

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

ไม่นานก็ได้รายชื่อออกมาทั้ง 843 รายการ แต่จำได้ว่ามีห้องทั้งหมด 730 ห้องแล้วที่เกินมาคืออัลลัย ไล่ดูผลที่ออกมาบางห้องมีเจ้าของมากกว่า 1 คน เป็นเรื่องแล้วเพราะถึงมีเจ้าของกี่คนแต่จะใช้สิทธิได้แค่ห้องละ 1 เท่านั้น ดังนั้นสมมุติว่าห้องไหนมีเจ้าของ 2 คน (เจอว่าบางห้องมี 3 เจ้าของ) จะต้องเขียนให้แสดงรายชื่อในแถวเดียวกัน เช่น ‘1xx/xx’, ‘คุณ พิชญ์ พันธุ์สนิท, คุณ xxx พันธุ์สนิท, คุณ xxx พันธุ์สนิท’, ‘69.58’ แทนที่จะอยู่คนและแถว คนและ row

ตอนแรกจะเขียนแบบใช้ sub query แต่รู้สึกว่าซับซ้อนเกินไปหน่อย ดีที่ไปเห็น GROUP_CONCAT() ลองเขียนดูง่ายกว่าใช้ซับคิวรี่เยอะ

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

ข้อมูลออกมาถูกต้องแล้ว แบบเขียนไม่ยากด้วย ^_^

Byphunsanit

group, range, value, กลุ่ม, ค่า, ช่วง

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

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

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

ไปเจอ Select a range of values ลองแก้ตามได้

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;

ผลลัพธ์
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 สุดท้ายของแต่ละกลุ่มเท่านั้น ลองแก้มั่วๆดู

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

ผลลัพธ์
start_id end_id
———– ———–
1 6
8 14
16 29
31 40
43 44

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

Tag Archive group

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

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