我所收集整理的一些语句！

潇洒老乌龟

以下语句是我在csdn,bc编程论坛,program论坛等地方收集整理的SQL语句，部分为自己创作。

（注：由于网页显示的原因，部分例中行例不对称，请见谅）
0001--如何将表中的相同ID号的第一条记录取出来

表：a
id name
11 aaaa
11 bbbb
11 cccc
22 dddd
22 eeee
22 ffff

如何将表中的相同id号的第一条记录取出来？即：
id name
11 aaaa
22 dddd


CREATE TABLE #a (
       [id] [char] (10),
       [name] [char] (10)
)

insert into #a(id,name) values(\'11\',\'aaaa\')  
insert into #a(id,name) values(\'11\',\'bbbb\')  
insert into #a(id,name) values(\'11\',\'cccc\')  
insert into #a(id,name) values(\'22\',\'dddd\')  
insert into #a(id,name) values(\'22\',\'eeee\')  
insert into #a(id,name) values(\'22\',\'ffff\')  

select * from #a b
where name=(select top 1 name from #a where id=b.id)

drop table #a

id         name      
---------- ----------
11         aaaa      
22         dddd      

（所影响的行数为 2 行）




CREATE TABLE #a (
       [id] [char] (10),
       [name] [char] (10)
)

insert into #a(id,name) values(\'11\',\'aaaa\')  
insert into #a(id,name) values(\'11\',\'bbbb\')  
insert into #a(id,name) values(\'11\',\'cccc\')  
insert into #a(id,name) values(\'22\',\'dddd\')  
insert into #a(id,name) values(\'22\',\'eeee\')  
insert into #a(id,name) values(\'22\',\'ffff\')  

select id1=identity(int,1,1),* into #t from #a
go
select id,name from #t where id1 in(select min(id1) from #t group by id)

drop table #a
drop table #t

id         name      
---------- ----------
11         aaaa      
22         dddd      

（所影响的行数为 2 行）

潇洒老乌龟

0002--SQL2000字符串分离

如何将\'2006-01-12|2006-02-13|2006-03-15|2006-11-11|2003-1-1\'按\'|\'分割成
2006-01-12
2006-02-13
2006-03-15
2006-11-11
2003-1-1


declare @Days varchar(4000)
declare @tmpDay varchar(10)
set @Days=\'2006-01-12|2006-02-13|2006-03-15|2006-11-11|2003-1-1\'
set @tmpDay=\'\'
declare @i int
set @i=0
while @i<len(@Days)
begin
  set @i=@i+1
  if SUBSTRING(@Days,@i,1)=\'|\'
     begin
       print left(@Days,@i-1)
       set @Days=SUBSTRING(@Days,@i+1,len(@Days))
       set @i=0
     end  
end
print @Days


输出结果：
2006-01-12
2006-02-13
2006-03-15
2006-11-11
2003-1-1



一字段信息包括如下：李三|李三＠d.com|公司|单位地址|  
我将查询用(Select)只列出李三及单位地址的方法？

create table #t(c1 varchar(100))
insert into #t select \'li4|33@com|mircrosoft|china|\'
insert into #t select \'zhang3|zhang3@163.com|IBM|USA|\'
insert into #t select \'李三|李三＠d.com|公司|单位地址|\'


select substring(c1,1,charindex(\'|\',c1)-1) as name,
reverse(substring(reverse(c1) , 2 , charindex(\'|\' , reverse(c1) , 2) - 2)) as address  from #t

drop table #t

name    address   
-----   -------
li4     china
zhang3  USA
李三    单位地址

（所影响的行数为 3 行）

潇洒老乌龟

0003--如何用SQL语句取主键字段名称及字段类型

如何取主键字段名称及字段类型

--得到主键字段名

1:
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME<>\'dtproperties\'

2:
EXEC sp_pkeys @table_name=\'表名\'

3:
select o.name as 表名,c.name as 字段名,k.colid as 字段序号,k.keyno as 索引顺序,t.name as 类型
from sysindexes i
join sysindexkeys k on i.id = k.id and i.indid = k.indid
join sysobjects o on i.id = o.id
join syscolumns c on i.id=c.id and k.colid = c.colid
join systypes t on c.xusertype=t.xusertype
where o.xtype = \'U\' and o.name=\'要查询的表名\'
and exists(select 1 from sysobjects where xtype = \'PK\' and parent_obj=i.id and name = i.name)
order by o.name,k.colid

潇洒老乌龟

0004--将多个表的数据按ID合并起来

将多个表的数据按ID合并起来
a
id , value
1  , 11
2  , 21
3  , 31
4  , 41
5  , 51

b
id , value
1  , 12
2  , 22
3  , 32
4  , 42

c  , value
1  , 13
2  , 23
3  , 33
5  , 53

d
a , value1 , value2 , value3
1 , 11     , 12     , 13
2 , 21     , 22     , 23
3 , 31     , 32     , 33
4 , 41     , 42     , 0
5 , 51     , 0      , 53


CREATE TABLE #a ([id] [char] (10),[value] [int])
CREATE TABLE #b ([id] [char] (10),[value] [int])
CREATE TABLE #c ([id] [char] (10),[value] [int])

insert into #a(id,value) values(\'1\',11)  
insert into #a(id,value) values(\'2\',21)  
insert into #a(id,value) values(\'3\',31)  
insert into #a(id,value) values(\'4\',41)  
insert into #a(id,value) values(\'5\',51)  

insert into #b(id,value) values(\'1\',12)  
insert into #b(id,value) values(\'2\',22)  
insert into #b(id,value) values(\'3\',32)  
insert into #b(id,value) values(\'4\',42)  

insert into #c(id,value) values(\'1\',13)  
insert into #c(id,value) values(\'2\',23)  
insert into #c(id,value) values(\'3\',33)  
insert into #c(id,value) values(\'5\',53)  

select isnull(isnull(#a.id,#b.id),#c.id) id,#a.value value1,#b.value value2,#c.value value3
from #a full join #b on #a.id=#b.id
full join #c on isnull(#a.id,#b.id)=#c.id

drop table #a
drop table #b
drop table #c

id         value1      value2      value3      
---------- ----------- ----------- -----------
1          11          12          13
2          21          22          23
3          31          32          33
4          41          42          NULL
5          51          NULL        53

（所影响的行数为 5 行）



2个表，
表A
   a1          a2
   1            9
   2           10
   3           67
   4           33
表B
   b1          b2
    2           31
    4           99
想实现这样
   a1         a2        b2
    1         9          NULL(或0或不体现任何数）
    2         10         31
    3         67         NULL(或0或不体现任何数）
    4         33         99
谢谢各位指导～


CREATE TABLE #a ([a1] [char] (10),[a2] [int])
CREATE TABLE #b ([b1] [char] (10),[b2] [int])

insert into #a(a1,a2) values(\'1\',9)  
insert into #a(a1,a2) values(\'2\',10)  
insert into #a(a1,a2) values(\'3\',67)  
insert into #a(a1,a2) values(\'4\',33)  

insert into #b(b1,b2) values(\'2\',31)  
insert into #b(b1,b2) values(\'4\',99)  

--显示NULL
--select isnull(#a.a1,#b.b1) a1,#a.a2 a2,#b.b2 b2
--from #a full join #b on #a.a1=#b.b1

--显示0
select #A.a1, #A.a2, isnull(#B.b2, 0)
from #A left join #B on #A.a1 = #B.b1

drop table #a
drop table #b

潇洒老乌龟

0005--如何取每个种类的前百分之x的记录

示例数据(表a):  
area cust money  
--------------  
A    123  20.0  
A    159  20.0  
A    456  25.0  
A    483  30.0  
A    789  40.0  
A    597  50.0  
B    147  10.0  
B    258  20.0  
B    369  25.0  
B    384  30.0   

希望得到的如下结果  
area cust money  
--------------  
A    483  30.0  
A    789  40.0  
A    597  50.0  
B    369  25.0  
B    384  30.0  

现有表a，想得到表a中各地区(area)的商户(cust)交易金额(money)排该地区里面前百分之50%的记录.  
即要：  
地区A中金额前百分之50%  
地区B中金额前百分之50%  
....C..............50%  
....D..............50%
......................  

CREATE TABLE #a (
       [area] [char] (10),
       [cust] [char] (10),
       [money] [numeric](10, 1) NULL
)

insert into #a(area,cust,money) values(\'A\',\'123\',20.0)  
insert into #a(area,cust,money) values(\'A\',\'159\',20.0)  
insert into #a(area,cust,money) values(\'A\',\'456\',25.0)  
insert into #a(area,cust,money) values(\'A\',\'483\',30.0)  
insert into #a(area,cust,money) values(\'A\',\'789\',40.0)  
insert into #a(area,cust,money) values(\'A\',\'597\',50.0)  
insert into #a(area,cust,money) values(\'B\',\'147\',10.0)  
insert into #a(area,cust,money) values(\'B\',\'258\',20.0)  
insert into #a(area,cust,money) values(\'B\',\'369\',25.0)  
insert into #a(area,cust,money) values(\'B\',\'384\',30.0)   

select * from #a t
where cust in
(
select top 50 percent cust from #a where area=t.area order by money desc
)

drop table #a

//结果
area       cust       money        
---------- ---------- ------------
A          483        30.0
A          789        40.0
A          597        50.0
B          369        25.0
B          384        30.0

（所影响的行数为 5 行）

潇洒老乌龟

0006--怎样用SQL查询出每天刷卡次数大于3次的人员刷卡记录

declare @record table(ManID varchar(10),TheDateTime datetime)
insert @record
select \'001\',  \'2006-09-26 10:00:00\' union all
select \'001\',  \'2006-09-26 10:05:00\' union all
select \'001\',  \'2006-09-26 12:00:00\' union all
select \'001\',  \'2006-09-26 18:00:00\' union all
select \'002\',  \'2006-09-26 08:00:00\' union all
select \'002\',  \'2006-09-26 10:00:00\' union all
select \'002\',  \'2006-09-26 20:00:00\' union all
select \'003\',  \'2006-09-26 21:00:00\' union all
select \'001\',  \'2006-09-27 12:00:00\' union all
select \'001\',  \'2006-09-27 18:00:00\' union all
select \'001\',  \'2006-09-27 18:00:00\' union all
select \'001\',  \'2006-09-27 18:00:00\' union all
select \'002\',  \'2006-09-27 08:00:00\'

select * from @record a where
exists(select * from @record where ManID = a.ManID and datediff(dd,TheDateTime,a.TheDateTime) = 0  group by ManID having count(*) >3)
order by TheDateTime,ManID


ManID      TheDateTime                                            
---------- ------------------------------------------------------
001        2006-09-26 10:00:00.000
001        2006-09-26 10:05:00.000
001        2006-09-26 12:00:00.000
001        2006-09-26 18:00:00.000
001        2006-09-27 12:00:00.000
001        2006-09-27 18:00:00.000
001        2006-09-27 18:00:00.000
001        2006-09-27 18:00:00.000

（所影响的行数为 8 行）

潇洒老乌龟

0007--查询每天的累计总量

现有如下结构的表:
编号----产品名称----产量(int)----日期(varchar)
1   ----某产品1 ----100      ----20060922
2   ----某产品1 ----130      ----20060923
3   ----某产品2 ----104      ----20060924
5   ----某产品1 ----100      ----20060925
7   ----某产品1 ----200      ----20060926
.
.
.
现在要求查询每天的累计产量如:
20060923的累计产量就为20060922的100+20060923的130=230.
20060924的累计产量就为230+104=334,
20060925的累计产量为334+100=434...
请问该如何查询?


create table #a(
       [id] [char] (10),
       [proname] [char] (10),
       [output] [int],
       [dt] [datetime]
)

insert into #a(id,proname,output,dt) values(\'1\',\'某产品1\',100,\'2006-09-22\')
insert into #a(id,proname,output,dt) values(\'2\',\'某产品1\',130,\'2006-09-23\')
insert into #a(id,proname,output,dt) values(\'3\',\'某产品2\',104,\'2006-09-24\')
insert into #a(id,proname,output,dt) values(\'5\',\'某产品1\',100,\'2006-09-25\')
insert into #a(id,proname,output,dt) values(\'7\',\'某产品1\',200,\'2006-09-26\')

select *,(select sum(output) from #a where dt<=b.dt) as \'每日总量\'
from #a b
order by dt

drop table #a


id         proname    output      dt                                                     每日总量        
---------- ---------- ----------- ------------------------------------------------------ -----------
1          某产品1       100         2006-09-22 00:00:00.000                                100
2          某产品1       130         2006-09-23 00:00:00.000                                230
3          某产品2       104         2006-09-24 00:00:00.000                                334
5          某产品1       100         2006-09-25 00:00:00.000                                434
7          某产品1       200         2006-09-26 00:00:00.000                                634

（所影响的行数为 5 行）

潇洒老乌龟

0008--得到表中除Col1、Col2的所有列

例如：userno_fm、userno_to
create table test(
       num int identity(1,1),
       userno_fm varchar(10),
       userno_to varchar(10),
       username varchar(10))
select * from test

declare @sql varchar(8000)
    select @sql=\'\'
    select @sql=@sql+\',\'+[name] from
    (select [name] from syscolumns where object_id(N\'[test]\')=[id] and [name] not in (\'userno_fm\',\'userno_to\')) A

    set @sql=\'select \'+stuff(@sql,1,1,\'\')+\' from [test]\'
    --print @sql
    exec (@sql)

drop table test

潇洒老乌龟

0009--两表字段横向统计表

如果有表t1 ,t2

t1包含了f1,f2,f3...int型字段
t2包含了f1,f2,f3...int型字段
如：
t1                         t2                     
=======================    =======================
f1    f2    f3              f1    f2    f3         
=======================    =======================
10    20    30              10    -20    30         
20    30    40              20    30    -40         
....                        ....                    

现在希望生成一个查询
==========================================================
t1.f1    t1.f2    t1.f3    t2.f1    t2.f2    t2.f3    sum     
==========================================================
10        20       30       10       -20       30      80  
20        30       40       20       30       -40      120  



如果有关联字段ID，如下：

select
      sum(t1.f1) as [t1.f1],
      sum(t1.f2) as [t1.f2],
      sum(t1.f3) as [t1.f3],
      sum(t2.f1) as [t1.f1],
      sum(t2.f2) as [t1.f2],
      sum(t2.f3) as [t1.f3],
      sum(
          isnull(t1.f1,0)+
          isnull(t1.f2,0)+
          isnull(t1.f3,0)+
          isnull(t2.f1,0)+
          isnull(t2.f2,0)+
          isnull(t2.f3,0)
         ) as [Sum]
from t1 a inner join t2 b on a.id=b.id



select
      t1.f1 as [t1.f1],
      t1.f2 as [t1.f2],
      t1.f3 as [t1.f3],
      t2.f1 as [t1.f1],
      t2.f2 as [t1.f2],
      t2.f3 as [t1.f3],
isnull(t1.f1,0)+
isnull(t1.f2,0)+
isnull(t1.f3,0)+
isnull(t2.f1,0)+
isnull(t2.f2,0)+
isnull(t2.f3,0) as [Sum]
from t1 a inner join t2 b on a.id=b.id

潇洒老乌龟

0010--用SQL算剩余量

请教SQL语句的具体写法!
现有2张表: 一张是商品总量表,一张是商品出库表.如下:

商品总量表:

商品名称 商品总量
a        100
b        200


商品出库表:

商品名称 商品出库数量
a        20
a        30
b        10
b        50
b        60

如何用一条SQL语句将每种商品的剩余量写出来?


1、Select A.商品名称,A. 商品总量-B. 商品出库数量合计 AS 商品的剩余量 from 商品总量表 AS A ,(Select商品名称 , sum(商品出库数量) as商品出库数量合计 from商品出库表 group by商品名称) AS B WHERE A. 商品名称=B. 商品名称

1、Select A.商品名称,A. 商品总量-B. 商品出库数量合计 AS 商品的剩余量 from 商品总量表 AS A LEFT JOIN ,(Select商品名称 , sum(商品出库数量) as商品出库数量合计 from商品出库表 group by商品名称) AS B ON A. 商品名称=B. 商品名称

最好用左联接处理



select a.商品名称 as 商品名称 , a.商品总量 - b.商品出库数量 as 剩余量
from 商品总量表 as a , (select 商品名称 , sum(商品出库数量) as 商品出库数量 group by 商品名称) as b
where a.商品名称 = b.商品名称