반응형
테이블 열을 행으로 피벗하고 값이 다른 테이블에 없는 경우 한 테이블의 값 사용
이상적인 레이아웃이 없는 테이블로 보고서를 만들고 싶습니다.엑셀로 피벗 테이블을 만들 수는 있지만 웹 기반 대시보드를 위해 온라인으로 보고서를 만들고 싶습니다.피벗 테이블에 대해 http://datacharmer.org/downloads/pivot_tables_mysql_5.pdf 을 살펴보았는데 값이 존재하는지 확인하기 위해 다른 테이블에 레코드가 있는 경우 테이블에서 선택을 선택합니다.나는 mysql에서 임시 테이블로 피벗 테이블을 만들 수 있었지만 보고 소프트웨어는 데이터 조작을 허용하지 않았습니다.
일부 답변(qid 742)은 survey_survey_144477 표에 있지만 다른 답변은 답변 표에 있습니다.그래서 qid가 survey_answer에 존재하지 않는 경우 survey_survey_144477의 값을 사용하려고 했습니다.
이것은 오직 하나의 특정한 보고서에 대한 것입니다.설문 결과는 table survey_survey_144477에 나와 있으며 몇 개의 질문만 있을 뿐 다른 설문에서는 20개 또는 30개의 질문이 있을 수 있습니다.그래서 모든 보고서의 모든 열 제목을 바꿀 필요가 없도록 이 역학 관계를 만들고 싶습니다.survey_survey_144477의 열 일부에 qid가 있지만 운이 없기 때문에 열 구문 분석도 찾았습니다.또한 모든 열을 선택할 수 있는 방법을 결정하려고 했지만 토큰, 마지막 페이지 등 일부는 무시했습니다. 왜냐하면 동적으로 만들면 도움이 되지만 운도 좋지 않을 것 같아서요.
어떤 도움이라도 주시면 감사하겠습니다.
원하는 출력은 다음과 같습니다.
| How would you rate ... | Do you think our product ... | One a scale of 1-10 ... | Will you recommend |
+-----------------+------+------------------------------+-------------------------+--------------------+
| Excellent | Yes | 7 | Yes |
| Very good | No | 8 | Yes |
시도해 본 결과:
## Create pivot table
CREATE TEMPORARY TABLE temp
SELECT id,'144477X148X740' AS lid, 740 AS qid, 144477X148X740 AS value FROM survey_survey_144477
UNION ALL
SELECT id, '144477X148X741' AS lid, 741 AS qid, 144477X148X741 AS value FROM survey_survey_144477
UNION ALL
SELECT id, '144477X148X742SQ001' AS lid, 742 AS qid, 144477X148X742SQ001 AS value FROM survey_survey_144477
UNION ALL
SELECT id, '144477X148X745' AS lid, 745 AS qid, 144477X148X745 AS value FROM survey_survey_144477;
## Get the answers. Table survey_answers has answers from dropdowns and selection boxes.
## Other answers are in the temp table.
select T.id, SQ.question, SA.answer
from survey_questions SQ
join survey_answers SA on SA.qid = SQ.qid
join temp T on T.qid = SQ.qid
where SQ.sid = 144477
-- and exists (select SA.answer as answer
-- from survey_answers SA
-- join survey_questions SQ on SA.qid = SQ.qid
-- join temp T on T.qid = SQ.qid)
CREATE TEMPORARY TABLE temp2
select id, qid, value
from temp
where not exists (select qid from survey_answers
where temp.qid = survey_answers.qid);
describe survey_survey_144477;
+---------------------+----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+----------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| token | varchar(35) | YES | | NULL | |
| submitdate | datetime | YES | | NULL | |
| lastpage | int(11) | YES | | NULL | |
| startlanguage | varchar(20) | NO | | NULL | |
| ipaddr | text | YES | | NULL | |
| refurl | text | YES | | NULL | |
| 144477X148X740 | varchar(5) | YES | | NULL | |
| 144477X148X741 | varchar(5) | YES | | NULL | |
| 144477X148X742SQ001 | decimal(30,10) | YES | | NULL | |
| 144477X148X745 | varchar(5) | YES | | NULL | |
| 144477X148X748 | text | YES | | NULL | |
+---------------------+----------------+------+-----+---------+----------------+
describe survey_questions ;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| qid | int(11) | NO | PRI | NULL | auto_increment |
| parent_qid | int(11) | NO | MUL | 0 | |
| sid | int(11) | NO | MUL | 0 | |
| gid | int(11) | NO | MUL | 0 | |
| type | varchar(1) | NO | MUL | T | |
| title | varchar(20) | NO | | | |
| question | mediumtext | NO | | NULL | |
| preg | mediumtext | YES | | NULL | |
| help | mediumtext | YES | | NULL | |
| other | varchar(1) | NO | | N | |
| mandatory | varchar(1) | YES | | NULL | |
| question_order | int(11) | NO | | NULL | |
| language | varchar(20) | NO | PRI | en | |
| scale_id | int(11) | NO | | 0 | |
| same_default | int(11) | NO | | 0 | |
| relevance | mediumtext | YES | | NULL | |
| modulename | varchar(255) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
describe survey_answers ;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| qid | int(11) | NO | PRI | 0 | |
| code | varchar(5) | NO | PRI | | |
| answer | mediumtext | NO | | NULL | |
| sortorder | int(11) | NO | MUL | NULL | |
| assessment_value | int(11) | NO | | 0 | |
| language | varchar(20) | NO | PRI | en | |
| scale_id | int(11) | NO | PRI | 0 | |
+------------------+-------------+------+-----+---------+-------+
select id, 144477X148X740, 144477X148X741, 144477X148X742SQ001, 144477X148X745, 144477X148X748 from survey_survey_144477 limit 10;
+----+----------------+----------------+---------------------+----------------+----------------+
| id | 144477X148X740 | 144477X148X741 | 144477X148X742SQ001 | 144477X148X745 | 144477X148X748 |
+----+----------------+----------------+---------------------+----------------+----------------+
| 1 | 1 | A2 | 5.0000000000 | A2 | name@email.com |
| 2 | 1 | A2 | 5.0000000000 | A2 | name@email.com |
| 3 | 1 | A2 | 5.0000000000 | A2 | name@email.com |
| 4 | 1 | A2 | 5.0000000000 | A2 | name@email.com |
| 5 | 1 | A2 | 5.0000000000 | A2 | name@email.com |
| 6 | 1 | A2 | 5.0000000000 | A2 | name@email.com |
| 7 | 1 | A2 | 5.0000000000 | A2 | name@email.com |
| 8 | 1 | A2 | 5.0000000000 | A2 | name@email.com |
| 9 | 1 | A2 | 5.0000000000 | A2 | name@email.com |
| 10 | 1 | A2 | 5.0000000000 | A2 | name@email.com |
+----+----------------+----------------+---------------------+----------------+----------------+
select qid, sid, question from survey_questions where sid = 144477;
+-----+--------+-------------------------------------------------------------------------------------------+
| qid | sid | question |
+-----+--------+-------------------------------------------------------------------------------------------+
| 748 | 144477 | Please enter your email address or phone number if you would like someone to contact you. |
| 740 | 144477 | How would you rate our product's quality? |
| 741 | 144477 | Do you think our product helps your business? |
| 742 | 144477 | One a scale of 1-10, how would you rate the value of our product? |
| 743 | 144477 | |
| 745 | 144477 | Will you recommend our product? |
+-----+--------+-------------------------------------------------------------------------------------------+
select * from survey_answers where qid between 740 and 745;
+-----+------+-----------+-----------+------------------+----------+----------+
| qid | code | answer | sortorder | assessment_value | language | scale_id |
+-----+------+-----------+-----------+------------------+----------+----------+
| 740 | 1 | Excellent | 1 | 1 | en | 0 |
| 740 | 2 | Very good | 2 | 1 | en | 0 |
| 740 | 3 | Good | 3 | 1 | en | 0 |
| 740 | 4 | Fair | 4 | 1 | en | 0 |
| 740 | 5 | Poor | 5 | 1 | en | 0 |
| 741 | A1 | Yes | 1 | 0 | en | 0 |
| 741 | A2 | No | 2 | 0 | en | 0 |
| 745 | A1 | Yes | 1 | 0 | en | 0 |
| 745 | A2 | No | 2 | 0 | en | 0 |
+-----+------+-----------+-----------+------------------+----------+----------+
업데이트:
한 테이블에서 다른 테이블로 값을 매핑하기 위해 테이블을 만들었습니다.이를 자동화하는 것은 나중에 언급할 과제입니다.
CREATE TABLE `survey_answers_lookup` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sid` int(11) NOT NULL DEFAULT '0',
`qid` int(11) NOT NULL DEFAULT '0',
`survey_table_row_id` int(11) NOT NULL DEFAULT '0' COMMENT 'id that is in the survey_<id> table',
`answer` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,740,1,1);
INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,741,1,'A1');
INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,742,1,'5.0000000000');
INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,745,1,'A1');
INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,748,1,'1@email.com');
INSERT INTO survey_answers_lookup(sid,qid,survey_table_row_id,answer)
VALUES (144477,740,2,'2');
INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,741,2,'A2');
INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,742,2,'8.0000000000');
INSERT INTO survey_answers_lookup(sid,qid,survey_table_row_id,answer)
VALUES (144477,745,2,'A2');
INSERT INTO survey_answers_lookup(sid,qid,survey_table_row_id,answer)
VALUES (144477,748,2,'2@email.com');
INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,740,3,'5');
INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,741,3,'A2');
INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,742,3,'10.0000000000');
INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
VALUES (144477,745,3,'A2');
INSERT INTO survey_answers_lookup(sid,qid,survey_table_row_id,answer)
VALUES (144477,748,3,'3@email.com');
결과적으로
select * from survey_answers_lookup;
# id, sid, qid, survey_table_row_id, answer
'1', '144477', '740', '1', '1'
'2', '144477', '741', '1', 'A1'
'3', '144477', '742', '1', '5.0000000000'
'4', '144477', '745', '1', 'A1'
'5', '144477', '748', '1', '1@email.com'
'6', '144477', '740', '2', '2'
'7', '144477', '741', '2', 'A2'
'8', '144477', '742', '2', '8.0000000000'
'9', '144477', '745', '2', 'A2'
'10', '144477', '748', '2', '2@email.com'
'11', '144477', '740', '3', '5'
'12', '144477', '741', '3', 'A2'
'13', '144477', '742', '3', '10.0000000000'
'14', '144477', '745', '3', 'A2'
'15', '144477', '748', '3', '3@email.com'
질문과 답변을 얻으려는 시도입니다.
-- Gives all of the rows from the answers_table even if that is not in the
-- survey_answers_lookup table because of the left join. 740 should only be printed three times.
select
--SQ.qid,
SA.code,
SQ.question,
SQ.type,
-- Types can be: ! * 1 E F K L M N Q R S T X Y
if (SQ.type IN ('B','M','L'), SA.answer, LU2.answer) as 'answer'
from survey_questions SQ
left join survey_answers SA on SA.qid = SQ.qid
-- and SQ.type IN ('B','M','L')
left join survey_answers_lookup LU on LU.qid = SQ.qid
and LU.answer = SA.code
-- and SQ.type IN ('B','M','L')
left join survey_answers_lookup LU2 on LU2.qid = SQ.qid
and SQ.title = 'Q7'
where SQ.sid = 144477;
# --SQ.qid, code, question, type, answer
'740', '1', 'How would you rate our product\'s quality?', 'L', 'Excellent'
'740', '2', 'How would you rate our product\'s quality?', 'L', 'Very good'
'740', '5', 'How would you rate our product\'s quality?', 'L', 'Poor'
'740', '3', 'How would you rate our product\'s quality?', 'L', 'Good'
'740', '4', 'How would you rate our product\'s quality?', 'L', 'Fair'
'741', 'A1', 'Do you think our product helps your business?', 'L', 'Yes'
'741', 'A2', 'Do you think our product helps your business?', 'L', 'No'
'741', 'A2', 'Do you think our product helps your business?', 'L', 'No'
'742', NULL, 'One a scale of 1-10, how would you rate the value of our product?', 'K', NULL
'745', 'A1', 'Will you recommend our product?', 'L', 'Yes'
'745', 'A2', 'Will you recommend our product?', 'L', 'No'
'745', 'A2', 'Will you recommend our product?', 'L', 'No'
'748', NULL, 'Please enter your email address or phone number if you would like someone to contact you.', 'S', '1@email.com'
'748', NULL, 'Please enter your email address or phone number if you would like someone to contact you.', 'S', '2@email.com'
'748', NULL, 'Please enter your email address or phone number if you would like someone to contact you.', 'S', '3@email.com'
-- 둘 이상의 행을 반환합니다.
select
SQ.qid,
SQ.question,
SQ.type,
-- Types can be: ! * 1 E F K L M N Q R S T X Y
if (SQ.type IN ('B','M','L'),
(select SA.answer
from survey_answers SA
join survey_questions SQ2 on SQ2.qid = SA.qid
join survey_answers_lookup LU on LU.qid = SQ2.qid
and LU.answer = SA.code),
(select LU2.answer
from survey_answers_lookup LU2
join survey_questions SQ3 on SQ3.qid = LU2.qid)
) as answer
from survey_questions SQ
where SQ.sid = 144477;
이것은 유형이 'B', 'M', 'L'인 경우에 작동합니다.
select
-- qid,
-- SA.code,
question,
-- Types can be: ! * 1 E F K L M N Q R S T X Y
if (type IN ('B','M','L'),
(select answer
from survey_answers
where survey_questions.qid = survey_answers.qid
and survey_answers_lookup.qid = survey_questions.qid
and survey_answers_lookup.answer = survey_answers.code
),
(0 -- select answer
-- from survey_answers_lookup
-- where survey_questions.qid = survey_answers_lookup.qid
)
) as answer
from survey_questions
join survey_answers_lookup on survey_answers_lookup.qid = survey_questions.qid
where survey_questions.sid = 144477;
'Do you think our product helps your business?', 'Yes'
'Do you think our product helps your business?', 'No'
'Do you think our product helps your business?', 'No'
'How would you rate our product\'s quality?', 'Excellent'
'How would you rate our product\'s quality?', 'Very good'
'How would you rate our product\'s quality?', 'Poor'
'One a scale of 1-10, how would you rate the value of our product?', '0'
'One a scale of 1-10, how would you rate the value of our product?', '0'
'One a scale of 1-10, how would you rate the value of our product?', '0'
'Please enter your email address or phone number if you would like someone to contact you.', '0'
'Please enter your email address or phone number if you would like someone to contact you.', '0'
'Please enter your email address or phone number if you would like someone to contact you.', '0'
'Will you recommend our product?', 'Yes'
'Will you recommend our product?', 'No'
'Will you recommend our product?', 'No'
-- 오류: 하위 쿼리가 하나 이상의 행을 반환합니다.
select
-- qid,
-- SA.code,
question,
-- Types can be: ! * 1 E F K L M N Q R S T X Y
if (type IN ('B','M','L'),
(select answer
from survey_answers
where survey_questions.qid = survey_answers.qid
and survey_answers_lookup.qid = survey_questions.qid
and survey_answers_lookup.answer = survey_answers.code
),
(select answer
from survey_answers_lookup
join survey_questions on survey_questions.qid = survey_answers_lookup.qid
where not exists (select * -- if the value is not in survey_answers table (not exists)
from survey_answers
where survey_answers.qid = survey_answers_lookup.qid
LIMIT 1
)
)
) as answer
from survey_questions
join survey_answers_lookup on survey_answers_lookup.qid = survey_questions.qid
where survey_questions.sid = 144477;
-- ----------------
-- ----------------
select
-- survey_answers_lookup.survey_table_row_id,
-- qid,
-- SA.code,
question,
-- Types can be: ! * 1 E F K L M N Q R S T X Y
if (type IN ('B','M','L'),
(select answer
from survey_answers
where survey_questions.qid = survey_answers.qid
and survey_answers_lookup.qid = survey_questions.qid
and survey_answers_lookup.answer = survey_answers.code
),
(select answer
from survey_answers_lookup
where qid not in (select qid
from survey_answers
)
)
) as answer
from survey_questions
join survey_answers_lookup on survey_answers_lookup.qid = survey_questions.qid
where survey_questions.sid = 144477;
아이디어는 유형이 'B', 'M', 'L'인 경우 table survey_answers에서 답을 얻고, 다른 유형인 경우 table survey_answers_lookup에서 답을 얻는 것입니다.답변이 해당 표에 없는 경우 qid가 table survey_answers에 없습니다.Table survey_answers_lookup에는 qid의 742 및 748에 대한 세 가지 답변이 있으며 쿼리는 어떤 값을 얻어야 할지 알 수 없습니다.거기에 참여하려면 다른 가치를 더해야 할 것 같습니다.감사해요.
select answer, qid
from survey_answers_lookup
where qid not in (select qid from survey_answers)
# answer, qid
'5.0000000000', '742'
'1@email.com', '748'
'8.0000000000', '742'
'2@email.com', '748'
'10.0000000000', '742'
'3@email.com', '748'
지저분할 수도 있지만 카운터를 추가하고 카운터를 테이블 서베이_답변_룩업의 id와 비교해도 될까요?
select answer
from survey_answers_lookup
where qid not in (select qid from survey_answers)
and id = @counter
제가 할 수 있었던 것 같아요.
set @counter = 0;
select
@counter := @counter+1 AS newindex,
survey_answers_lookup.id as id,
survey_answers_lookup.survey_table_row_id,
survey_answers_lookup.qid,
question,
-- Types can be: ! * 1 E F K L M N Q R S T X Y
if (type IN ('B','M','L'),
(select answer
from survey_answers
where survey_questions.qid = survey_answers.qid
and survey_answers_lookup.qid = survey_questions.qid
and survey_answers_lookup.answer = survey_answers.code
),
(select answer
from survey_answers_lookup
where qid not in (select qid from survey_answers)
and id = @counter
)
) as answer
from survey_questions
join survey_answers_lookup on survey_answers_lookup.qid = survey_questions.qid
where survey_questions.sid = 144477
order by survey_answers_lookup.id;
# newindex, id, survey_table_row_id, qid, question, answer
'1', '1', '1', '740', 'How would you rate our product\'s quality?', 'Excellent'
'2', '2', '1', '741', 'Do you think our product helps your business?', 'Yes'
'3', '3', '1', '742', 'One a scale of 1-10, how would you rate the value of our product?', '5.0000000000'
'4', '4', '1', '745', 'Will you recommend our product?', 'Yes'
'5', '5', '1', '748', 'Please enter your email address or phone number if you would like someone to contact you.', '1@email.com'
'6', '6', '2', '740', 'How would you rate our product\'s quality?', 'Very good'
'7', '7', '2', '741', 'Do you think our product helps your business?', 'No'
'8', '8', '2', '742', 'One a scale of 1-10, how would you rate the value of our product?', '8.0000000000'
'9', '9', '2', '745', 'Will you recommend our product?', 'No'
'10', '10', '2', '748', 'Please enter your email address or phone number if you would like someone to contact you.', '2@email.com'
'11', '11', '3', '740', 'How would you rate our product\'s quality?', 'Poor'
'12', '12', '3', '741', 'Do you think our product helps your business?', 'No'
'13', '13', '3', '742', 'One a scale of 1-10, how would you rate the value of our product?', '10.0000000000'
'14', '14', '3', '745', 'Will you recommend our product?', 'No'
'15', '15', '3', '748', 'Please enter your email address or phone number if you would like someone to contact you.', '3@email.com'
이제 설문조사를 새로 작성하고 질문에 답할 때 이 표를 업데이트하는 트리거를 만들어야 합니다.
(댓글에서)
SELECT *
FROM survey_survey_144477
UNION ALL
SELECT id,
144477X148X741,
741 AS qid,
144477X148X741 AS value
FROM survey_survey_144477
UNION ALL
SELECT id,
144477X148X742SQ001,
742 AS qid,
144477X148X742SQ001 AS value
FROM survey_survey_144477
UNION ALL
SELECT id,
144477X148X745, 745 AS qid,
144477X148X745 AS value
FROM survey_survey_144477;
select T.id, SQ.question, SQ.type, -- SA.answer,
case SQ.type
when 'L' then SA.answer -- list choice --
when 'M' then -- multiple choice --
when 'B' then -- array-10(B)
when 'T' OR 'N' then T.value -- text or numeric
when 'K' then T.value -- slider --
else "Do nothing" end as 'answer'
from survey_questions SQ
join survey_answers SA ON SA.qid = SQ.qid
join temp T ON T.qid = SQ.qid and T.value = SA.code
where SQ.sid = 144477 --
and SQ.type = 'K'
order by T.id;
피벗이 필요할 때마다 SQL로 할 생각을 합니다.하지만 저는 그 생각이 너무 고통스러워서 감당할 수 없다고 재빨리 일축합니다.대신 저장된 데이터를 피벗하기 위해 PHP로 코드를 작성합니다. 데이터베이스에 대해 보다 "자연스러운" 방법입니다. 즉, 한 개당 한 행, 한 개당 한 열이 아니라 한 행입니다.
이를 통해 설문조사별 1개의 테이블 문제를 해결할 수 있습니다.빠른 속도로 테이블을 만드는 것은 일반적으로 디자인이 좋지 않습니다.
즉, SQL에서 프로세스를 자동화하는 데 필요한 것을 확인하기로 결정했습니다.아직도 엉망입니다.
문제를 요약하는 방법:
이 세 개의 표를 봤을 때,
질문:
select qid, sid, question from survey_questions where sid = 144477;
+-----+--------+-------------------------------------------------------------------------------------------+
| qid | sid | question |
+-----+--------+-------------------------------------------------------------------------------------------+
| 748 | 144477 | Please enter your email address or phone number if you would like someone to contact you. |
| 740 | 144477 | How would you rate our product's quality? |
| 741 | 144477 | Do you think our product helps your business? |
| 742 | 144477 | One a scale of 1-10, how would you rate the value of our product? |
| 743 | 144477 | |
| 745 | 144477 | Will you recommend our product? |
+-----+--------+-------------------------------------------------------------------------------------------+
답변 조회(매핑?)
select * from survey_answers_lookup;
# id, sid, qid, survey_table_row_id, answer
'1', '144477', '740', '1', '1'
'2', '144477', '741', '1', 'A1'
'3', '144477', '742', '1', '5.0000000000'
'4', '144477', '745', '1', 'A1'
'5', '144477', '748', '1', '1@email.com'
'6', '144477', '740', '2', '2'
'7', '144477', '741', '2', 'A2'
'8', '144477', '742', '2', '8.0000000000'
'9', '144477', '745', '2', 'A2'
'10', '144477', '748', '2', '2@email.com'
'11', '144477', '740', '3', '5'
'12', '144477', '741', '3', 'A2'
'13', '144477', '742', '3', '10.0000000000'
'14', '144477', '745', '3', 'A2'
'15', '144477', '748', '3', '3@email.com'
답변:
select * from survey_answers where qid between 740 and 745;
+-----+------+-----------+-----------+------------------+----------+----------+
| qid | code | answer | sortorder | assessment_value | language | scale_id |
+-----+------+-----------+-----------+------------------+----------+----------+
| 740 | 1 | Excellent | 1 | 1 | en | 0 |
| 740 | 2 | Very good | 2 | 1 | en | 0 |
| 740 | 3 | Good | 3 | 1 | en | 0 |
| 740 | 4 | Fair | 4 | 1 | en | 0 |
| 740 | 5 | Poor | 5 | 1 | en | 0 |
| 741 | A1 | Yes | 1 | 0 | en | 0 |
| 741 | A2 | No | 2 | 0 | en | 0 |
| 745 | A1 | Yes | 1 | 0 | en | 0 |
| 745 | A2 | No | 2 | 0 | en | 0 |
+-----+------+-----------+-----------+------------------+----------+----------+
원하는 출력은 다음과 같습니다.
| How would you rate ... | Do you think our product ... | One a scale of 1-10 ... | Will you recommend |
+-----------------+------+------------------------------+-------------------------+--------------------+
| Excellent | Yes | 7 | Yes |
| Very good | No | 8 | Yes |
(의견: 폭이 너무 넓습니다.)
3단계로 진행합니다.
1단계: 데이터 가져오기:
SELECT qid, q.question, a.answer
FROM survey_questions AS q
JOIN survey_answers_lookup AS al USING(sid, qid)
JOIN survey_answers AS a USING(sid, qid)
WHERE sid = 144477
AND al.answer = a.code
ORDER BY qid;
원하는 출력은 얻지만 아직 피벗되지 않았는지 확인합니다.마음에 드시면 2단계로 넘어가도록 하겠습니다.
2단계:CASE WHEN안 가요 안 요. 무슨 말씀을 하시는지 이해가 안 가요. 테이블에 보이지가 않아요.
3단계: 피벗
생성된 코드에 공급할 하위 쿼리를 만듭니다.
http://mysql.rjweb.org/doc.php/pivot
언급URL : https://stackoverflow.com/questions/43036858/pivot-table-columns-to-rows-and-use-a-value-in-one-table-if-it-does-not-exists-i
반응형
'sourcetip' 카테고리의 다른 글
| 오류: "정의되지 않은 공급자가 있습니다!일반적으로 이것은 당신이 순환적인 의존 관계를 가지고 있다는 것을 의미합니다." (0) | 2023.09.15 |
|---|---|
| 선택 후 동작 트리거 선택2 (0) | 2023.09.15 |
| Laravel 5.1의 URL로 돌아가기 (0) | 2023.09.10 |
| Swift에서 목록의 배경색을 수정하는 방법UI? (0) | 2023.09.10 |
| 비동기 보이드, ASP.순 및 미결 작업 수 (0) | 2023.09.10 |