sql 질문... 드립니다. 채택완료
밀랍
3년 전
조회 2,346
</p>
<p> <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 } ?></p>
<p>위 이미지는 해당 소스를 사용하여 뿌려주고 있습니다.
여기에서 판매고객별로 나누기 위해 고객명을 추가했습니다. (c.wr_9)
</p>
<p> <?
$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 } ?></p>
<p>
해서 select에 c.wr_9를 추가하고 group by 에 c.wr_9를 추가해서 두번째 이미지처럼 만들었습니다...
여기서 제가 하고 싶은건..
아래 이미지(포토샵으로 합친 이미지입니다.)와 같이 순위, 총판장명, 구입품목, 구매횟수, 총 구매금액을 묶고 싶은데.. 어떻게 해야 할지 모르겠습니다.
구매 품목과 판매 품목은 다를수 있기 때문에 크게 신경쓰지 않아도 되는 부분입니다.
더불어.. 추운날씨에 감기 조심하시기 바랍니다..
댓글을 작성하려면 로그인이 필요합니다.
답변 2개
채택된 답변
+20 포인트
3년 전
$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
}
}
이런 식입니다. 참고하세요.
로그인 후 평가할 수 있습니다
댓글을 작성하려면 로그인이 필요합니다.
답변을 작성하려면 로그인이 필요합니다.
로그인
지금 이것저것 해보고 있는데.. 잘 안되서 질문드립니다.