그누보드 QA - sql 질문... 드립니다.

sql 질문... 드립니다.

2021.12.09 10:55:17 1882 (59.♡.♡.2)

본문


        <tr>
            <th>순위</th>
            <th>총판장명</th>
            <th>구매품목</th>
            <th>구매횟수</th>
            <th>총 구매금액</th>
            <th>판매품목</th>
            <th>판매횟수</th>
            <th>총 판매금액</th>
            <th>판매율</th>
        </tr>
        </thead>
        <tbody>
        <?
        $sum_count = "0";
        $sql = "
        select a.mb_name, b.mb_id, c.mb_id, count(*) as cnt,
        group_concat(DISTINCT b.wr_12 separator '||') wr_12b,
        group_concat(DISTINCT c.wr_12 separator '||') wr_12c,
        count(DISTINCT b.wr_31) as b_name,
        count(DISTINCT c.wr_31) as c_name,
        sum(DISTINCT b.wr_31) as totb,
        sum(DISTINCT c.wr_31) as totc,
        ROUND((sum(DISTINCT c.wr_31) / sum(DISTINCT b.wr_31)) * 100, 1) as rat
          from g5_member a
                   inner join g5_write_csorder b on a.mb_id = b.mb_id
                   inner join g5_write_cscenter c on b.mb_id = c.mb_id
         where
               b.wr_29 between '{$stx1}' and '{$stx2}' and b.wr_50 like '%{$stx7}%' and NOT b.wr_54 IN ('exchange') and NOT b.wr_7 IN ('4') and
               c.wr_29 between '{$stx1}' and '{$stx2}' and c.wr_50 like '%{$stx7}%'
         group by a.mb_name ORDER BY totb DESC
        ";
        $result = sql_query($sql, TRUE);
        while ($row = sql_fetch_array($result)) {
        $list_nameb = explode('||',$row['wr_12b']);
        $numb = array_count_values($list_nameb);
        $list_namec = explode('||',$row['wr_12c']);
        $numc = array_count_values($list_namec);
        $sum_count ++
        ?>
        <tr>
            <td class="td_c"><?php echo $sum_count ?>위 </td>
            <td class="td_c"><?php echo $row['mb_name'] ?></td>
            <td class="td sne_td">
            <?php foreach( $numb as $key => $value ){ ?>
            <?php echo $key; ?>(<?php echo $value?>),
            <?php } ?></td>
            <td class="td_c"><?php echo $row['b_name'] ?></td>
            <td class="td_r"><?php echo number_format(str_replace(',', '', $row['totb']) / 2) ?>원</td>
            <td class="td sne_td">
            <?php foreach( $numc as $key => $value ){ ?>
            <?php echo $key; ?>(<?php echo $value?>),
            <?php } ?>
            </td>
            <td class="td_c"><?php echo $row['c_name'] ?></td>
            <td class="td_r"><?php echo number_format(str_replace(',', '', $row['totc']) / 2) ?>원</td>
            <td class="td_r"><?php echo $row['rat'] ?> %</td>
        </tr>
        <?php } ?>

위 이미지는 해당 소스를 사용하여 뿌려주고 있습니다.

여기에서 판매고객별로 나누기 위해 고객명을 추가했습니다. (c.wr_9)


        <?
        $sum_count = "0";
        $sql = "
        select a.mb_name, b.mb_id, c.mb_id, count(*) as cnt,
        group_concat(DISTINCT b.wr_12 separator '||') wr_12b,
        group_concat(DISTINCT c.wr_12 separator '||') wr_12c,
        count(DISTINCT b.wr_31) as b_name,
        count(DISTINCT c.wr_31) as c_name,
        sum(DISTINCT b.wr_31) as totb,
        sum(DISTINCT c.wr_31) as totc,
        c.wr_9,
        ROUND((sum(DISTINCT c.wr_31) / sum(DISTINCT b.wr_31)) * 100, 1) as rat
          from g5_member a
                   inner join g5_write_csorder b on a.mb_id = b.mb_id
                   inner join g5_write_cscenter c on b.mb_id = c.mb_id
         where
               b.wr_29 between '{$stx1}' and '{$stx2}' and b.wr_50 like '%{$stx7}%' and NOT b.wr_54 IN ('exchange') and NOT b.wr_7 IN ('4') and
               c.wr_29 between '{$stx1}' and '{$stx2}' and c.wr_50 like '%{$stx7}%'
         group by a.mb_name, c.wr_9 ORDER BY totb DESC
        ";
        $result = sql_query($sql, TRUE);
        while ($row = sql_fetch_array($result)) {
        $list_nameb = explode('||',$row['wr_12b']);
        $numb = array_count_values($list_nameb);
        $list_namec = explode('||',$row['wr_12c']);
        $numc = array_count_values($list_namec);
        $sum_count ++
        ?>
        <tr>
            <td class="td_c"><?php echo $sum_count ?>위 </td>
            <td class="td_c"><?php echo $row['mb_name'] ?></td>
            <td class="td sne_td">
            <?php foreach( $numb as $key => $value ){ ?>
            <?php echo $key; ?>(<?php echo $value?>),
            <?php } ?></td>
            <td class="td_c"><?php echo $row['b_name'] ?></td>
            <td class="td_r"><?php echo number_format(str_replace(',', '', $row['totb']) / 2) ?>원</td>
            <td class="td sne_td">
            <?php foreach( $numc as $key => $value ){ ?>
            <?php echo $key; ?>(<?php echo $value?>),
            <?php } ?>
            </td>
            <td><?php echo $row['wr_9']?></td>
            <td class="td_c"><?php echo $row['c_name'] ?></td>
            <td class="td_r"><?php echo number_format(str_replace(',', '', $row['totc']) / 2) ?>원</td>
            <td class="td_r"><?php echo $row['rat'] ?> %</td>
        </tr>
        <?php } ?>

해서 select에 c.wr_9를 추가하고 group by 에 c.wr_9를 추가해서 두번째 이미지처럼 만들었습니다...

여기서 제가 하고 싶은건..

아래 이미지(포토샵으로 합친 이미지입니다.)와 같이 순위, 총판장명, 구입품목, 구매횟수, 총 구매금액을 묶고 싶은데.. 어떻게 해야 할지 모르겠습니다.

구매 품목과 판매 품목은 다를수 있기 때문에 크게 신경쓰지 않아도 되는 부분입니다.

더불어.. 추운날씨에 감기 조심하시기 바랍니다..

#그누보드5 #php #sql #group #select

답변 2

엑스엠엘 자기소개 회원게시물 회원 질문검색 회원 답변검색 회원 댓글검색 님의 답변

2021-12-09 17:39:04 207.♡.♡.49

        $result = sql_query($sql, TRUE);
        $td_row= array();
        while ($row = sql_fetch_array($result))
          if( isset( $$td_row[ $row['mb_id']]) $td_row[ $row['mb_id']]++;

else $td_row[ $row['mb_id']]=1;

sql_data_seek( $result);

$pre_id='';

while ($row = sql_fetch_array($result)) {

if( $pre_id != $row['mb_id']) {

        ?><tr>
            <td class="td_c"  rowspan="<?=$td_row[ $row['mb_id']]?>" ><?php echo $sum_count ?>위 </td>
구매금액까지 rowspan 추가
            <td class="td_c"><?php echo $row['mb_name'] ?></td>
            <td class="td sne_td">
            <?php foreach( $numb as $key => $value ){ ?>
            <?php echo $key; ?>(<?php echo $value?>),
            <?php } ?></td>
            <td class="td_c"><?php echo $row['b_name'] ?></td>
            <td class="td_r"><?php echo number_format(str_replace(',', '', $row['totb']) / 2) ?>원</td>
            <td class="td sne_td">
            <?php foreach( $numc as $key => $value ){ ?>
            <?php echo $key; ?>(<?php echo $value?>),
            <?php } ?>
            </td>
            <td><?php echo $row['wr_9']?></td>
            <td class="td_c"><?php echo $row['c_name'] ?></td>
            <td class="td_r"><?php echo number_format(str_replace(',', '', $row['totc']) / 2) ?>원</td>
            <td class="td_r"><?php echo $row['rat'] ?> %</td>
        </tr><?php

} else {

?><tr>

<td class="td sne_td"> <?php foreach( $numc as $key => $value ){ ?> <?php echo $key; ?>(<?php echo $value?>), <?php } ?> </td> <td><?php echo $row['wr_9']?></td> <td class="td_c"><?php echo $row['c_name'] ?></td> <td class="td_r"><?php echo number_format(str_replace(',', '', $row['totc']) / 2) ?>원</td> <td class="td_r"><?php echo $row['rat'] ?> %</td> </tr><?php

}

이런 식입니다. 참고하세요.