经典sql语句

一、循环

create table tb(
   col1 varchar(1),
   col2 varchar(2)
) 

insert tb(col1,col2)values('0','0')
 go 10000000

 

 

二、数据合并

if object_id('[order]') is not null drop table [order]
go
create table [order]([orderid] int,[ordertype] varchar(1))
insert [order]
select 1,'A' union all
select 2,'B'
if object_id('[order_car]') is not null drop table [order_car]
go
create table [order_car]([orderid] int,[carno] varchar(100))
insert [order_car]
select 1,'10' union all
select 1,'20' union all
select 2,'30'

select
  b.orderid,
  a.ordertype,
  carno=stuff((select ','+ltrim(carno) from order_car where orderid=b.orderid for xml path('')),1,1,'')
from
  [order] a
join
  order_car b
on
  a.orderid=b.orderid
group by
  b.orderid,
  a.ordertype

 

三、sql2005 数据库邮件 
 EXEC msdb.dbo.sp_send_dbmail
    @recipients=N'pdm@163.com',
    @body=N'The master database is full.' ; --1122abc

 

四、背包算法

  IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
CREATE TABLE [tb] (id int,num int)
INSERT INTO [tb]
SELECT 1,2 UNION ALL

 

SELECT 2,3 UNION ALL
SELECT 3,3 UNION ALL
SELECT 4,5 UNION ALL
SELECT 6,7 UNION ALL
SELECT 7,12 UNION ALL
SELECT 8,23

--SQL查询如下:
--select * from tb
DECLARE @i int;
SET @i = 12;

;WITH Liang AS
(
    SELECT id,num,total=num,path=CAST(RTRIM(id) AS varchar(MAX))
    FROM tb WHERE num <= @i
    UNION ALL
    SELECT A.id,A.num,A.num+B.total,B.path+','+RTRIM(A.id)
    FROM tb AS A
        JOIN Liang AS B
    ON A.num+B.total <= @i AND A.id > B.id
    AND CHARINDEX(','+RTRIM(A.id)+',',','+B.path+',')=0
)
--select * from Liang
SELECT TOP 1 WITH TIES * FROM Liang WHERE total<=@i ORDER BY total DESC

 

 

IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
CREATE TABLE [tb] (id int,num int)
INSERT INTO [tb]
SELECT 1,2 UNION ALL
SELECT 2,3 UNION ALL
SELECT 3,3 UNION ALL
SELECT 4,5 UNION ALL
SELECT 6,7 UNION ALL
SELECT 7,12 UNION ALL
SELECT 8,23

 

GO
--建立函数
CREATE FUNCTION dbo.f_Path(@i int)
    RETURNS @t TABLE(id int,num int,total int,path varchar(8000),level int)
AS
BEGIN
    DECLARE @level int;
    SET @level = 1;
   
    INSERT @t SELECT id,num,num,CAST(RTRIM(id) AS varchar(8000)),@level
         FROM tb WHERE num <= @i
        
    WHILE @@ROWCOUNT > 0
        BEGIN
            SET @level = @level + 1
            INSERT @t
                SELECT A.id,A.num,A.num+B.total,B.path+','+RTRIM(A.id),@level
                FROM tb AS A
                    JOIN @t AS B
                ON A.num+B.total <= @i AND A.id > B.id
                     AND CHARINDEX(','+RTRIM(A.id)+',',','+B.path+',')=0
                     AND B.level = @level -1;
        END
   
    RETURN;
END
GO
--SQL查询如下:

SELECT A.*
FROM tb AS A
    JOIN (
        SELECT TOP 1 * FROM dbo.f_Path(15)
         ORDER BY total DESC,LEN(path)-LEN(REPLACE(path,',','')) DESC,id DESC
    ) AS B
ON CHARINDEX(','+RTRIM(A.id)+',',','+B.path+',')>0;

GO
--删除测试

 

--查询excel

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:/book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

 

 

五、表默认实现

create table t(i int identity,d int NOT NULL default(0)) --创建测试表
insert t select 6 union select 2 union select 3 --插入测试数据
select * from t --查看测试数据
sp_helpconstraint t --本例找到DF__t__d__093F5D4E
alter table t drop constraint DF__t__d__093F5D4E --命令成功完成
alter table t drop column d --命令成功完成
select * from t --查看删除列后的表
drop table t --删除测试表

alter table A add d2 int default(100) with values