关于经常利用取余数来获得分组的总结

来源：互联网发布：ppt数据图表双轴显示编辑：程序博客网时间：2024/05/17 08:48

var table,len;

window.onload=init;

function init(){

table=document.getElementById("t1");

}

function addRow(){

pageDiv=document.getElementById("page");

len=table.rows.length;

pagesize=parseInt(len/10);

trunToPage(pagesize);

if (len%10==0){ //就想提一点就是取余数的用法，总结：就是向分页这样的情况，以及sql牵涉到group的情况，诸多情况都需要用取余数来分组，今天做了一个sqll想了半天才意识到哎，做了那么长时间把这个个忘了，情况如下

var aLink=document.createElement("<A>");

aLink.innerText=pagesize+1;

pageDiv.appendChild(aLink);

var aLen=pageDiv.getElementsByTagName("A").length;

for(var i=0;i<aLen;i++){

pageDiv.getElementsByTagName("A")[i].href="#";

pageDiv.getElementsByTagName("A")[i].onclick=function(){trunToPage1(this.innerText)}

}

var tdObj1=document.createElement("<TD>");

tdObj1.width="52";

tdObj1.className="UCCA";

tdObj1.align="center";

tdObj1.innerText=len+1;

var tdObj2=document.createElement("<TD>");

tdObj2.innerText="NO URL";

tdObj2.width="659";

tdObj2.className="UCCD";

var checkbox=document.createElement("<INPUT>");

checkbox.name="url";

checkbox.type="checkbox";

checkbox.value="#";

checkbox.onclick=function(){alert(this.name)}

var td3A=document.createElement("<A>");

td3A.href="http://www.outsourcexp.com/Index.aspx";

td3A.target="_blank";

td3A.appendChild(checkbox);

var td3div=document.createElement("<DIV>");

td3div.align="center";

td3div.className="UCCB";

td3div.appendChild(td3A);

var tdObj3=document.createElement("<TD>");

tdObj3.width="35";

tdObj3.className="UCCC";

tdObj3.appendChild(td3div);

var trObj=document.createElement("<TR>");

if(len%2==0){

trObj.style.backgroundColor="#FFFFFF";

}

else{

trObj.style.backgroundColor="#F5FAFA";

}

trObj.appendChild(tdObj1);

trObj.appendChild(tdObj2);

trObj.appendChild(tdObj3);

table.firstChild.appendChild(trObj);

}

function check(){

var j

j=0

check123=document.getElementsByTagName("INPUT")

for(i=0;i<check123.length;i++){

if (check123[i].name=="url"){

check123[i].checked=true;

}

function go_url(){

o1=document.getElementsByTagName("INPUT");

for (var i=0;i<o1.length;i++){

if(o1[i].type=="checkbox"){

if (o1[i].checked){

location.href=o1[i].value;

}

function trunToPage(){

if (len+1>pagesize*10){

for(i=0;i<pagesize*10;i++){

table.rows[i].style.display="none"

}

function trunToPage1(value){

len=table.rows.length;

for(i=0;i<len;i++){

table.rows[i].style.display="none"

}

for(i=(value-1)*10;i<Math.min(value*10,len);i++){

table.rows[i].style.display="block"

}

</script>

<html>

<head>

body {

background-color: #E6EAE9;

}

.UCCD{color: #4F6B72;font-weight: bold;text-indent:1em;}

.UCCA {color: #4F6B72; font-weight: bold;}

td{ height:20px;}

a:link {

color: #4F6B72;

text-decoration: none;

}

a:visited,a:hover,a:active {

text-decoration: none;

color: #4F6B72;

}

.UCCB {font-size: 12px; font-weight: bold; }

.UCCE{

color: #4F6B72;

font-weight: bold;

border-left: 1px #ffffff solid;

border-top: 1px #ffffff solid;

background-color: #D2EBEF;

font-size: 16px;

}

.UCCC{

color: #4F6B72;

font-weight: bold;

border: 1px #ffffff solid;

background-color: #D2EBEF;

font-size: 12px;

}

</style>

</head>

<body>

<table>

<tr>

</tr>

</table>

<tr>

</tr>

</table>

<td width="659" class="UCCD">http://www.outsourcexp.com/Index.aspx</td>

</tr>

<td class="UCCD">http://www.getafreelancer.com/</td>

</tr>

<td class="UCCD">http://www.scriptlance.com/</td>

</tr>

<td class="UCCD">http://www.getacoder.com/</td>

</tr>

</tr>

<td class="UCCD">http://www.elance.com/</td>

</tr>

<td class="UCCD">http://www.rentacoder.com/</td>

</tr>

<td class="UCCD">http://www.freelancerchina.com/</td>

</tr>

</tr>

</tr>

</table>

</form>

</body>

</html>

create table T([time] varchar(05),result varchar(10))

insert into T

select '00:15','success' union all

select '00:16','success' union all

select '00:25','fail' union all

select '00:35','fail' union all

select '00:36','success' union all

select '01:12','fail' union all

select '01:38','success' union all

select '03:16','success'

select convert(char(05),dateadd(minute,[time]*30,'00:00'),108)+'-'+convert(char(05),dateadd(minute,[time]*30,'00:30'),108) as [Time],

success,

fail

from

(

select datediff(minute,'00:00',[time])/30 as [time],

sum(case when result='success' then 1 else 0 end) as success,

sum(case when result='fail' then 1 else 0 end ) as fail

from T

group by datediff(minute,'00:00',[time])/30

) A

/**//*

Time success fail

----------- ----------- -----------

00:00-00:30 2 1

00:30-01:00 1 1

01:00-01:30 0 1

01:30-02:00 1 0

03:00-03:30 1 0

drop table T

--像这样的分组情况，需要用到取余数来分组