加载中…
加载中…
标签：
佛学it |
分类： 工作学习 |
excel如何把有规律的单行或单列数据做成表格
例如有以下一行数据，每8列重复一次，但是内容不相同，如何把他们做成表格呢。
内江 5 0 -100% 7 2 0 0 攀枝花 4 3 -77% 10 6 3 1 乐山 11 10 -63% 27 16 10 1 广元 7 8 -61% 17 10 8 1 巴中 36 23 -59% 84 48 22 1.05 资阳 6 5 -47% 13 7 4 1.25 眉山 5 5 -29% 8 3 5 1 南充 11 17 -18% 23 12 14 1.21 雅安 9 30 -15% 45 36 24 1.25 成都 30 54 -10% 50 20 47 1.15 遂宁 11 24 -3% 21 10 23 1.04 泸州 10 27 1% 53 43 26 1.04 宜宾 14 50 16% 44 30 40 1.25 广安 8 36 40% 31 23 34 1.06 自贡 5 17 41% 23 18 12 1.42 达州 11 44 42% 48 37 28 1.57 德阳 8 17 72% 9 1 5 3.4 绵阳 14 54 162% 25 11 33 1.64 阿坝 31 118 32% 97 66 85 1.39 甘孜 40 71 -26% 87 47 53 1.34 凉山 24 11 -77% 30 6 11 1 全省 300 624 -9% 754 454 487 1.28
因为数据时非常有规律的，所以，可以通单元格的行列号来引用单元格的值，出了标题，表格数据从第三行开始，因次可表示为：=INDIRECT(ADDRESS(2,(CELL("row",$A4)-4)*8++COLUMN(A4)))
公式就是这样，具体的单元格及数值可以自己根据情况调整。表格效果如下图：
同理可以把有规律的数据变成长列，如下把N*16的矩阵数据变成长列：
=INDIRECT(ADDRESS(TRUNC(CELL("row",A992)/16-61),IF(MOD(ROW(A1),16)=0,16,MOD(ROW(A1),16))))
A992表示我的长列数据充A992列开始往下排。
标签：
sql取最大值sql最小值it |
分类： 数据库 |
--1、Name相同ID最小的记录(推荐用1,2,3),方法3在SQl05时，效率高于1、2
方法1:
Select * from #T a where not exists(select 1 from #T where
Name=a.Name and ID
方法2:
select a.* from #T a join (select min(ID)ID,Name from #T group by
Name) b on a.Name=b.Name and a.ID=b.ID
方法3:
select * from #T a where ID=(select min(ID) from #T where
Name=a.Name)
方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID>=b.ID
group by a.ID,a.Name,a.Memo having count(1)=1
方法5:
select * from #T a group by ID,Name,Memo having ID=(select
min(ID)from #T where Name=a.Name)
方法6:
select * from #T a where (select count(1) from #T where Name=a.Name
and ID
方法7:
select * from #T a where ID=(select top 1 ID from #T where
Name=a.name order by ID)
方法8:
select * from #T a where ID!>all(select ID from #T where
Name=a.Name)
方法9:
select * from #T a where ID in(select min(ID) from #T group by
Name)
--SQL2005:
方法10:
select ID,Name,Memo from (select *,min(ID)over(partition by Name)
as MinID from #T a)T where ID=MinID
生成结果:
--2、Name相同ID最大的记录,与min相反:
方法1:
Select * from #T a where not exists(select 1 from #T where
Name=a.Name and ID>a.ID)
方法2:
select a.* from #T a join (select max(ID)ID,Name from #T group by
Name) b on a.Name=b.Name and a.ID=b.ID order by ID
方法3:
select * from #T a where ID=(select max(ID) from #T where
Name=a.Name) order by ID
方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID<=b.ID
group by a.ID,a.Name,a.Memo having count(1)=1
方法5:
select * from #T a group by ID,Name,Memo having ID=(select
max(ID)from #T where Name=a.Name)
方法6:
select * from #T a where (select count(1) from #T where Name=a.Name
and ID>a.ID)=0
方法7:
select * from #T a where ID=(select top 1 ID from #T where
Name=a.name order by ID desc)
方法8:
select * from #T a where ID!
方法9:
select * from #T a where ID in(select max(ID) from #T group by
Name)
--SQL2005:
方法10:
select ID,Name,Memo from (select *,max(ID)over(partition by Name)
as MinID from #T a)T where ID=MinID
生成结果2:
标签：
httppostwebclient()杂谈 |
分类： VB.NET编程 |
剛剛看到一段簡短的程式是使用vb.net 開發應用程式來對網站使用POST的方式傳送資料
Dim web As New System.Net.WebClient()
web.Headers.Add("Content-Type",
"application/x-www-form-urlencoded")
Dim d As Byte() =
System.Text.Encoding.ASCII.GetBytes("SEA
Dim res As Byte() = web.UploadData("http://www.baidu.com", "POST",
d)
msgbox(System.Text.Encoding.ASCII.GetStr
比起下面這種方法是簡單許多!
Dim httpResp As System.Net.HttpWebResponse
Dim httpUrl2 As New System.Uri("http://www.baidu.com")
Dim req As HttpWebRequest
req = CType(WebRequest.Create(httpUrl2), HttpWebRequest)
req.Method = "POST"
req.ContentType = "application/x-www-form-urlencoded"
Dim bytesData() As Byte =
Encoding.ASCII.GetBytes("SEA
req.ContentLength = bytesData.Length
Dim postStream As Stream = req.GetRequestStream()
postStream.Write(bytesData, 0, bytesData.Length)
postStream.Close()
Dim res As HttpWebResponse = CType(req.GetResponse(),
HttpWebResponse)
Dim reader As StreamReader = New
StreamReader(res.GetResponseStream,System.Text.Encoding.GetEncoding("BIG5"))
Dim respHTML As String = reader.ReadToEnd()
res.Close()
MsgBox(respHTML)
