SQL UDF
有如下表 groupid memberid string val a 3 stral 6 a 9 stra2 7 b 2 strb1 3 b 4 strb2 7 b 5 strb3 3 b 9 strb4 11 c 3 strc1 8 c 7 strc2 10 c 9 strc3 12 想得出结果如下,注意product,是groupid相对应的val乘积 groupid product a 42 b 693 c 960 先看下自己的苯方法 ……
文章列表
SQL Cursor
USE TEST;GODECLARE @SchemaName varchar(255)DECLARE @TableName varchar(255)DECLARE @IndexName varchar(255)DECLARE @Fragmentation floatDECLARE TableCursor CURSOR FOR SELECT SCHEMA_NAME(CAST(OBJECTPROPERTYEX(i.object_id,'Schemaid') As int)), OBJECT_NAME(i.object_id), i.name, ps.avg_fragmentation_in_percent FROM sys.dm_db_index……
Pivot and unpivot
提问 现有一表ee，数据为: employee_sn vehicle_type Cvehicle_type time_money ----------- ------------ ------------- --------------------- 400012 1 4 20.00 400027 1 13 &n……
研究CTE recursion
这是邹建书的列子利用CTE recursion找部门下的所有部门. use test;go CREATE TABLE Dept( id int primary key, parent_id int, Name nvarchar(20))insert Deptselect 0,0,N'<全部>' union allselect 1,0,N'财务部' union allselect 2,0,N'行政部' union allselect 3,0,N'业务部' union allselect 4,0,N'财务部' union allselect 5,4,N'销售部' ……
After Trigger and Foreign Key and Transation
USE TEST GO if object_id('tb3') is not null drop table tb3 go create table tb3(code int identity(1,1), name nvarchar(12),Pay money)go create trigger trig1 on tb3 after insert AS if (select count(code) from inserted where Pay<1500)>0 begin RAISERROR('THE DATA INTEGRITY PROBLEM',16,1) ROLLBACK TRANSACTION END;GO ……
TRigger and function
use pubsgo if object_id('tb1') is not null drop table tb1; go Create table tb1 (E_id Nchar(5) primary key,Name Nvarchar(12));GO if object_id('tirg1') is not null drop trigger trig1 go Create trigger trig1 on tb1 for insert as if (select count(e_id) from inserted where name =N'张春良')>1 begin print 'th……
Crystal Report开发小技巧慢慢加
1.控制DETAIL记录 选中 Detail--右健--Section Expert--选中 New Page After--点 编辑 --输入 RecordNumber mod 6 =0 ---确定
BOM反向的Report (where used )完成
item where used report
要求单个item在所有成品中的用量，先参考一个函数 --生成测试数据 create table BOM_1(Item int,bom_head varchar(20),bom_child varchar(20),number int,products_attribute varchar(20)) insert into BOM_1 select 1 ,'A' ,'A1',1,'采购' insert into BOM_1 select 2 ,'A' ,'A2',2,'生产' insert into BOM_1 select 3 ,'A2','A3',3,'生产' insert into BOM_1 select 4 ,'A2','A4',2,'采购' insert into BOM_1 select 5 ,'A3','A5',2,'采购' insert into BOM_1 select 6 ,'A3','A6',1,'采购' insert into BOM_1 select 7 ,'B' ,'B1',1,'采购' insert into BOM_1 select 8 ,'B' ,'B2',2,'生产' insert into BOM_1 select 9 ,'B2','B3……
用户定义函数
函数是由一个或多个 Transact-SQL 语句组成的子程序，可用于封装代码以便重新使用。Microsoft® SQL Server™ 2000 并不将用户限制在定义为 Transact-SQL 语言一部分的内置函数上，而是允许用户创建自己的用户定义函数。 可使用 CREATE FUNCTION 语句创建、使用 ALTER FUNCTION 语句修改、以及使用 DROP FUNCTION 语句除去用户定义函数。每个完全合法的用户定义函数名 (database_name.owner_name.function_name) 必须唯一。 必须被授予 CREATE FUNCTION 权限才能创建、修改或除去用户定义函数。不是所有者的用户在 Transact-SQL 语句中使用某个函数之前，必须先给此用户授予该函数的适当权限。……
SQL INJECTION
YouStop SQL Injection Attacks Before They Stop Stop SQL Injection Attacks Before They Stop You
研究邹建的一个bom展开例子
SQL Join
Specifies the type of join operation. INNER Specifies all matching pairs of rows are returned. Discards unmatched rows from both tables. This is the default if no join type is specified. LEFT [OUTER] Specifies that all rows from the left table not meeting the specified condition are included in the result set, and output columns from the right table are set to NULL in addition to all rows returned by the inner join. RIGHT [OUTER] Specifies that all rows from the right table not meeting the specified condition are included in the result set, and output columns fro……
@@ROWCOUNT
今天一直在研究Accpac的bom结构,差点搞晕,最终发现自己一个地方理解错误 @@ROWCOUNT 开始以为这个全局变量扫描一行+1后面发现online book写很清楚是返回受上一语句影响的行数,所以对bom来说他是提示还没找到（根节点或叶枝节点
Crystal Rerort(二)
Crystal Report, Formula 中有关循环变量类加问题,我们一定要注意在变量前加上WhileprintingRecords,这样formula每次取出的才不是最新值,另外Resetcount付初始值的Formula我们一般放在PH, PF,RH,RF, 假如报表中只调用一次我们放在前面两个位置如果每页都调用,我们必须放在后面两个位置,另外在Formula之间调用Vairable,我们必须设置它为Global变量
SQL Server Date Formats
SQL Server Date Formats One of the most frequently asked questions in SQL Server forums is how to format a datetime value or column into a specific date format. Here's a summary of the different date formats that come standard in SQL Server as part of the CONVERT function. Following the standard date formats are some extended date formats that are often asked by SQL Server developers. ……
Dynamic SQL
Overview Dynamic SQL (code that is executed dynamically), like cursors and temporary tables, is another area of T-SQL that should be used with care and wisdom. It has the potential to be used in an unsafe way and could lead to serious security breaches and code that performs badly and is difficult to maintain. On the other hand, when used wisely, dynamic SQL can help you achieve things that would be difficult to achieve any other way. And there are cases in which it is the only way you……
PO采购ITEM清单及单价
这是相关SQL查询语句 select dbo.mipoh.id as PONO,dbo.mipoh.odate as PODATE,dbo.mipod.ITEM,dbo.mipoh.postat,dbo.mipoh.VENDOR, dbo.mipod.descr,dbo.mipod.po_uofm,dbo.mipod.ordered/dbo.mipod.poxstk as QTY, dbo.mipod.PRICE,(dbo.mipod.ordered/dbo.mipod.poxstk)*dbo.mipod.price as AMOUNTfrom dbo.mipoh inner join ……
SQL JOIN
SQL2000列转行
注：上面信息摘自网上部分有自己所写 1，创建表 /* 普通行列转换 (爱新觉罗.毓华 2007-11-18于海南三亚) 假设有张学生成绩表(tb)如下: Name Subject Result 张三 语文 74 张三 数学 83 张三 物理 93 李四 语文 74 李四 数学 84 李四 物理 94 */ ------------------------------------------------------------------------- /* 想变成 姓名 语文 数学 物理 ---------- ----------- ----------- ----------- 李四 74 84 94 张三 74 83 93 */ create table tb ( Name varchar(10) , Subject va……