sql语句总结

 

在查询语句中不能使用iif(isempty(),,)函数，能用ISNULL ( check _ expression , replacement_value )

replacement_value 是在 check_expression 为 NULL时将返回的表达式。 也能用 (CASE WHEN t .parentuid IS NULL THEN 1 ELSE 2 END) AS stype ，据说isnull比case速度要快，没测试过，姑且是吧！

但是在用case时，说结构不对。视图定义中没有输出列或from语句中没有项目。搞不懂，不过能输出结果。

同事说有些情况需要在属性中设置，不能在设计中进行设置。奇怪！！！！

SELECT s.so_id AS so_id, s.state_Name AS state_Name, s.state_Memo AS state_Memo,
s.main_Rank AS main_Rank, s.sub_Rank AS sub_Rank, s.state_Code AS state_Code,
p.execstate_id, p.exec_begin, p.exec_end, p.empcode, p.create_Time, p.exec_memo,
p.remarks, p.uid, s.id, CASE WHEN p.plan_time IS NULL
THEN s.plan_time ELSE s.plan_time END AS plan_time
FROM (SELECT dbo.exec_state_proc.*
FROM (SELECT MAX(DISTINCT create_Time) AS create_Time
FROM dbo.exec_state_proc
GROUP BY execstate_id) AS t INNER JOIN
dbo.exec_state_proc ON t .create_Time = dbo.exec_state_proc.create_Time)
p RIGHT OUTER JOIN
dbo.exec_state s ON p.execstate_id = s.id

1)其中若引用了case when保存时总是出错（case错误），不过不影响查询。

2）其中若引用了case when保存后，若再添加新的列，则不能保存（from错误）。这时可以先删除case，添加字段，然后在添加case.

一个存储过程，作为备忘。

CREATE PROCEDURE crm_changeamount
@so_id numeric,@so_amount numeric,@empcode nvarchar(5)
AS
declare @allsum numeric,@qp_id numeric,@task_id numeric,@old_so_amount numeric
set @allsum=0
select @old_so_amount=so_amount from crmtest.dbo.tc_sales_order_m where so_id=@so_id
update crmtest.dbo.tc_sales_order_m set so_amount=@so_amount where so_id=@so_id
select @qp_id=qp_id from crmtest.dbo.rel_qp_so where so_id=@so_id
select @allsum=@allsum+so_amount from crmtest.dbo.tc_sales_order_m m join crmtest.dbo.rel_qp_so s on m.so_id=s.so_id where s.qp_id=@qp_id
update crmtest.dbo.tc_quote_price_m set qp_amount=@allsum where qp_id=@qp_id
select @task_id=task_id from crmtest.dbo.tc_quote_price_m where qp_id=@qp_id
update crmtest.dbo.tc_task set task_sales_projection=@allsum where task_id=@task_id
insert into tracehistory (tablename,pkeyid,oldvalue,newvalue,alt_empcode) values('tc_sales_order_m',@so_id,@old_so_amount,@so_amount,@empcode)
GO

其中注意的几点：

1）as前面的变量是存储过程的参数。

2）@empcode nvarchar(5)变量定义为nvarchar需要定义其个数。

3）declare @allsum numeric,@qp_id numeric,@task_id numeric,@old_so_amount numeric存储过程中变量的声明需要加declare

4)select @task_id=task_id from crmtest.dbo.tc_quote_price_m where qp_id=@qp_id 设置某个变量的值可采用上面的方法。
5）在sql中的查询分析器里调用存储过程使用exec produrename a1,b1,c1 如

exec crm_changemount 5201,2401,'02141'

6)在.net中调用存储过程 myApp.Get_Field("crm_changeamount " & soid & "," & newamount & ",'" & myApp.GetEmpCode(Me.User.Identity.Name & "'"))其中myapp.get_field(sql语句)。

Me.SqlDataSource1.SelectCommand = "select * from (" & Me.FindData1.GetStrSql() & ") t order by prod_code desc"

需要对嵌套的sql语句加括号并定义个别名如t.

在查询中引用查询结果时，查询需重新命名如 as t,且记。

若嵌套中的查询结果为某个值，则不需要，如下所示

select @prod_id=isnull(prod_id,'') from crmtest.dbo.tc_product where ltrim(rtrim(prod_name))=ltrim(rtrim((select order_class_name from crmtest.dbo.tc_order_class where order_class_id=@prod_id)))

Dim strSql As String = "insert into budget_main(setdate,bmonth) values ( '" + setdate + " ','" + bmonth + "'"）

其中的日期需要用单引号括起来。

切记！

一触发器中的语句：

if ((select parentuid from plan_main where uid=(select MID from inserted )) is null)

运行后出现下面的错误：子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。

将语句都加上top 后就正常了。如下所示

if ((select top 1 parentuid from plan_main where uid=(select distinct MID from inserted )) is null)

insert 使用总结：

若插入的为值，则采用下面的方法values:

insert into tracehistory (tablename,pkeyid,oldvalue,newvalue,alt_empcode) values('tc_sales_order_m',@so_id,@old_so_amount,@so_amount,@empcode)
若插入的为sql语句，则不需使用values.

insert into exec_state_Emp(subid, empcode, isAlert_Sys, isAlert_Mail, isAlert_SMS) select @subid,emp_code, isAlert_Sys, isAlert_Mail, isAlert_SMS from

exec_Module_SubEmp where msubid=@ModuleId

select 使用总结：

select @sql=@sql +email + ';' from tablename

对于查询结果为多条记录的情况下，此方法可以将某列的字段同时使用;连接起来。

----2007-12-21

存储过程中使用的列为变量

declare @oldplan nvarchar(50),@plan_time smalldatetime,@sql nvarchar(200)
set @oldplan='设计结束时间'
set @sql=''
set @sql='select @plan_time='+@oldplan +' from 需求计划表 where 编号=''XQ-2004-227'''

exec sp_executesql @sql,N'@plan_time smalldatetime output',@plan_time output
select @plan_time

---------2007-12-24

在执行insert、delete和update语句时，若是同时执行了多条记录，则不能影响触发器。

如delete from plan 则表plan 中的触发器将不会被执行。

可通过游标的方式逐条记录执行，将会执行触发器。

---------2007-12-26

select convert(nvarchar(10),plan_time,120) from plan_sub

这个查询语句，将会把plan_time如yyyy-mm-dd hh:mm:ss[.fff]格式转换为

yyyy-mm-dd字符串格式

----------2008-1-8

SELECT main_id,max(audit_date)
FROM audit_sub
group by main_id

同事介绍在select语句中后面的字段除了为被分组的字段外（如例子中的main_id），只能是表达式了，如max ,min 等。可根据这两个字段作为关键字进行查找。

另外一种方法：

SELECT *，(SELECT TOP 1 audit_date FROM audit_sub
WHERE main_id = m.id
ORDER BY audit_date DESC) AS lastdate FROM dbo.audit_Main m

将会得到想要的结果

对audit_main中的每条记录进行查找筛选。

若写成这样：

SELECT TOP 1 dbo.audit_Sub.audit_Date
FROM dbo.audit_Sub INNER JOIN
dbo.audit_Main m ON dbo.audit_Sub.main_ID = m.ID
ORDER BY dbo.audit_Sub.audit_Date DESC

将会得到一条记录。此语句是查询结果后取得最晚的一条记录。

----------2008-10-23

select a,b sum(c) from table1 group by a,b

查询语句中含有group by 时，select 后面的列字段只能包括group by后面的列或者是聚合函数（Sum、Min、Max、Count 和 Distinct Count）

-----------2008-10-24

下面是网上找到的得行转换成列的代码

if object_id('pubs..tb') is not null
drop table tb
go
create table tb
(
月份 varchar(10),
蔬菜 varchar(10),
单位 varchar(10),
销售量 float
)

insert into tb(月份,蔬菜,单位,销售量) values('1月','白菜','斤',50)
insert into tb(月份,蔬菜,单位,销售量) values('1月','土豆','斤',30)
insert into tb(月份,蔬菜,单位,销售量) values('2月','白菜','斤',60)
insert into tb(月份,蔬菜,单位,销售量) values('2月','土豆','斤',40)
insert into tb(月份,蔬菜,单位,销售量) values('2月','黄瓜','斤',10)
insert into tb(月份,蔬菜,单位,销售量) values('3月','白菜','斤',5)

DECLARE @SQL VARCHAR(4000)
SET @SQL='SELECT 蔬菜,单位'
SELECT @SQL= @SQL+ ',sum(CASE WHEN 月份 = ''' + 月份 + ''' THEN 销售量 ELSE 0 END)['+月份+']' FROM (SELECT DISTINCT 月份 FROM tb) TAB
SET @SQL=@SQL+ ' FROM tb group by 蔬菜,单位 cube'
EXEC (@SQL)

我的理解：

',sum(CASE WHEN 月份 = ''' + 月份 + ''' THEN 销售量 ELSE 0 END)['+月份+']' FROM (SELECT DISTINCT 月份 FROM tb) TAB

等同于',['+月份+']=sum(CASE WHEN 月份 = ''' + 月份 + ''' THEN 销售量 ELSE 0 END)' FROM (SELECT DISTINCT 月份 FROM tb) TAB
其中 [ ] 括起来的内容表示是列的名称，可以根据内容动态的进行更新；

其中FROM (SELECT DISTINCT 月份 FROM tb) TAB取得各个月份（由于采用了group by所以月份要单独重新取得）。

declare    @sql    varchar(1000)   
set    @sql='select export_plan_yunguan.*,发货计划时间,发货执行时间,制造完成确认计划时间,制造完成确认执行时间 from export_plan_yunguan,(select 需求计划号 '   
select    @sql=@sql+',['+state_name+'计划时间]=max(convert(nvarchar(10),计划时间,120))'+',['+state_name+'执行时间]=max(convert(nvarchar(10),exec_end,120))' from (select distinct state_name from export_plan_yunguan1 ) t
set @SQL=@SQL + ' from    export_plan_yunguan1 group by 需求计划号) as s where s.需求计划号=export_plan_yunguan.需求计划号'

exec(@sql)  

上面代码是我学习后所写，由于使用了group by 需求计划号,只能取得需求计划号和聚合函数，若想取得计划时间，只好将其放到聚合函数中max(convert(nvarchar(10),计划时间,120))。

下面两段代码结果是一样的，

SELECT dbo.exec_Module.*FROM dbo.exec_Module INNER JOIN dbo.exec_Module_Sub ON dbo.exec_Module.MID = dbo.exec_Module_Sub.MID

select exec_module.* from dbo.exec_Module,exec_Module_Sub where dbo.exec_Module.MID = dbo.exec_Module_Sub.MID结果是一样的

所以我认为下面的两段代码结果应该是一致的，但是实际上A不能正常执行，B正常。

A:

declare @sql varchar(1000)
set @sql='select export_plan_yunguan.*,发货计划时间,发货执行时间,制造完成确认计划时间,制造完成确认执行时间 from export_plan_yunguan inner join (select 需求计划号 '
select @sql=@sql+',['+state_name+'计划时间]=max(convert(nvarchar(10),计划时间,120))'+',['+state_name+'执行时间]=max(convert(nvarchar(10),exec_end,120))' from (select distinct state_name from export_plan_yunguan1 ) t
set @SQL=@SQL + ' from export_plan_yunguan1 group by 需求计划号）as s on s.需求计划号=export_plan_yunguan.需求计划号'

exec(@sql)

B:

declare @sql varchar(1000)
set @sql='select export_plan_yunguan.*,发货计划时间,发货执行时间,制造完成确认计划时间,制造完成确认执行时间 from export_plan_yunguan,(select 需求计划号 '
select @sql=@sql+',['+state_name+'计划时间]=max(convert(nvarchar(10),计划时间,120))'+',['+state_name+'执行时间]=max(convert(nvarchar(10),exec_end,120))' from (select distinct state_name from export_plan_yunguan1 ) t
set @SQL=@SQL + ' from export_plan_yunguan1 group by 需求计划号) as s where s.需求计划号=export_plan_yunguan.需求计划号'