sql 질문... 드립니다.
본문
<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를 추가해서 두번째 이미지처럼 만들었습니다...
여기서 제가 하고 싶은건..
아래 이미지(포토샵으로 합친 이미지입니다.)와 같이 순위, 총판장명, 구입품목, 구매횟수, 총 구매금액을 묶고 싶은데.. 어떻게 해야 할지 모르겠습니다.
구매 품목과 판매 품목은 다를수 있기 때문에 크게 신경쓰지 않아도 되는 부분입니다.
더불어.. 추운날씨에 감기 조심하시기 바랍니다..
답변 2
$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
}
}
이런 식입니다. 참고하세요.
loop를 두 번 돌려서
처음에는 총판장 별로 row(tr 갯수)를 계산하고
둘째 loop에서 td에 rows를 지정해 주면 될 듯합니다.
조금만 더 힌트를 주시면 안될까요?
지금 이것저것 해보고 있는데.. 잘 안되서 질문드립니다.
답변을 작성하시기 전에 로그인 해주세요.
