sql 질문입니다..(카운트세기) 채택완료
</p><p><?</p><p>$query = "SELECT * FROM g5_board_new where dateco_id != '' and dateco_id != 'epro' and dateco_id != 'admin' ORDER BY wr_id desc";</p><p>$sql = sql_query($query);</p><p>$state_1 = sql_fetch(" select count(*) as cnt from {$g5['board_new_table']} where addarea='작업지시' ");</p><p>
</p><p>?></p><p><style type="text/css"></p><p>
</p><p>*{</p><p>padding:0;</p><p>margin:0;</p><p>}</p><p>
</p><p>table, tr, td{ border: 1px solid #ddd;</p><p>
</p><p>}</p><p>
</p><p></style></p><p>
</p><p><span style="white-space:pre"> </span><table class="table table-bordered"></p><p><span style="white-space:pre"> </span><tr></p><p><span style="white-space:pre"> </span><th >아이디</th></p><p><span style="white-space:pre"> </span><th >작업지시</th></p><p><span style="white-space:pre"> </span><th >승인대기</th></p><p><span style="white-space:pre"> </span><th >승인완료</th></p><p><span style="white-space:pre"> </span><th >승인거절</th></p><p><span style="white-space:pre"> </span><th >작업완료</th></p><p><span style="white-space:pre"> </span><th >기간만료</th></p><p><span style="white-space:pre"> </span></tr></p><p><span style="white-space:pre"> </span><?php while($row= sql_fetch_array($sql)) { ?> </p><p><span style="white-space:pre"> </span><tr> </p><p><span style="white-space:pre"> </span><td><?php echo $row["dateco_id"]; ?></td> </p><p><span style="white-space:pre"> </span><td> <?php echo $state_1['cnt']?></td> </p><p><span style="white-space:pre"> </span></tr> <?php } ?> </p><p>
</p><p><span style="white-space:pre"> </span></table></p><p>
질문은 이러합니다.
g5_board_new에 있는 아이디를 뿌리고, 그 아이디와 연결된 단어들의 숫자를 같이 뿌리고 싶습니다.
예)
아이디 |
작업지시 |
승인대기 |
승인완료 |
홍길동 |
10 |
0 |
5 |
장길산 |
10 |
1 |
4 |
임꺽정 |
5 |
20 |
9 |
이런식으로 해당아이디와 연결되는 단어의 숫자를 세고 싶습니다.
위 코드를 작성하면 g5_board_new에 있는 아이디는 정상적으로 뿌려집니다.
근데 그 아이디에 연결되는 단어의 숫자를 어떻게 뿌려야할지 감이 안잡혀서요,,,
혹시 도움을 구할 수 잇을까요?
아님 불가능한가요?
답변 4개
이런건 제작의뢰에 가까운 내용이지만.. 열정을 봐서
</span></p><p><span style="font-family: Verdana;"><?php</span></p><p><span style="font-size: 11pt; font-family: Verdana;">$query = "SELECT </span></p><p><span style="font-size: 11pt; font-family: Verdana;"> x.dateco_id, </span></p><p style="font-size: 14.6667px;"><span style="font-size: 11pt; font-family: Verdana;"> (select count(*) from </span><span style="font-family: Verdana; font-size: 14.6667px;">g5_board_new a </span></p><p style="font-size: 14.6667px;"><span style="font-family: Verdana; font-size: 14.6667px;"> where a.dateco_id = x.dateco_id and </span><span style="font-family: Verdana; font-size: 14.6667px;">addarea='작업지시') workcnt, </span></p><p style="font-size: 14.6667px;"><span style="font-family: Verdana; font-size: 14.6667px;">
</span></p><p style="font-size: 14.6667px;"><span style="font-size: 11pt; font-family: Verdana;"> (select count(*) from </span><span style="font-family: Verdana; font-size: 14.6667px;">g5_board_new a </span></p><p style="font-size: 14.6667px;"><span style="font-family: Verdana; font-size: 14.6667px;"> where a.dateco_id = x.dateco_id and </span><span style="font-family: Verdana; font-size: 14.6667px;">addarea='승인대기') staycnt, </span></p><p style="font-size: 14.6667px;"><span style="font-family: Verdana; font-size: 14.6667px;">
</span></p><p style="font-size: 14.6667px;"><span style="font-size: 11pt; font-family: Verdana;"> (select count(*) from </span><span style="font-family: Verdana; font-size: 14.6667px;">g5_board_new a </span></p><p style="font-size: 14.6667px;"><span style="font-family: Verdana; font-size: 14.6667px;"> where a.dateco_id = x.dateco_id and </span><span style="font-family: Verdana; font-size: 14.6667px;">addarea='승인완료') endcnt</span></p><p style="font-size: 14.6667px;"><span style="font-family: Verdana; font-size: 14.6667px;"> FROM (select distinct dateco_id </span><span style="font-family: Verdana; font-size: 11pt;">g5_board_new </span></p><p style="font-size: 14.6667px;"><span style="font-family: Verdana; font-size: 11pt;"> where dateco_id != '' and dateco_id != 'epro' </span></p><p style="font-size: 14.6667px;"><span style="font-family: Verdana; font-size: 11pt;"> and dateco_id != 'admin' ORDER BY wr_id desc) x";</span></p><pre><span style="font-family: Verdana;">$sql = sql_query($query);
</span></pre><pre><span style="font-family: Verdana;">
</span></pre><pre>?>
<style type="text/css">
*{
padding:0;
margin:0;
}
table, tr, td{ border: 1px solid #ddd;
}
</style>
<table class="table table-bordered">
<tr>
<th >아이디</th>
<th >작업지시</th>
<th >승인대기</th>
<th >승인완료</th></pre><pre><!--
<th >승인거절</th>
<th >작업완료</th>
<th >기간만료</th></pre><pre>-->
</tr>
<?php while($row= sql_fetch_array($sql)) { ?>
<tr>
<td><?php echo $row["dateco_id"]; ?></td>
<pre style="font-size: 14.6667px;"> <td> <?php echo $row['<span style="font-family: Verdana; font-size: 14.6667px; white-space: normal;">workcnt</span>']?></td>
</pre><pre style="font-size: 14.6667px;"> <td> <?php echo $row['<span style="font-family: Verdana; font-size: 14.6667px; white-space: normal;">staycnt</span>']?></td>
</pre><pre style="font-size: 14.6667px;"> <td> <?php echo $row['<span style="font-family: Verdana; font-size: 14.6667px; white-space: normal;">endcnt</span>']?></td>
</pre> </tr> <?php } ?>
</table></pre><pre><span style="font-family: Verdana;">
하시면원하는 값들을 사용하실수있습니다.답변에 대한 댓글 3개
동일한 구조로
방문자의 카운트를 확인하는걸 보여드리면 무슨차이가 있는지 직접 확인가능하실겁니다.
[code]
select vi_ip,
(select count(a.vi_id) from g5_visit a where a.vi_date = curdate() and x.vi_ip = a.vi_ip) today,
(select count(a.vi_id) from g5_visit a where a.vi_date between '2017-01-01' and curdate() and x.vi_ip = a.vi_ip) toyear
from (select distinct vi_ip from g5_visit ) x
[/code]
이 코드가 보이시면 위의 내용이 정상인지 확인 가능하지 않을까요?
"select dateco_id,
(select count(a.dateco_id) from g5_board_new a where a.dateco_id = x.dateco_id and addarea='작업지시') cnt_1,
(select count(a.dateco_id) from g5_board_new a where a.dateco_id = x.dateco_id and addarea='승인대기') cnt_2
from (select distinct dateco_id from g5_board_new where dateco_id != '' and dateco_id != 'epro' and dateco_id != 'admin' ORDER BY wr_id desc) x ";
[/code]
이렇게 하니까 되네요!
조언 감사합니다.
댓글을 작성하려면 로그인이 필요합니다.
이렇게 쿼리 하시면 되는거 아닌가요?
select dateco_id
, (select count(*) from g5_board_new in where in.dateco_id = out.date_co_id and in.addarea='작업지시') as count_1
, (select count(*) from g5_board_new in where in.dateco_id = out.date_co_id and in.addarea='승인대기') as count_2, (select count(*) from g5_board_new in where in.dateco_id = out.date_co_id and in.addarea='승인완료') as count_3
from g5_board_new out
답변에 대한 댓글 8개
$query = "위에 쓴 쿼리
where dateco_id != '' and dateco_id != 'epro' and dateco_id != 'admin' ORDER BY wr_id desc";
$sql = sql_query($query);
...
<td><?php echo $row["dateco_id"]; ?></td>
<td><?php echo $row['count_1']?></td>
<td><?php echo $row['count_2']?></td>
<td><?php echo $row['count_3']?></td>
[/code]
$query = "select dateco_id
, (select count(*) from g5_board_new in where in.dateco_id = out.date_co_id and in.addarea='작업지시') as count_1
, (select count(*) from g5_board_new in where in.dateco_id = out.date_co_id and in.addarea='승인대기') as count_2
, (select count(*) from g5_board_new in where in.dateco_id = out.date_co_id and in.addarea='승인완료') as count_3
, (select count(*) from g5_board_new in where in.dateco_id = out.date_co_id and in.addarea='승인거절') as count_4
, (select count(*) from g5_board_new in where in.dateco_id = out.date_co_id and in.addarea='작업완료') as count_5
, (select count(*) from g5_board_new in where in.dateco_id = out.date_co_id and in.addarea='기간만료') as count_6
from g5_board_new out where dateco_id != '' and dateco_id != 'epro' and dateco_id != 'admin' ORDER BY wr_id desc";
$sql = sql_query($query);
<?php
$cnt=0;
while($row= sql_fetch_array($sql)) {
?>
<tr>
<td><?php echo $row['dateco_id']; ?></td>
<td><?php echo $row['count_1']?></td>
<td><?php echo $row['count_2']?></td>
<td><?php echo $row['count_3']?></td>
<td><?php echo $row['count_4']?></td>
<td><?php echo $row['count_5']?></td>
<td><?php echo $row['count_6']?></td>
</tr>
<?php $cnt++; } ?>
[/code]
아무것도 안나오네요...
카운트를 하게되면
중복데이타가 나오게 됩니다.
@플래토 님 말씀처럼 오류는 좀 있네요 ^^
댓글을 작성하려면 로그인이 필요합니다.
답변을 작성하려면 로그인이 필요합니다.
로그인
근데 ㅠㅠ 아무것도 나오지 않네요..