如何将表中的相同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 行）

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 行）

如何用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

将多个表的数据按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 行）

如何取每个种类的前百分之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 行）

怎样用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 行）

查询每天的累计总量

现有如下结构的表:
编号----产品名称----产量(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 行）

得到表中除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

两表字段横向统计表

如果有表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

用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.商品名称