2006年12月28日
#
SELECT
YEAR(BirthDate),
ROW_NUMBER() OVER(ORDER BY YEAR(BirthDate)) AS 'RowNumber',/* 按年产生一个唯一的序号 */
ROW_NUMBER() OVER(PARTITION BY YEAR(BirthDate) ORDER BY BirthDate) AS RowNumberPartition, /* 每年产生一个唯一的序列 */
RANK() OVER(ORDER BY YEAR(BirthDate)) AS 'Rank', /* 产生一个非紧密排名 */
DENSE_RANK() OVER(ORDER BY YEAR(BirthDate)) AS 'Dense_Rank', /* 产生一个紧密排名 */
NTILE(10) OVER(ORDER BY BirthDate DESC) AS 'ntile' /* 将结果分成10个组 */
FROM HumanResources.Employee
ORDER BY BirthDate
看看新的排序函数如何解决SQL server 2000中不方便解决的问题
--按BirthDate排序，取第10条到20条的数据 (这一定是最激动人心的新特性，哈哈)
SELECT BirthDate FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY BirthDate) AS a,BirthDate
FROM HumanResources.Employee
) AS a
WHERE a BETWEEN 10 AND 20
--将数据分成十份，取第三份
SELECT
BirthDate
FROM
(
SELECT
NTILE(10) OVER(ORDER BY BirthDate DESC) AS a,BirthDate
FROM HumanResources.Employee
) AS a
WHERE a = 3
再来看看如何用新的排序函数解决以前在SQL server 2000中的问题
-- 出生的员工最多的一年出生多少员工（有点）
/* SQL server 2000 */
SELECT MAX(a)
FROM
(
SELECT COUNT(EmployeeID) AS a FROM HumanResources.Employee GROUP BY YEAR(BirthDate)
) AS a
/* SQL server 2005 */
SELECT MAX(a)
FROM
(
SELECT
ROW_NUMBER() OVER(PARTITION BY YEAR(BirthDate) ORDER BY BirthDate) AS a
FROM HumanResources.Employee
) AS a
-- 出生日期涵盖了多少年
/* SQL server 2000 */
SELECT COUNT(DISTINCT YEAR(BirthDate)) FROM HumanResources.Employee
/* SQL server 2005 */
SELECT MAX(a)
FROM
(
SELECT
DENSE_RANK() OVER(ORDER BY YEAR(BirthDate)) AS a
FROM HumanResources.Employee
) AS a
-- 显示前10%的数据
/* SQL server 2000 */
SELECT TOP 10 Percent BirthDate FROM HumanResources.Employee
/* SQL server 2005 */
SELECT
BirthDate
FROM
(
SELECT
NTILE(10) OVER(ORDER BY BirthDate DESC) AS a,BirthDate
FROM HumanResources.Employee
) AS a
WHERE a = 1
2006年10月22日
#
2006年10月20日
#
1. 行列转换--普通
假设有张学生成绩表(CJ)如下
Name Subject Result
张三 语文 80
张三 数学 90
张三 物理 85
李四 语文 85
李四 数学 92
李四 物理 82
想变成
姓名 语文 数学 物理
张三 80 90 85
李四 85 92 82
declare @sql varchar(4000)
set @sql = 'select Name'
select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
from (select distinct Subject from CJ) as a
select @sql = @sql+' from CJ group by name'
select @sql
exec(@sql)
2. 行列转换--合并
有表A,
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1
创建一个合并的函数
alter function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from test where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go
--调用自定义函数得到结果
select id,dbo.fmerg(id) from test
2006年10月18日
#
<html>
<head>
<title>
test
</title>
<script language="javascript">
//var select1_len = document.frm.s1.options.length;
var select2 = new Array(4);
for (i=0; i<4; i++)
{
select2[i] = new Array();
}
//定义基本选项
select2[0][0] = new Option("请选择", " ");
select2[1][0] = new Option("PHP", " ");
select2[1][1] = new Option("ASP", " ");
select2[1][2] = new Option("JSP", " ");
select2[2][0] = new Option("C/C++", " ");
select2[2][1] = new Option("Java", " ");
select2[2][2] = new Option("C#", " ");
select2[3][0] = new Option("Perl", " ");
select2[3][1] = new Option("Ruby", " ");
select2[3][2] = new Option("Python", " ");
function redirec(x)
{
var temp = document.frm.s2;
for (i=0;i<select2[x].length;i++)
{
temp.options[i]=new Option(select2[x][i].text,select2[x][i].value);
}
temp.options[0].selected=true;
}
</script>
</head>
<body>
<form name="frm">
<select name="s1" onChange="redirec(this.options.selectedIndex)">
<option selected>请选择</option>
<option value="1">脚本语言</option>
<option value="2">高级语言</option>
<option value="3">其他语言</option>
</select>
<select name="s2">
<option value="请选择" selected>请选择</option>
</select>
</form>
</body>
</html>
/**//// <summary>
/// 经过处理获取所略图
/// </summary>
/// <param name="ms"></param>
/// <param name="height"></param>
/// <returns></returns> private byte[] LessonPicAutoWidth(MemoryStream ms, int height)
{
byte[] ChagedByte;
System.Drawing.Image OriginalImage = System.Drawing.Image.FromStream(ms, true);
System.Drawing.Bitmap OriginalPic, NewPic;
OriginalPic = new System.Drawing.Bitmap(OriginalImage);
int Width = height * OriginalPic.Width / OriginalPic.Height;
if (Width > 280)
{
Width = 280;
}
NewPic = new Bitmap(OriginalPic, Width, height);
MemoryStream Newms = new MemoryStream();
NewPic.Save(Newms, System.Drawing.Imaging.ImageFormat.Jpeg);
ChagedByte = Newms.GetBuffer();
OriginalPic.Dispose();
NewPic.Dispose();
Newms.Close();
return ChagedByte;
}
/**//// <summary>
/// 显示图片
/// </summary>
/// <param name="EmployeeID"></param> public void GetPhoto(string EmployeeID, int height)
{
Bitmap bmp;
byte[] PhotoByte;
string cnstr;
string sql;
cnstr = "Data Source=localhost;Database=Northwind;uid=sa;pwd=123";
sql = "Select Photo From Employees Where EmplyeeID=" + EmployeeID;
using (SqlConnection cn = new SqlConnection(cnstr))
{
using (SqlCommand cmd = cn.CreateCommand())
{
cn.Open();
cmd.CommandText = sql;
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
reader.Read();
PhotoByte = (byte[])reader.GetValue(0);
}
}
}
if (PhotoByte == null || PhotoByte.Length == 0) return;
MemoryStream tempStream = new MemoryStream(PhotoByte);
PhotoByte = LessonPicAutoWidth(tempStream, height);
bmp = new Bitmap(tempStream);
Response.ContentType = "image/gif";
bmp.Save(Response.OutputStream, System.Drawing.Imaging.ImageFormat.Gif);
Response.End();
}
//页面源代码
<form id="form1" runat="server">
<div style="text-align: center">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None" DataSourceID="EmployeeList">
<Columns>
<asp:CommandField ShowSelectButton="True" />
<asp:CommandField ShowEditButton="True" />
<asp:CommandField ShowDeleteButton="True" />
<asp:BoundField DataField="EmployeeID" HeaderText="雇员ID号" />
<asp:BoundField DataField="Name" HeaderText="雇员姓名" />
<asp:TemplateField HeaderText="BirthDate出生日期" SortExpression="BirthDate">
<EditItemTemplate>
<asp:Calendar ID="Calendar1" runat="server" BackColor="#FFFFCC" BorderColor="#FFCC66"
BorderWidth="1px" DayNameFormat="Shortest" Font-Names="Verdana" Font-Size="8pt"
ForeColor="#663399" Height="200px" SelectedDate='<%# Bind("BirthDate") %>' ShowGridLines="True"
VisibleDate='<%# Eval("BirthDate") %>' Width="220px">
<SelectedDayStyle BackColor="#CCCCFF" Font-Bold="True" />
<TodayDayStyle BackColor="#FFCC66" ForeColor="White" />
<SelectorStyle BackColor="#FFCC66" />
<OtherMonthDayStyle ForeColor="#CC9966" />
<NextPrevStyle Font-Size="9pt" ForeColor="#FFFFCC" />
<DayHeaderStyle BackColor="#FFCC66" Font-Bold="True" Height="1px" />
<TitleStyle BackColor="#990000" Font-Bold="True" Font-Size="9pt" ForeColor="#FFFFCC" />
</asp:Calendar>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("BirthDate", "{0:d}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="上司">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="ReportsToList"
DataTextField="Name" DataValueField="EmployeeID" SelectedValue='<%# Bind("Commander") %>'>
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# GetReportsToName(Eval("Commander")) %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<EditRowStyle BackColor="#999999" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
</div>
<asp:SqlDataSource ID="EmployeeList" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT EmployeeID, LastName + ',' + FirstName AS Name, BirthDate, ReportsTo AS Commander FROM Employees">
</asp:SqlDataSource>
<asp:SqlDataSource ID="ReportsToList" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="Select EmployeeID,LastName+','+FirstName as Name From Employees Union Select Null,'无' Order by Name ASC">
</asp:SqlDataSource>
</form>
//绑定第一个模板列的Label1控件，将EmployeeID转换为姓名
public string GetReportsToName(object reportsToId)
{
foreach (DataRowView row in ReportsToList.Select(DataSourceSelectArguments.Empty))
{
if (reportsToId.Equals(row["EmployeeID"]))
{
return row["Name"].ToString();
}
}
throw new ArgumentException();
}
2006年10月11日
#
1.GrdView命令列弹出对话框
<asp:GridView ID="GridView1" runat="server" OnRowCommand="GridView1_RowCommand">
<Columns>
<asp:ButtonField CommandName="编辑" Text="编辑" />
<asp:ButtonField CommandName="删除" Text="<div onclick="return confirm('您确实要删除该项目吗？')">删除</div>" />
</Columns>
</asp:GridView>
2.使GridView控件选中列与编辑行合为一行
在RowEditing事件下下写如下代码：
GridView1.SelectedIndex = e.NewEditIndex
3.向动态增加控件页面
◎先new一个控件；◎Control frm = this.FindControl("form1")；◎frm.Controls.Add(控件ID)
http://java.mblogger.cn/edgar001
2006年9月28日
#
摘要: 昨天面试打击很大，我几乎彻夜未眠。辗转反侧中我反复在问自己这样一个问题：我的能力（）A.很弱；B.比较弱；C.比较强；D.很强。良久我都没能找到答案，在排除了C、D之后，我一直倾向于选A。终于在天快亮的时候我找到了答案！不是A，也不是B，我选D，我有很多理由选D。昨天骂自己的话太狠了，今天收回。首先，在众多学生当中，真正能像我们这样在学校就真枪实弹的做过项目的少之又少。很多人在简历里吹自己会这个会...
阅读全文
2006年9月27日
#
摘要: 做完手头这个项目，自以为自己很了不起了。会几个存储过程简历上号称自己精通Sql Server；能写几行粗糙的代码就号称自己对C＃有很深的研究！做完项目也没有好好想一下怎么去好好优化一下代码，怎么去实现会更好！看到这个也会，那个也懂！实际上就懂那么点皮毛！今天一面试让人给问傻了！悲哀！不要忘记了高中那毁掉你一生的罪魁祸首——浮躁！脚踏实地，不骄不躁，自强不息，方能展翅鹏飞！
阅读全文
2006年8月30日
#
摘要: 1.如何取得一个数据表的所有列名方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。SQL语句如下：declare@objidint,@objnamechar(40)set@objname='tablename'select@objid=idfromsysobjectswhereid=object_id(@objname)...
阅读全文