琐碎小事2（添加ing）-CSDN博客

1.distinct函数&日期转换string再截取&日期的拼接

distinct (a)函数：查出的数据根据列a过滤掉重复数据
查询字符串拼接
select distinct (CommUnId), cast ( year (MeterReadDate) as varchar ( 4 )) + ' 年 ' + cast ( month (MeterReadDate) as varchar ( 2 )) + ' 月 ' From
Pub_Meate where Year (MeterReadDate) >= ( SELECT DATEPART ( year , GETDATE ()) AS ' Month Number ' )
日期类型转换成string类型再截取
select distinct (CommUnId), SUBSTRING ( Convert ( varchar ( 10 ),MeterReadDate, 120 ), 1 , 7 ) From
Pub_Meate where Year (MeterReadDate) >= ( SELECT DATEPART ( year , GETDATE ()) AS ' Month Number ' )

2、取得最近日期，先插入再修改

insert into pub_MeterRead(PubMeterID,communID,LastReadDate,LastReadData)
select ID,CommUnId,MeterReadDate,CurMeterCnt From Pub_meate Where 1 = 1
And MeterTypeCode = ' 01 ' and not exists ( select PubMeterID from v_Pub_MeterRead where 1 = 1
And MeterTypeCode = ' 01 ' and PubMeterID = Pub_meate.id)

OnKeyPress = " event.returnValue=false; " // 文本框禁用键盘输入（可以复制粘贴）
ContentEditable = " false " // 文本框禁用输入（用鼠标可以复制不能粘贴）

3、日期控件只能选择本月日期

var TextBoxDate;
function CheckDate(txtDate) {debugger;
TextBoxDate = txtDate;
var date = txtDate.value;
PageMethods.ServerCheckDate(date, OnCheckDate);
}
function OnCheckDate(result) {debugger;
if (result == false ) {
TextBoxDate.value = "" ;
alert( ' 您只能选择本月日期 ' );
}
}
< td class = " tdtitle " >
读数日期：
</ td >
< td class = " tdcatag " >
< asp:TextBox ID = " TextBox2 " runat = " server " onchange = " CheckDate(this) " ></ asp:TextBox >
< cc1:CalendarExtender ID = " CalendarExtender2 " runat = " server " Enabled = " True "
Format = " yyyy-MM-dd " TargetControlID = " TextBox2 " >
</ cc1:CalendarExtender >
</ td >

[System.Web.Services.WebMethod]
public static bool ServerCheckDate( string date)
{
if (Common.PageValidate.StrToDate(date) == null )
{
return false ;
}
else
{
string str = string .Format( " {0:yyyy-MM} " , Common.PageValidate.StrToDate(date));
if (str == DateTime.Now.ToString( " yyyy-MM " ))
{
return true ;
}
}
return false ;
}

用法

<% @ Page Language = " C# " AutoEventWireup = " true " CodeBehind = " Formula.aspx.cs " Inherits = " BeidouWY.Web.ChargeManage.ChargeCS.Formula " %>

<% @ Register Assembly = " AjaxControlToolkit " Namespace = " AjaxControlToolkit " TagPrefix = " cc1 " %>
<! DOCTYPE html PUBLIC " -//W3C//DTD XHTML 1.0 Transitional//EN " " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd " >
< html xmlns = " http://www.w3.org/1999/xhtml " >
< head runat = " server " >
< title ></ title >
< link href = " http://www.cnblogs.com/Css/CommStyle.css " rel = " Stylesheet " type = " text/css " />

< script language = " javascript " type = " text/javascript " >
function ListClick(obj) {
var a = obj.value;
var t = document.getElementById( " TabContainer1_TabPanel2_TextBox1 " );
t.value = t.value + a;
}
function btnClick(obj) {
var a = obj.value;
var t = document.getElementById( " TabContainer1_TabPanel2_TextBox1 " );
t.value = t.value + a;
}
function Insert(o) {
var str = o.value;
var obj = document.getElementById( ' TabContainer1_TabPanel2_TextBox1 ' );
if (document.selection) {
obj.focus();
var sel = document.selection.createRange();
document.selection.empty();
sel.text = str;
} else {
var prefix, main, suffix;
prefix = obj.value.substring( 0 , obj.selectionStart);
main = obj.value.substring(obj.selectionStart, obj.selectionEnd);
suffix = obj.value.substring(obj.selectionEnd);
obj.value = prefix + str + suffix;
}
obj.focus();
}

function AAA(obj) {
alert(obj.value);
}

</ script >

< style type = " text/css " >
#Button9
{
width: 154px;
}
#Button8
{
width: 115px;
}
</ style >

</ head >
< body >
< form id = " form1 " runat = " server " >
< div >
< asp:ScriptManager ID = " ScriptManager1 " runat = " server " >
</ asp:ScriptManager >
< cc1:TabContainer ID = " TabContainer1 " runat = " server " ActiveTabIndex = " 1 " Height = " 400px "
Width = " 100% " >
< cc1:TabPanel runat = " server " HeaderText = " TabPanel1 " ID = " TabPanel1 " >
< HeaderTemplate >
< b > 快捷 </ b >
</ HeaderTemplate >
< ContentTemplate >
< asp:RadioButtonList runat = " server " ID = " RadList1 " >
< asp:ListItem >
公式自定义
</ asp:ListItem >
< asp:ListItem Value = " 费用金额=单价*建筑面积 " > 单价 * 建筑面积 </ asp:ListItem >
< asp:ListItem Value = " 费用金额=单价*使用面积 " > 单价 * 使用面积 </ asp:ListItem >

</ asp:RadioButtonList >

</ ContentTemplate >
</ cc1:TabPanel >
< cc1:TabPanel ID = " TabPanel2 " runat = " server " HeaderText = " TabPanel2 " >
< HeaderTemplate >
< b > 高级 </ b >
</ HeaderTemplate >
< ContentTemplate >
< table width = " 80% " >
< tr >
< td style = " width: 150px " >
< fieldset style = " width: 150px " >
< legend > 计算元素 </ legend >
< asp:ListBox runat = " server " ID = " ListBox1 " Rows = " 5 " Width = " 150px " onDblClick = " Insert(this) " >
< asp:ListItem Text = " 费用_标准单价 " ></ asp:ListItem >
< asp:ListItem Text = " 费用_计划内单价 " ></ asp:ListItem >
< asp:ListItem Text = " 费用_计划外单价 " ></ asp:ListItem >
< asp:ListItem Text = " 费用金额 " ></ asp:ListItem >
< asp:ListItem Text = " 房屋_建筑面积 " ></ asp:ListItem >
< asp:ListItem Text = " 房屋_使用面积 " ></ asp:ListItem >

</ asp:ListBox >
</ fieldset >
</ td >
< td rowspan = " 2 " valign = " top " >
< table width = " 100% " >
< tr >
< td >
< asp:TextBox ID = " TextBox1 " runat = " server " Height = " 90px " TextMode = " MultiLine " Width = " 441px " ></ asp:TextBox >
</ td >
</ tr >
< tr >
< td >
< input id = " Button2 " type = " button " value = " + " onclick = " Insert(this) " />< input id = " Button1 " type = " button "
value = " - " onclick = " Insert(this) " />< input id = " Button3 " type = " button " value = " * " onclick = " Insert(this) " />< input id = " Button4 "
type = " button " value = " / " onclick = " Insert(this) " />< input id = " Button5 " type = " button " value = " = " onclick = " Insert(this) " />< input
id = " Button6 " type = " button " value = " ( " onclick = " Insert(this) " />< input id = " Button7 " type = " button " value = " ) " onclick = " Insert(this) " />
< input ID = " Button8 " type = " button " value = " 如果…则…如果完 " onclick = " Insert(this) " />< input ID = " Button9 "
type = " button " value = " 如果…则…否则…如果完 " onclick = " Insert(this) " />< input ID = " Button10 " type = " button "
value = " 且 " onclick = " Insert(this) " />< input ID = " Button11 " type = " button " value = " 或 " onclick = " Insert(this) " /></ td >
</ tr >
< tr >
< td >
& nbsp; 注意事项：如果使用条件语句，必须把条件字段和条件值用单引号括起来 </ td >
</ tr >
</ table >
</ td >
</ tr >
< tr >
< td >
< fieldset style = " width: 150px " >
< legend > 条件元素 </ legend >
< asp:ListBox runat = " server " ID = " ListBox2 " Rows = " 5 " Width = " 150px " onDblClick = " Insert(this) " >

< asp:ListItem Text = " 房屋_是否空置 " ></ asp:ListItem >
< asp:ListItem Text = " 房屋_权属类型 " ></ asp:ListItem >
< asp:ListItem Text = " 房屋_用途 " ></ asp:ListItem >
</ asp:ListBox >
</ fieldset >
</ td >
</ tr >
</ table >
</ ContentTemplate >
</ cc1:TabPanel >
</ cc1:TabContainer >
</ div >

</ form >
</ body >
</ html >

UNION将两个查出来结构相同的表连接在一起

UNION指令的目的是将两个SQL语句的结果合并起来。从这个角度来看，我们会产生这样的感觉，UNION跟JOIN似乎有些许类似，因为这两个指令都可以由多个表格中撷取资料。 UNION的一个限制是两个 SQL 语句所产生的栏位需要是同样的资料种类。另外，当我们用 UNION这个指令时，我们只会看到不同的资料值 (类似 SELECT DISTINCT )。 union只是将两个结果联结起来一起显示，并不是联结两个表

　　 UNION 的语法如下： [ SQL 语句 1 ]

　　 UNION

　　 [ SQL 语句 2 ] 假设我们有以下的两个表格，

　　Store_Information 表格 store_name Sales Date

　　Los Angeles $ 1500 Jan - 05 - 1999

　　San Diego $ 250 Jan - 07 - 1999

　　Los Angeles $ 300 Jan - 08 - 1999

　　Boston $ 700 Jan - 08 - 1999

　　Internet Sales 表格 Date Sales

　　Jan - 07 - 1999 $ 250

　　Jan - 10 - 1999 $ 535

　　Jan - 11 - 1999 $ 320

　　Jan - 12 - 1999 $ 750

　　而我们要找出来所有有营业额 (sales) 的日子。要达到这个目的，我们用以下的 SQL 语句：

　　 SELECT Date FROM Store_Information

　　 UNION

　　 SELECT Date FROM Internet_Sales 结果:

　　Date

　　Jan - 05 - 1999

　　Jan - 07 - 1999

　　Jan - 08 - 1999

　　Jan - 10 - 1999

　　Jan - 11 - 1999

　　Jan - 12 - 1999

　　有一点值得注意的是，如果我们在任何一个 SQL 语句 (或是两句都一起) 用 " SELECT DISTINCT Date" 的话，那我们会得到完全一样的结果。

　　SQL Union All

　　 UNION ALL 这个指令的目的也是要将两个 SQL 语句的结果合并在一起。 UNION ALL 和 UNION 不同之处在于 UNION ALL 会将每一笔符合条件的资料都列出来，无论资料值有无重复。 UNION ALL 的语法如下： [ SQL 语句 1 ]

　　 UNION ALL

　　 [ SQL 语句 2 ] 我们用和上一页同样的例子来显示出 UNION ALL 和 UNION 的不同。同样假设我们有以下两个表格:

　　Store_Information 表格 store_name Sales Date

　　Los Angeles $ 1500 Jan - 05 - 1999

　　San Diego $ 250 Jan - 07 - 1999

　　Los Angeles $ 300 Jan - 08 - 1999

　　Boston $ 700 Jan - 08 - 1999

　　Internet Sales 表格 Date Sales

　　Jan - 07 - 1999 $ 250

　　Jan - 10 - 1999 $ 535

　　Jan - 11 - 1999 $ 320

　　Jan - 12 - 1999 $ 750

　　而我们要找出有店面营业额以及网络营业额的日子。要达到这个目的，我们用以下的SQL语句：

　　 SELECT Date FROM Store_Information

　　 UNION ALL

　　 SELECT Date FROM Internet_Sales 结果:

　　Date

　　Jan - 05 - 1999

　　Jan - 07 - 1999

　　Jan - 08 - 1999

　　Jan - 08 - 1999

　　Jan - 07 - 1999

　　Jan - 10 - 1999

　　Jan - 11 - 1999

　　Jan - 12 - 1999

　　 ============

　　表1

　　A B

　　a 1

　　b 0

　　c 3

　　d 0

　　e 2

　　表2

　　A B

　　c 0

　　e 4

　　合并两个表除去重复的数据（以表2的数据为主），我们将会得到以下的表:

　　A B

　　a 1

　　b 0

　　c 0

　　d 0

　　e 4

　　 select A,B from 表1 where A not in ( select A from 表2)

　　 union

　　 select A,B from 表2

eg:
select a.FolderConId,a.empid,a.depid,a.Adminflag,a.writeFlag,a.readflag,mrbaseinf.EmpName as EmpName from fmDocFolderCtr a,mrbaseinf
where folderid = @folderid and a.empid <> @empid and a.empid = mrbaseinf.empid and a.delflag = 0
union
select a.FolderConId,a.empid,a.depid,a.Adminflag,a.writeFlag,a.readflag,mrDepartment.DepName as EmpName from fmDocFolderCtr a,mrDepartment
where folderid = @folderid and a.depid = mrDepartment.depid and a.delflag = 0

select fileId,b.docid,b.fname,size,a.Typename as ftype,fpath,b.cdate,fmdoc.keyword as keyword
from fmfileAtt a,fmFileCont b,fmdoc
where b.docid = fmDoc.DocId and b.ftype = a.type and b.delflag = 0

关于存储过程输出参数的问题par1.Direction = ParameterDirection.InputOutput;

后台代码
protected string File( int FileId)
{
string FileN = "" ;
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand comm = new SqlCommand())
{
comm.Connection = conn;
comm.CommandText = " P_FildLoad " ;
comm.CommandType = CommandType.StoredProcedure;
SqlParameter par1 = new SqlParameter( " @FileN " , SqlDbType.NVarChar, 500 );
SqlParameter par2 = new SqlParameter( " @Fileid " , SqlDbType.Int);
par1.Direction = ParameterDirection.InputOutput; // 正确写法
// par1.Direction = ParameterDirection.Output 没有返回值
par1.Value = " " ;
par2.Value = FileId;
comm.Parameters.Add(par1);
comm.Parameters.Add(par2);
conn.Open();
comm.ExecuteNonQuery();
FileN = par1.Value.ToString();
}
}
return FileN;
}

存储过程：

CREATE PROCEDURE P_FildLoad
(
@Fileid int ,
@FileN nvarchar( 500 ) = ' ' OUTPUT
)
AS
DECLARE @Fil varchar( 20 );
DECLARE @Fileidnew int
SET @Fileidnew = @Fileid
WHILE( 1 = 1 )
BEGIN
SELECT @Fil = FolderName FROM OA_fmDocFolder WHERE ID = @Fileidnew
IF(isnull(@Fil, '' ) != '' )
BEGIN
SET @FileN = @Fil + ' / ' + @FileN
SET @Fil = NULL
SELECT @Fileidnew = ParentFolderId FROM OA_fmDocFolder WHERE ID = @Fileidnew
END
ELSE
BREAK
END
GO

代码

<% @ Page Language = " C# " AutoEventWireup = " true " CodeBehind = " LateFeesSuanFa.aspx.cs "
Inherits = " BeidouWY.Web.ChargeManage.ChargeCS.LateFeesSuanFa " %>

<% @ Register Assembly = " AjaxControlToolkit " Namespace = " AjaxControlToolkit " TagPrefix = " cc1 " %>
<! DOCTYPE html PUBLIC " -//W3C//DTD XHTML 1.0 Transitional//EN " " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd " >
< html xmlns = " http://www.w3.org/1999/xhtml " >
< head runat = " server " >
< title >< / title>
< link href = " /Css/CommStyle.css " rel = " Stylesheet " type = " text/css " / >

< script language = " javascript " type = " text/javascript " >
function Insert(o) {
var str = o.value;
var obj = document.getElementById( ' TextBox1 ' );
if (document.selection) { // 获取文本编辑对象(光标选中对象，只有IE支持)
obj.focus();
var sel = document.selection.createRange(); // 获取光标选中文本
document.selection.empty(); // 将光标选中对象清空(文本清空)
sel.text = str; // 给光标选中对象赋值(在文本框中的光标处追加str的值)
} else { // 如果浏览器不是IE
var prefix, main, suffix;
prefix = obj.value.substring( 0 , obj.selectionStart); // 将文本框中的对象拆分，从0到光标起始处
main = obj.value.substring(obj.selectionStart, obj.selectionEnd); // 得到光标选中的文本
suffix = obj.value.substring(obj.selectionEnd); // 得到光标以后的文本
obj.value = prefix + str + suffix; // 将光标选中的值替换成给定的值
}
obj.focus();
}
< / script>

< / head>
< body >
< form id = " form1 " runat = " server " >
< div style = " text-align: center; width: 100% " >
< asp:ScriptManager ID = " ScriptManager1 " runat = " server " >
< / asp:ScriptManager>
< table width = " 95% " class = " tablemain " border = " 1 " bordercolor = " #99CC33 " >
< tr >
< td colspan = " 4 " class = " tdhead " >
滞纳金计算方式
< / td>
< / tr>
< tr >
< td colspan = " 4 " >
< div style = " width: 50%; float: left " >
< label >
名称( * ) & nbsp; & nbsp; & nbsp; & nbsp; < / label><asp:TextBox ID="txtLatefees" runat="server">< / asp:TextBox >< / div>
< div >
< label >
滞纳时间算法( * ) & nbsp; & nbsp; & nbsp; & nbsp; < / label>
< asp:DropDownList ID = " ddlLatefeesCnt " runat = " server " >
< / asp:DropDownList>
< / div>
< / td>
< / tr>
< tr >
< td colspan = " 4 " style = " background-image: url('/Images/tab_14.gif') " >
< label style = " float: left " >
计算起始日期 < / label>
< / td>
< / tr>
< tr >
< td style = " width: 170px " >
< fieldset style = " width: 170px " >
< legend > 计算元素 < / legend>
< asp:ListBox ID = " ListBox1 " runat = " server " onDblClick = " Insert(this) " Rows = " 5 " Width = " 170px " >
< asp:ListItem Text = " 是否从上次计算后开始计算 " >< / asp:ListItem>
< asp:ListItem Text = " 计算起始日期 " >< / asp:ListItem>
< asp:ListItem Text = " 计算本金 " >< / asp:ListItem>
< asp:ListItem Text = " 计算滞纳金 " >< / asp:ListItem>
< asp:ListItem Text = " 本次计算滞纳金天数 " >< / asp:ListItem>
< / asp:ListBox>
< / fieldset>
< / td>
< td rowspan = " 2 " valign = " middle " colspan = " 3 " >
< table width = " 100% " >
< tr >
< td align = " left " >
< asp:TextBox ID = " TextBox1 " runat = " server " Height = " 90px " TextMode = " MultiLine " Width = " 441px " >< / asp:TextBox>
< / td>
< / tr>
< tr >
< td align = " left " >
< input id = " Button2 " onclick = " Insert(this) " type = " button " value = " + " / ><input id="Button1"
onclick = " Insert(this) " type = " button " value = " - " / ><input id="Button3" οnclick="Insert(this)"
type = " button " value = " * " / ><input id="Button4" οnclick="Insert(this)" type="button"
value = " / " / ><input id="Button5" οnclick="Insert(this)" type="button" value="=" / >< input
id = " Button6 " onclick = " Insert(this) " type = " button " value = " ( " / ><input id="Button7"
onclick = " Insert(this) " type = " button " value = " ) " / >
< input id = " Button10 " onclick = " Insert(this) " type = " button " value = " 且 " / ><input id="Button11"
onclick = " Insert(this) " type = " button " value = " 或 " / ><br / >
< input id = " Button8 " onclick = " Insert(this) " type = " button " value = " 如果…则…如果完 " / ><input
id = " Button9 " onclick = " Insert(this) " type = " button " value = " 如果…则…否则…如果完 " / >
< / td>
< / tr>
< tr >
< td >
& nbsp;
< / td>
< / tr>
< / table>
< / td>
< / tr>
< tr >
< td >
< fieldset style = " width: 170px " >
< legend > 辅助元素 < / legend>
< asp:ListBox ID = " ListBox2 " runat = " server " onDblClick = " Insert(this) " Rows = " 5 " Width = " 170px " >
< asp:ListItem Text = " 应收日期 " >< / asp:ListItem>
< asp:ListItem Text = " 本次计算截止日期 " >< / asp:ListItem>
< asp:ListItem Text = " 应收金额 " >< / asp:ListItem>
< asp:ListItem Text = " 已收金额 " >< / asp:ListItem>
< asp:ListItem Text = " 应收本金 " >< / asp:ListItem>
< asp:ListItem Text = " 优惠金额 " >< / asp:ListItem>
< asp:ListItem Text = " 滞纳金 " >< / asp:ListItem>
< asp:ListItem Text = " 减免滞纳金 " >< / asp:ListItem>
< asp:ListItem Text = " 已计算滞纳金天数 " >< / asp:ListItem>
< asp:ListItem Text = " 滞纳金计算比例 " >< / asp:ListItem>
< / asp:ListBox>
< / fieldset>
< / td>
< / tr>
< tr >
< td colspan = " 4 " >
< asp:ImageButton ID = " ibtn_Save " runat = " server " AlternateText = " 保存 " ImageUrl = " ~/Images/save.gif "
OnClick = " ibtn_Save_Click " / >
< asp:ImageButton ID = " ibtn_return " runat = " server " AlternateText = " 返回 " CausesValidation = " False "
ImageUrl = " ~/Images/return.gif " OnClick = " ibtn_return_Click " / >
< / td>
< / tr>
< / table>
< / div>
< / form>
< / body>
< / html>