MySQL ที่ซับซ้อนนับรวมในการเข้าร่วมแบบข้าม

ขอบคุณสำหรับการอ่าน ฉันลองสิ่งนี้มาสี่ชั่วโมงแล้วและมีความก้าวหน้าที่ดี แต่ก็ไม่สามารถเข้าใจได้

ฉันได้ทำให้แอปพลิเคชันง่ายขึ้นเหลือแค่พารามิเตอร์ที่ฉันกำลังดิ้นรนเท่านั้น

ฉันมีฐานข้อมูลของลูกค้าที่เคยใช้บริการ หนึ่งครั้งต่อปีและตารางค้นหาสองตารางเพื่อให้สามารถเข้ารหัสผลลัพธ์ได้

Table - Gender
gender_code | gender_name
          1 | Male
          2 | Female
          3 | Other

Table - Service
service_code | service_name
           1 | WiFi
           2 | Network
           3 | Telephone

Table - Customers13
transactionid | customerid | service_code | gender_code
            1 |          4 |            3 |           1
            2 |          7 |            1 |           2
            3 |          9 |            1 |           1

Table - Customers14
transactionid | customerid | service_code | gender_code
            1 |         13 |            2 |           2
            2 |          4 |            2 |           1
            3 |         17 |            2 |           2

สิ่งที่ฉันต้องการได้รับคือตารางที่ส่งคืนซึ่งมีการเรียงลำดับการบริการและเพศที่เป็นไปได้ทั้งหมดและจำนวนแต่ละรายการในแต่ละปี ฉันเริ่มต้นด้วยการพยายามนับให้ถูกต้องและใช้แบบสอบถาม MySQL ต่อไปนี้:

select t.service_name, t.gender_name, count13, count14
from (select service_name, gender_name, count(Customers13.gender_code) as count13
from Customers13  
inner join Gender on Customers13.gender_code = Gender.gender_code  
inner join Service on Customers13.service_code = Service.service_code 
group by service_name, gender_name) t
inner join (select service_name, gender_name, count(Customers14.gender_code) as count14
from Customers14  
inner join Gender on Customers14.gender_code = Gender.gender_code  
inner join Service on Customers14.service_code = Service.service_code 
group by service_name, gender_name) m on m.service_name=t.service_name and 
m.gender_name=t.gender_name

สิ่งนี้ทำให้ฉันใกล้จะคืนตารางที่ฉันต้องการได้ แต่จะมีการแข่งขันในทั้งสองตารางเท่านั้น สิ่งที่ฉันต้องการคือการได้บรรทัดแม้ว่าจะไม่มีตารางที่ตรงกันก็ตาม ตัวอย่างเช่น:

service_name | gender_name | count13 | count14
WiFi         | Male        |       1 |       0
WiFi         | Female      |       1 |       0
WiFi         | Other       |       0 |       0
Network      | Male        |       0 |       1
Network      | Female      |       0 |       2
Network      | Other       |       0 |       0
Telephone    | Male        |       1 |       0
Telephone    | Female      |       0 |       0
Telephone    | Other       |       0 |       0

ความพยายามครั้งสุดท้ายของฉันคือใช้การรวมแบบข้ามเพื่อให้สองคอลัมน์แรกถูกต้อง แต่ฉันไม่รู้ว่าจะข้ามไปยังการเติมฟิลด์การนับได้อย่างไร:

เลือก service_name, sex_name จาก Service cross join Gender


mysql count cross-join
person user1967034    schedule 18.09.2014    source แหล่งที่มา
comment
เพื่อให้อธิบายง่ายๆ สักหน่อย คุณเคยพิจารณาใช้ set แทนที่จะคิดว่าเป็น char หรือ varchar หรือไม่? บางอย่างเช่น set('WiFi','Network','Telephone')   -  person luis_pmb    schedule 18.09.2014
comment
ข้อมูลไม่ใช่ของฉัน แต่อยู่ในรูปแบบ .csv อย่างไรก็ตาม สคีมานั้นเป็นของฉันจริงๆ ดังนั้นฉันจึงสามารถตั้งค่าฟิลด์ในแบบที่ฉันต้องการได้อย่างแน่นอน Set ไม่ใช่สิ่งที่ฉันเคยใช้มาก่อน แต่ถ้ามันทำให้ปัญหาง่ายขึ้น ฉันก็ยินดีเป็นอย่างยิ่ง   -  person user1967034    schedule 18.09.2014
comment
คุณมีโต๊ะสำหรับแต่ละปีหรือไม่? แต่ทำไม? นั่นดูเหมือนเป็นความคิดที่แย่และไม่จำเป็น   -  person Strawberry    schedule 18.09.2014
comment
Gender_gender_code ด้วย ใช่ไหม?   -  person Strawberry    schedule 18.09.2014
comment
ในหลายอุตสาหกรรมจำเป็นต้องมีการรายงานประจำปี ไม่ได้หมายความว่าข้อมูลจะถูกเก็บไว้ในตารางฐานข้อมูลในแต่ละปี เพียงแต่บางครั้งจำเป็นต้องจัดการเอาต์พุตซึ่งแบ่งออกเป็นไฟล์รายปี   -  person user1967034    schedule 18.09.2014
comment
ขอขอบคุณ แก้ไข Gender.gender_code แล้ว ความผิดมาจากฉันที่ทำให้ปัญหาง่ายขึ้น   -  person user1967034    schedule 18.09.2014

คำตอบ (2)


arrow_upward
1
arrow_downward

ผูกเพศทั้งหมดเข้ากับบริการทั้งหมดด้วยการเข้าร่วมแบบข้าม จากนั้นปล่อยให้เข้าร่วมแบบสอบถามย่อยของสหภาพ:

SELECT
    s.service_name, g.gender_name, COUNT(c.cnt13), COUNT(c.cnt14)
FROM
    service s
CROSS JOIN
    gender g
LEFT JOIN
    (
        SELECT gender_code, service_code, 1 AS cnt13, NULL AS cnt14
        FROM Customers13
        UNION ALL
        SELECT gender_code, service_code, NULL, 1
        FROM Customers14
    ) AS c
    USING (gender_code, service_code)
GROUP BY s.service_name, g.gender_name
person Gervs    schedule 18.09.2014
comment
คุณสวย คนสวย! นั่นทำงานได้อย่างสมบูรณ์แบบ ฉันสนิทกันมากจนไม่สามารถทำให้พวกเขาเข้าร่วมได้อย่างถูกต้อง ดูเหมือนว่า USING จะสร้างความแตกต่างทั้งหมด - person user1967034; 18.09.2014
comment
การใช้เป็นอีกวิธีหนึ่งในการเขียนเงื่อนไขการเข้าร่วม การรวมทางซ้ายร่วมกับ cross joinj เป็นการหลอกลวง ;-) - person Gervs; 18.09.2014

arrow_upward
0
arrow_downward

แล้วโซลูชันนี้ล่ะ:

select
service_name, gender_name, sum(cnt13), sum(cnt14)
from
(
   select service_name, gender_name, count(c.GENDER_CODE) as cnt13, 0 as cnt14
   from SERVICE s,gender g,customers13 c
   where c.GENDER_CODE=g.GENDER_CODE
   and c.SERVICE_CODE=s.SERVICE_CODE
   group by service_name, gender_name
   union all
   select service_name, gender_name, 0 as cnt13, count(c.GENDER_CODE) as cnt14
   from SERVICE s,gender g,customers14 c
   where c.GENDER_CODE=g.GENDER_CODE
   and c.SERVICE_CODE=s.SERVICE_CODE
   group by service_name, gender_name
)
group by service_name, gender_name
person derlarsschneider    schedule 18.09.2014
comment
ฉันได้รับข้อผิดพลาด: 'รหัสข้อผิดพลาด: 1248 ทุกตารางที่ได้รับมาต้องมีนามแฝงของตัวเอง' ขอขอบคุณสำหรับความช่วยเหลือของคุณมา ณ จุดนี้ - person user1967034; 18.09.2014
comment
ตกลง ฉันได้เปลี่ยนนามแฝง c เป็น d แล้วและมันก็ใช้งานได้ตรงประเด็น มันไม่ได้ทำให้ฉันนับได้ว่าไม่มีค่าที่ตรงกัน และโดยพื้นฐานแล้วมันทำให้ตารางหนึ่งอยู่ทับอีกตารางหนึ่งซึ่งน่าเสียดาย ขอบคุณสำหรับความช่วยเหลือของคุณ แต่ก็ชื่นชม - person user1967034; 18.09.2014